PostgreSQL设置隔离级别

• serializable
• repeatable read
• read committed
• read uncommitted

PostgreSQL默认的隔离级别为read committed。
查看当前默认的隔离级别：

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

查看当前的隔离级别：

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

1. 修改PostgreSQL默认的隔离级别

修改配置文件postgresql.conf，设置默认的隔离级别，如下：
vim postgresql.conf
default_transaction_isolation = \’read committed\’
重新加载配置：
pg_ctl -D /data/pg reload

2. 动态修改PostgreSQL隔离级别

修改隔离级别必须在事务中执行，可以修改默认的隔离级别和当前会话的隔离级别，语法如下：
set default_transaction_isolation=\’repeatable read\’;
set transaction isolation level serializable;
示例：

postgres=# begin;
BEGIN
postgres=# set default_transaction_isolation=\'repeatable read\';
SET
postgres=#  show default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 repeatable read
(1 row)
postgres=# begin;
BEGIN
postgres=# set transaction isolation level serializable;
SET
postgres=# show transaction_isolation;
 transaction_isolation
-----------------------
 serializable
(1 row)