级别及修改"/>
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'
更多推荐
postgreSQL默认的隔离级别及修改
发布评论