【PostgreSQL】PostgreSQL的事务隔离级别

 

PostgreSQL支持的隔离级别：

·serializable

·repeatable read

·read committed

·read uncommitted

 

PostgreSQL默认的隔离级别为read committed。

 

1.查看默认的事务隔离级别

?

1 2 3 4 5

postgres=# show default_transaction_isolation;  default_transaction_isolation -------------------------------  read committed (1 row)

　　

2.查看当前事务的隔离级别

?

1 2 3 4 5

postgres=# show transaction_isolation;  transaction_isolation -----------------------  read committed (1 row)

　　

3.配置默认的隔离级别

?

1 2	$ vi postgresql.conf default_transaction_isolation = 'read committed'

　　

重新加载后生效

?

1	$ pg_ctl -D /data/pg_data reload

　　

4.动态修改事务隔离级别

(1)动态修改默认隔离级别

语法​：

?

1	set default_transaction_isolation='repeatable read';

　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

postgres=# show default_transaction_isolation;  default_transaction_isolation -------------------------------  read committed (1 row)   postgres=# show transaction_isolation;  transaction_isolation -----------------------  read committed (1 row)   postgres=# set default_transaction_isolation='repeatable read'; SET postgres=# show default_transaction_isolation;  default_transaction_isolation -------------------------------  repeatable read (1 row)   postgres=# show transaction_isolation;  transaction_isolation -----------------------  repeatable read (1 row)

　　

​

(2)动态修改当前事务的隔离级别

语法​：

?

1	set transaction_isolation = 'repeatable read';

　　

只有在事务中才有效，比如：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

postgres=# set transaction_isolation = 'repeatable read'; SET postgres=#  show transaction_isolation;                   transaction_isolation -----------------------  read committed (1 row)   postgres=# show default_transaction_isolation;  default_transaction_isolation -------------------------------  read committed (1 row)   postgres=# 开启一个事务后，再次查看 postgres=# begin; BEGIN postgres=*# set transaction_isolation = 'repeatable read'; SET postgres=*# show default_transaction_isolation;  default_transaction_isolation -------------------------------  read committed (1 row)   postgres=*#  show transaction_isolation;                   transaction_isolation -----------------------  repeatable read (1 row)   postgres=*#

　　

此外

?

1	set transaction_isolation = 'repeatable read';

等价于：

?

1	set transaction isolation level repeatable read;