PostgreSQL设置隔离级别

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)

发表评论