postgreSQL默认的隔离级别及修改

作者：瀚高PG实验室 （Highgo PG Lab）- 徐云鹤

SQL标准的4种隔离机制。

Isolation level	Dirty reads	Non-repeatable reads	Phantoms
Read Uncommitted	may occur	may occur	may occur
Read Committed	don't occur	may occur	may occur
Repeatable Read	don't occur	don't occur	may occur
Serializable	don't occur	don't occur	don't occur

常见的关系型数据库的默认事务隔离级别采用的是READ_COMMITED，例如PostgreSQL、ORACLE、SQL Server和DB2。但是使用InnoDB引擎的MySQL数据库默认事务隔离级别是REPEATABLE_READ。

查询postgreSQL默认的隔离级别：

highgo=> show default_transaction_isolation;

 default_transaction_isolation 

-------------------------------

 read committed

(1 row)

检查当前隔离级别：

highgo=# show transaction_isolation;

 transaction_isolation 

-----------------------

 read committed

(1 row)

修改隔离级别：

1.修改当前事务的隔离级别，须在事物中执行：

highgo=# begin;

highgo=# set transaction isolation level serializable;

SET

highgo=# show transaction_isolation; 

transaction_isolation 

----------------------- 

serializable

(1 row)

2.修改当前会话默认的隔离级别：

highgo=# begin;

highgo=# set default_transaction_isolation='repeatable read';

SET

highgo=# show transaction_isolation;

 transaction_isolation 

-----------------------

 serializable

(1 row)

highgo=#  show default_transaction_isolation;

 default_transaction_isolation 

-------------------------------

 repeatable read

(1 row)

highgo=# commit;

COMMIT

highgo=# show transaction_isolation;

 transaction_isolation 

-----------------------

 repeatable read

(1 row)

--

highgo=#  show default_transaction_isolation;

 default_transaction_isolation 

-------------------------------

 read committed

(1 row)

highgo=#  set default_transaction_isolation='repeatable read';

SET

highgo=#  show default_transaction_isolation;

 default_transaction_isolation 

-------------------------------

 repeatable read

(1 row)

highgo=#  show transaction_isolation;

 transaction_isolation 

-----------------------

 repeatable read

(1 row)

如果在事物中修改默认的隔离级别是不影响当前事物的。否则即时生效。

也可以在数据库级别设置默认的隔离级别：

[highgo@db1 data]$ cat postgresql.conf |grep default_transaction_isolation

#default_transaction_isolation = 'read committed'