【PostgreSQL】PostgreSQL的事务隔离级别

 

PostgreSQL支持的隔离级别：

·serializable

·repeatable read

·read committed

·read uncommitted

 

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

 

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

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

　　

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

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

　　

3.配置默认的隔离级别

$ vi postgresql.conf
default_transaction_isolation = 'read committed'

　　

重新加载后生效

$ pg_ctl -D /data/pg_data reload

　　

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

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

语法​：

set default_transaction_isolation='repeatable read';

　

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)动态修改当前事务的隔离级别

语法​：

set transaction_isolation = 'repeatable read';

　　

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

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=*# 

　　

此外

set transaction_isolation = 'repeatable read';

等价于：

set transaction isolation level repeatable read;