postgreSQL默认的隔离级别及修改

SQL标准的4种隔离机制。

Isolation levelDirty readsNon-repeatable readsPhantomsRead Uncommittedmay occurmay occurmay occurRead Committeddon't occurmay occurmay occurRepeatable Readdon't occurdon't occurmay occurSerializabledon't occurdon't occurdon't occur常见的关系型数据库的默认事务隔离级别采用的是READ_COMMITED，例如PostgreSQL、ORACLE、SQL Server和DB2。但是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'

By 徐云鹤