各种隔离级别的群魔乱舞 | SQL全面教程十:事务(6)厘清PostgreSQL中如何查看和设置隔离级别| 8月更文挑战

2,477 阅读2分钟

PostgreSQL默认的隔离级别是读提交(RC)。PostgreSQL内部只实现了三种不同的隔离级别,如果设置隔离级别为"读未提交",则其行为和读提交完全相同,即PostgreSQL没有实现读未提交。

下面看一下如何查看以及设置PostgreSQL的隔离级别。

查看PostgreSQL的隔离级别

方法一:查看默认的隔离级别

查看默认的隔离级别:show default_transaction_isolation;

postgres=# show default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read committed
(1 行记录)

方法二:查看默认的隔离级别

可以从PG的系统配置表pg_settings中查看默认的隔离级别

postgres=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_isolation';
             name              |    setting
-------------------------------+----------------
 default_transaction_isolation | read committed
(1 行记录)

查看当前会话的隔离级别

查看当前会话(连接)的隔离级别:

  • show transaction_isolation;
  • SELECT current_setting('transaction_isolation');
postgres=# show transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 行记录)

配置PostgreSQL的隔离级别

设置当前事务的隔离级别(只能在事务块内修改)

在PostgreSQL中,SET TRANSACTION可以用来修改事务的隔离级别。但是它只能在事务块(transaction blocks)中执行。

SET TRANSACTION ISOLATION LEVEL [ SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED ];

如下,修改PgSQL的隔离级别:

postgres=# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
警告:  SET TRANSACTION 只能在事务块中使用
SET
postgres=# begin transaction;
BEGIN
postgres=# show transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1 行记录)
postgres=# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
postgres=# show transaction_isolation;
 transaction_isolation
-----------------------
 repeatable read
(1 行记录)

以指定的隔离级别开始事务

可以直接在开始事务时指定隔离级别:

postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=# show transaction_isolation;
 transaction_isolation
-----------------------
 repeatable read
(1 行记录)

设置当前会话中的事务隔离级别

设置当前会话的隔离级别语法有些复杂,语法如下所示:

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL [ SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED ]

SQL语句永久修改全局的事务隔离级别

在命令行中,或者说使用SQL语句,也可以实现永久修改全局的事务隔离级别,命令为:ALTER SYSTEM SET default_transaction_isolation TO 'xxx'

修改完成后,执行SELECT pg_reload_conf();SQL语句重新加载PostgreSQL配置,使其生效。这样那就不用重启pg服务了。

postgres=# ALTER SYSTEM SET default_transaction_isolation TO 'REPEATABLE READ';
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 行记录)

postgres=# show default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 repeatable read
(1 行记录)

postgres=# SELECT current_setting('transaction_isolation');
 current_setting
-----------------
 repeatable read
(1 行记录)

这样配置是永久生效的,PostgreSQL重启后仍有效,具体的修改位置暂时不确定,因为查看配置文件postgresql.conf,其中的设置并没有修改。即ALTER SYSTEM SET并没有影响配置文件。

PG配置文件永久修改事务的隔离级别

PostgreSQL的配置文件postgresql.conf中有一项关于隔离级别的配置,修改后需要reload重新加载生效。这样可以永久修改全局的隔离级别。

如下:

#default_transaction_isolation = 'read committed'