Postgresql和Mysql中的事务隔离

事务的定义

事务拥有四个重要的特性：

  • 原子性（Atomicity）
    • 事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。
  • 一致性（Consistency）
    • 指事务将数据库从一种状态转变为另一种一致的的状态。事务开始前和结束后，数据库的完整性约束没有被破坏。
  • 隔离性（Isolation）
    • 要求每个读写事务的对象对其他事务的操作对象能互相分离，即该事务提交前对其他事务不可见。 也可以理解为多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。这指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。
  • 持久性（Durability）
    • 事务一旦提交，则其结果就是永久性的。即使发生宕机的故障，数据库也能将数据恢复，也就是说事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。
      这四个特性就是常说的数据库的ACID 特性

事务隔离的定义

SQL标准定义了4类隔离级别。用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

SQL事务隔离级别说明

SQL事务隔离参照等级从低到高依次为:

  • Read Uncommitted（读取未提交内容）
    • 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
  • Read Committed（读取提交内容）
    • 这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果。
  • Repeatable Read（可重读）
    • 这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的 “幻影” 行。
  • Serializable（可串行化）
    • 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

事务隔离的问题

  • 脏读（Drity Read）
    • 一个事务读取到另一事务未提交的更新数据。当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中（这个数据在有可能会回滚）
  • 不可重复读 (Non-repeatable read）
    • 在一个事务内，前后两次读到的数据是不一样。在 T1 事务两次读取同一数据之间，T2 事务对该数据进行了修改，就会发生 T1 事务中的两次数据读取不一样的结果。相反， 可重复读：在同一事务中多次读取数据时，能够保证所读数据一样，也就是后续读取不能读到另一事务已提交的更新数据。
  • 幻读 (Phantom Read）
    • 指当事务不是独立执行时发生的一种现象，例如：T1 事务对表中的 “全部数据行” 进行了修改，同时 T2 事务向表中插入了一行 “新数据”，操作 T1 事务的用户发现表中还存在没有修改的数据行，就好象发生了幻觉一样。
  • 不可重复读和幻读的异同
    • 两者都表现为两次读取的结果不一致
    • 不可重复读是由于另一个事务对数据的更改所造成的，第二次读到了不一样的记录
    • 幻读是由于另一个事务插入或删除引起的，第二次查询的结果发生了变化
    • 对于不可重复读，只需要锁住满足条件的记录
    • 对于幻读，要锁住满足条件及其相近的记录

mysql

mysql 隔离级别

隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
未提交读（Read uncommitted） 可能 可能 可能
已提交读（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（SERIALIZABLE） 不可能 不可能 不可能

Mysql默认个隔离级别是可重复读（Repeatable read）

mysql命令

mysql 8.x中事务隔离相关的命令如下:

check session transaction level (mysql8+)

1
2
3
4
5
6
7
8
9
mysql> SELECT @@transaction_ISOLATION;
+-------------------------+
| @@transaction_ISOLATION |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)
mysql>

check global transaction level (mysql8+)

1
2
3
4
5
6
7
8
9
mysql> SELECT @@global.transaction_ISOLATION;
+--------------------------------+
| @@global.transaction_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)
mysql>

改变单个会话或者新连接的隔离级别

1
2
3
4
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL SERIALIZABLE;

试验

如下实验基于mysql 8.x, 5.x的事务命令和8.x的有差异，整个试验过程和文字描述都摘录自文章MySQL 事务隔离级别和锁

READ UNCOMMITTED（读未提交）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。

  1. 创建数据库并设置隔离级别

    1
    2
    3
    4
    5
    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    create database test;
    use test;
    create table test(id int primary key);
    insert into test(id) values(1);

  2. mysql 1中，开始事务，更新记录为2

    1
    2
    3
    begin;
    update test set id = 2 where id = 1;
    select * from test; -- 此时看到一条ID为2的记录

  3. mysql 终端2中，开启事务后查看表中的数据。

    1
    2
    3
    4
    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    use test;
    begin;
    select * from test; -- 此时看到一条 ID 为 2 的记录

最后一步读取到了 mysql 终端 1 中未提交的事务（没有 commit 提交动作），即产生了脏读(Dirty Read)

READ COMMITTED（读提交）

一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果，此现象称为不可重复读问题(NonRepeatable Read)

  1. 准备两个终端，在此命名为 mysql 终端 1 和 mysql 终端 2，再准备一张测试表 test，写入一条测试数据并调整隔离级别为 READ COMMITTED

    1
    2
    3
    4
    5
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    create database test;
    use test;
    create table test(id int primary key);
    insert into test(id) values(1);

  2. 登录 mysql 终端 1，开启一个事务，将 ID 为 1 的记录更新为 2，并确认记录数变更过来。

    1
    2
    3
    begin;
    update test set id = 2 where id = 1;
    select * from test; -- 此时看到一条记录为 2

  3. 登录 mysql 终端 2，开启一个事务后，查看表中的数据。

    1
    2
    3
    4
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    use test;
    begin;
    select * from test; -- 此时看一条 ID 为 1 的记录

  4. 登录 mysql 终端 1，提交事务。

    1
    commit;

  5. 切换到 mysql 终端 2。

    1
    select * from test; -- 此时看到一条 ID 为 2 的记录

mysql 终端 2 在开启了一个事务之后，在第一次读取 test 表（此时 mysql 终端 1 的事务还未提交）时 ID 为 1，在第二次读取 test 表（此时 mysql 终端 1 的事务已经提交）时 ID 已经变为 2，说明在此隔离级别下已经读取到已提交的事务。

REPEATABLE READ（可重复读）

该隔离级别是 MySQL 默认的隔离级别，在同一个事务里，select 的结果是事务开始时时间点的状态，因此，同样的 select 操作读到的结果会是一致的，但是，会有幻读现象(Phantom Read)。
MySQL 的 InnoDB 引擎可以通过 next-key locks 机制（参考行锁的算法）来避免幻读。

  1. 准备两个终端，在此命名为 mysql 终端 1 和 mysql 终端 2。mysql 1中准备一张测试表 test 并调整隔离级别为 REPEATABLE READ.

    1
    2
    3
    4
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    create database test;
    use test;
    create table test(id int primary key,name varchar(20));

  2. 登录 mysql 终端 1，开启一个事务。

    1
    2
    begin;
    select * from test; -- 无记录

  3. 登录 mysql 终端 2，开启一个事务。

    1
    2
    3
    4
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    use test;
    begin;
    select * from test; -- 无记录

  4. 切换到 mysql 终端 1，增加一条记录并提交。

    1
    2
    insert into test(id,name) values(1,'a');
    commit;

  5. 切换到 mysql 终端 2。

    1
    select * from test; -- 此时查询还是无记录

通过这一步可以证明，在该隔离级别下已经读取不到别的已提交的事务，如果想看到 mysql 终端 1 提交的事务，在 mysql 终端 2 将当前事务提交后再次查询就可以读取到 mysql 终端 1 提交的事务。我们接着实验，看看在该隔离级别下是否会存在别的问题。

  1. 此时接着在 mysql 终端 2 插入一条数据。
    1
    insert into test(id,name) values(1,'b'); -- 此时报主键冲突的错误

也许到这里您心里可能会有疑问，明明在第 5 步没有数据，为什么在这里会报错呢？其实这就是该隔离级别下可能产生的问题，MySQL 称之为幻读。

SERIALIZABLE（序列化）

在该隔离级别下事务都是串行顺序执行的，MySQL 数据库的 InnoDB 引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。

  1. 准备两个终端，在此命名为 mysql 终端 1 和 mysql 终端 2. 登入 mysql 1，准备一张测试表 test 并调整隔离级别为 SERIALIZABLE

    1
    2
    3
    4
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    create database test;
    use test;
    create table test(id int primary key);

  2. 登录 mysql 终端 1，开启一个事务，并写入一条数据。

    1
    2
    begin;
    insert into test(id) values(1);

  3. 登录 mysql 终端 2，开启一个事务。

    1
    2
    3
    4
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    use test;
    begin;
    select * from test; -- 此时会一直卡住

  4. 立马切换到 mysql 终端 1,提交事务。

    1
    commit;

一旦事务提交，mysql 终端 2 会立马返回 ID 为 1 的记录，否则会一直卡住，直到超时，其中超时参数是由 innodb_lock_wait_timeout 控制。由于每条 select 语句都会加锁，所以该隔离级别的数据库并发能力最弱。

postgresql

Postgresql 隔离级别

如下表格内容引自官网Transaction Isolation

Isolation Level Dirty Read Nonrepeatable Read Phantom Read Serialization Anomaly
Read uncommitted Allowed, but not in PG Possible Possible Possible
Read committed Not possible Possible Possible Possible
Repeatable read Not possible Not possible Allowed, but not in PG Possible
Serializable Not possible Not possible Not possible Not possible

注意点:

  • Read uncommitted在标准sql中被定义，但是在PG中没有实现，会被当做Read committed来对待
  • PG中默认的隔离级别是Read committed
  • PG中Repeatable read下，幻读(Phantom Read)也不是被允许的。

postgresql命令

Postgresql下事务隔离的命令如下:

查看数据库事务隔离级别

1
2
3
SELECT name, setting FROM pg_settings WHERE name ='default_transaction_isolation';
或
SELECT current_setting('default_transaction_isolation');

修改全局事务级别

方法一: 修改postgresql.conf文件中的default_transaction_isolation
方法二: 使用alter system set default_transaction_isolation命令

1
2
alter system set default_transaction_isolation to 'SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED';
SELECT pg_reload_conf();

alter system set命令设置后，会在配置文件目录下产生postgresql.auto.conf文件，用来覆盖默认的postgresql.conf配置。
alter system set default_transaction_isolation并不对当前数据库生效，需要使用SELECT pg_reload_conf()来重新load配置或重启数据库后才能生效。

查看当前会话事务隔离级别的方法

1
2
3
show transaction_isolation
或
SELECT current_setting('transaction_isolation');

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

1
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED };

设置事务隔离级别

事务中设置隔离等级方式一:

1
2
3
BEGIN;
SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED };
show transaction_isolation;

事务中设置隔离等级方式二:

1
2
begin transaction isolation level serializable;
show transaction_isolation;

试验

READ UNCOMMITTED（读未提交）

Postgresql中可以设置隔离级别是READ UNCOMMITTED，但是具体的实现方式是和READ COMMITTED的行为一致的。

  1. 准备两个终端，在此命名为 pg 终端 1 和 pg 终端 2，再准备一张测试表 test，写入一条测试数据并调整隔离级别为READ UNCOMMITTED。登录pg终端1，执行如下操作。

    1
    2
    3
    4
    5
    create database test;
    \c test;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    create table test(id serial primary key);
    insert into test(id) values(1);

  2. 登录 pg终端 1，开启一个事务，将 ID 为 1 的记录更新为 2，并确认记录数变更过来。

    1
    2
    3
    begin;
    update test set id = 2 where id = 1;
    select * from test; -- 此时看到一条记录为 2

  3. 登录 pg终端 2，开启一个事务后，查看表中的数据。

    1
    2
    3
    4
    \c test;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    begin;
    select * from test; -- 此时看一条 ID 为 1 的记录

  4. 登录 pg终端 1，提交事务。

    1
    commit;

  5. 切换到 pg 终端 2。

    1
    select * from test; -- 此时看到一条 ID 为 2 的记录

可以看到，即使是设置了隔离级别为READ UNCOMMITTED，在pg 终端2中也无法读取pg终端 1中未提交的事务，只有在pg终端 1中提交了事务后，pg终端 2中才能读取对应的值。 即PG中READ UNCOMMITTED的隔离级别，同READ COMMITTED的隔离级别效果是一致的。

READ COMMITTED（读提交）

READ COMMITTED（读提交）是PG的默认隔离等级。
一个事务可以读取另一个已提交的事务，多次读取会造成不一样的结果，此现象称为不可重复读问题(NonRepeatable Read)

  1. 准备两个终端，在此命名为 pg 终端 1 和 pg 终端 2，再准备一张测试表 test，写入一条测试数据并调整隔离级别为READ COMMITTED。登录pg终端1，执行如下操作。

    1
    2
    3
    4
    5
    create database test;
    \c test;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
    create table test(id serial primary key);
    insert into test(id) values(1);

  2. 登录 pg终端 1，开启一个事务，将 ID 为 1 的记录更新为 2，并确认记录数变更过来。

    1
    2
    3
    begin;
    update test set id = 2 where id = 1;
    select * from test; -- 此时看到一条记录为 2

  3. 登录 pg终端 2，开启一个事务后，查看表中的数据。

    1
    2
    3
    4
    \c test;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
    begin;
    select * from test; -- 此时看一条 ID 为 1 的记录

  4. 登录 pg终端 1，提交事务。

    1
    commit;

  5. 切换到 pg 终端 2。

    1
    select * from test; -- 此时看到一条 ID 为 2 的记录

REPEATABLE READ（可重复读）

在REPEATABLE READ模式下，PG的select 的结果始终保持事务开始时那个时间点的状态，因此，同样的 select 操作不会读到其他事务提交的结果,读到的结果会是一致的。

  1. 准备两个终端，在此命名为 pg 终端 1 和 pg 终端 2。pg 1中准备一张测试表 test 并调整隔离级别为 REPEATABLE READ.

    1
    2
    3
    4
    create database test;
    \c test;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    create table test(id serial primary key,name varchar(20));

  2. 登录 pg 终端 1，开启一个事务。

    1
    2
    begin;
    select * from test; -- 无记录

  3. 登录 pg 终端 2，开启一个事务。

    1
    2
    3
    4
    \c test;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    begin;
    select * from test; -- 无记录

  4. 切换到 pg 终端 1，增加一条记录并提交。

    1
    2
    insert into test(id,name) values(1,'a');
    commit;

  5. 切换到 pg 终端 2。

    1
    2
    3
    select * from test; -- 此时查询还是无记录
    commit;
    select * from test; -- 此时能查到id=1的记录。

此时，可以看到，在pg终端2中的事务中，看不到pg终端1中提交的内容。只有在pg终端2中commit后，才能看到pg终端1中提交的内容。

我们来看pg是怎么避免幻读现象的。

  1. 在终端1，中更新id=1的记录

    1
    2
    begin;
    update test set name='t1' where id=1; -- 此时update语句可以执行完成

  2. 在终端2中，也更新id=1的记录

    1
    2
    begin;
    update test set name='t2' where id=1;  -- 此时update语句会被block住。

  3. 在pg终端1中，commit

    1
    commit;

  4. 此时，在pg终端2中，会报ERROR: could not serialize access due to concurrent update的错误。

    1
    2
    3
    test=# update test set name='t2' where id=1; 
    ERROR:  could not serialize access due to concurrent update
    test=#

在事务中，如果更新的数据被其他事务在更新，那么PG会等待其他事务完成。如果其他事务rollback了，那么继续更新数据，如果其他事务更新了同一条数据，那么本事务中会报告ERROR: could not serialize access due to concurrent update的错误。

如下是PG官方文档的相关描述:

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the transaction start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the repeatable read transaction will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message

ERROR: could not serialize access due to concurrent update

注意，在REPEATABLE READ模式中，因为事务在update同样的记录时，会有block的情况。因此可能会导致死锁的情况。
当出现死锁时，PG能够发现该情况，然后主动让一个事务操作失败。

1
2
3
4
5
6
7
test=# update test set name='t2' where id=2;
ERROR:  deadlock detected
DETAIL:  Process 63825 waits for ShareLock on transaction 2955343; blocked by process 72840.
Process 72840 waits for ShareLock on transaction 2955344; blocked by process 63825.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,5) in relation "test"
test=#

SERIALIZABLE（序列化）

在该隔离级别下事务都是串行顺序执行的，可以避免脏读、不可重读复读和幻读问题。

  1. 准备两个终端，在此命名为 pg 终端 1 和 pg 终端 2. 登入 pg 1，准备一张测试表 test 并调整隔离级别为 SERIALIZABLE

    1
    2
    3
    4
    create database test;
    \c test;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    create table test(id serial primary key);

  2. 登录 pg 终端 1，开启一个事务，并写入一条数据。

    1
    2
    begin;
    insert into test(id) values(1);

  3. 登录 pg 终端 2，开启一个事务。

    1
    2
    3
    4
    \c test;
    SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    begin;
    insert into test(id) values(2);

  4. 切换到 pg 终端 1,提交事务。

    1
    commit; -- 可以成功提交

  5. 切换到 pg 终端 2,提交事务。

    1
    commit; -- 会报ERROR:  could not serialize access due to read/write dependencies among transactions的错误

在REPEATABLE READ模式下，pg终端2的commit是可以成功提交的。
但是在SERIALIZABLE模式下，为了保证序列化，当两个事务都提交了数据时，PG会让第一个commit的事务成功提交，而让第二个事务提交失败。
详细描述参见: Transaction Isolation

Reference

留言

▲