事务隔离级别-多版本并发控制(MVVC)-锁

前言

事务就是一组原子性的SQL语句组合,或者说一个独立的工作单元。如果其中有任何一条语句因为崩溃或其他原因无法执行,那么所有的语句都不会执行。也就是说,事务内的语句,要么全部执行成功,要么全部执行失败。在整个过程中,无论事务是否成功完成,总能确保数据的完整性。事务除了ACID外,还涉及隔离级别、多版本并发控制特性。

原文地址:https://aspdotnetcore.net/mysql-mvvc/

事务隔离级别

创建测试表

-- 创建表
CREATE TABLE `test` (
  `id` bigint(20) NOT NULL,
  `nickname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入一条记录
INSERT INTO test(id,nickname)VALUES(1000,'adnc');

读未提交-Read Uncommitted

READ UNCOMMITTED:事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为脏读(Dirty Read) 。这个级别会导致很多问题,从性能上来说,READ UNCOMMITTED 不会比其他的级别好太多,但却缺乏其他级别的很多好处,除非真的有非常必要的理由,在实际应用中一般很少使用。

事务A 事务B
1 START TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
2 SELECT nickname FROM test WHERE id=1000;;
读取结果:nickname = adnc
3 UPDATE test SET nickname=’alpha’ WHERE id=1000;
更新nickname字段
事务A更新nickname
**事务A执行了更新操作,事务B中UPDATE test SET nickname=’adnc’ WHERE id=1000 执行不了,要等到事务A提交。**
4 SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = alpha
SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = alpha
脏读,与第2步读取的结果不一致,同一个事务里面多次读取数据的结果不一样。
5 SELECT count(*) FROM test;
读取结果:1
SELECT count(*) FROM test;
读取结果:1
6 INSERT INTO test(id,nickname)VALUES(1001,’beta’);
插入一条新纪录
事务A新增了一条记录
新增的1001记录,事务B中也是是更新了不的。
7 SELECT count(*) FROM test;
读取结果:2
SELECT count(*) FROM test;
读取结果:2
脏读,与第5步读取的结果不一致,同一个事务里面多次读取数据的结果不一样。
8 COMMIT; COMMIT;

读提交-Read Committed

READ COMMITTED:大多数数据库系统的默认隔离级别都是 READ COMMITTED(但MysQL是REPEATABLE READ)。READ COMMITTED 满足前面提到的隔离性的简单定义:一个事务开始时,只能“看见”已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatable read),因为两次执行同样的查询,可能会得到不一样的结果。

  • 先删除记录,只保留一条。DELETE FROM test WHERE id>1000
  • 再更新nickname字段。UPDATE test SET nickname=’adnc’ WHERE id=1000
事务A 事务B
1 START TRANSACTION; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
2 SELECT nickname FROM test WHERE id=1000;;
读取结果:nickname = adnc
3 UPDATE test SET nickname=’alpha’ WHERE id=1000;
更新nickname字段
事务A更新nickname
**事务A执行了更新操作,事务B中UPDATE test SET nickname=’adnc’ WHERE id=1000 执行不了,要等到事务A提交。**
4 SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = alpha
SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = adnc
读提交隔离级别,没有出现脏读,读取结果还是adnc。
5 SELECT count(*) FROM test;
读取结果:1
SELECT count(*) FROM test;
读取结果:1
6 INSERT INTO test(id,nickname)VALUES(1001,’beta’);
插入一条新纪录
事务A新增了一条记录
新增的1001记录,事务B中也是是更新了不的。
7 SELECT count(*) FROM test;
读取结果:2
SELECT count(*) FROM test;
读取结果:1
读提交隔离级别,没有出现脏读,读取结果还是1。
8 COMMIT; 注意这里,事务A提交了。
9 SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = alpha
不可重复读,事务A提交后,与第4步读取的结果不一致,同一个事务里面两次读取数据的结果不一样。
10 SELECT count(*) FROM test;
读取结果:2
不可重复读,事务A提交后,与第5步读取的结果不一致,同一个事务里面两次读取数据的结果不一样。
COMMIT;

可重复读-REPEATABLE READ

REPEATABLE READ:解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上,可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。

  • 先删除记录,只保留一条。DELETE FROM test WHERE id>1000
  • 再更新nickname字段。UPDATE test SET nickname=’adnc’ WHERE id=1000
事务A 事务B
1 START TRANSACTION; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
2 SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = adnc
3 UPDATE test SET nickname=’alpha’ WHERE id=1000;
更新nickname字段
事务A更新nickname
**事务A执行了更新操作,事务B中UPDATE test SET nickname=’adnc’ WHERE id=1000 执行不了,要等到事务A提交。**
4 SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = alpha
SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = adnc
可重复读隔离级别,没有出现脏读,读取结果还是adnc。
5 SELECT count(*) FROM test;
读取结果:1
SELECT count(*) FROM test;
读取结果:1
6 INSERT INTO test(id,nickname)VALUES(1001,’beta’);
插入一条新纪录
事务A新增了一条记录
新增的1001记录,事务B中也是是更新了不的。
7 SELECT count(*) FROM test;
读取结果:2
SELECT count(*) FROM test;
读取结果:1
可重复读隔离级别,没有出现脏读,读取结果还是1。
8 COMMIT; 注意这里,事务A提交了。
9 SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = adnc
同一个事务里面多次读取数据的结果一样,可重复读。
10 SELECT count(*) FROM test;
读取结果:1
同一个事务里面多次读取数据的结果一样,可重复读。
11 INSERT INTO test(id,nickname)VALUES(1001,’gamma’);
这里会报错,在事务里读取不到1001的记录,但也插入不了。

查询不到,插入又不成功,幻读
12 COMMIT;

可串行化-SERIALIZABLE

SERIALIZABLE:这是最高的隔离级别,它通过强制事务排序,串行化执行,使之不可能相互冲突,从而解决幻读问题。这个隔离级别,会导致大量的超时现象和锁竞争,性能极差。

  • 先删除记录,只保留一条。DELETE FROM test WHERE id>1000
  • 再更新nickname字段。UPDATE test SET nickname=’adnc’ WHERE id=1000
事务A 事务B
1 START TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
2 SELECT nickname FROM test WHERE id=1000;
读取结果:nickname = adnc
3 UPDATE test SET nickname=’alpha’ WHERE id=1000;
这里更新不了,但可以新增记录,新增记录后,事务B不能执行查询。
事务A不能更新nickname字段
3 SELECT count(*) FROM test;
4 Insert 也执行不了

多版本并发控制

多版本并发控制参考原文地址:https://segmentfault.com/a/1190000037557620

多版本并发控制(MVCC) 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说,不管事务执行多长时间,事务内部看到的数据是不受其它事务影响的,根据事务开始的时间不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的。

简单来说,多版本并发控制 的思想就是保存数据的历史版本,通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来,读取数据的时候不需要加锁也可以保证事务的隔离效果。

可以认为 多版本并发控制(MVCC) 是行级锁的一个变种,但是它在很多情况下避免了加锁操作,因此开销更低。虽然实现机制有所不同,但大都实现了非阻塞的读操作,写操作也只锁定必要的行。

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑,它们一般都同时实现了多版本并发控制(MVCC)。不仅是MySQL,包括Oracle、PostgreSQL等其他数据库系统也都实现了MVCC,但各自的实现机制不尽相同,因为MVCC没有一个统一的实现标准,典型的有乐观(optimistic)并发控制和悲观(pessimistic)并发控制。

多版本并发控制解决了哪些问题?

通过 MVCC 可以让读写互相不阻塞,即读不阻塞写写不阻塞读,这样就可以提升事务并发处理能力。

  • 普通锁,只能串行执行;
  • 读写锁,可以实现读读并发;
  • 数据多版本并发控制,可以实现读写并发。

降低了死锁的概率

因为 InnoDB 的 MVCC 采用了乐观锁的方式,读取数据时并不需要加锁,对于写操作,也只锁定必要的行。

解决一致性读的问题

一致性读也被称为快照读,当我们查询数据库在某个时间点的快照时,只能看到这个时间点之前事务提交更新的结果,而不能看到这个时间点之后事务提交的更新结果。

快照读与当前读

快照读(SnapShot Read) 是一种一致性不加锁的读,是InnoDB并发如此之高的核心原因之一。

这里的一致性是指,事务读取到的数据,要么是事务开始前就已经存在的数据,要么是事务自身插入或者修改过的数据。

  • 不加锁的简单的 SELECT 都属于快照读
SELECT * FROM test WHERE id=1000
  • 与 快照读 相对应的则是 当前读,当前读就是读取最新数据,而不是历史版本的数据。加锁的 SELECT 就属于当前读。
SELECT * FROM test WHERE id=1000 LOCK IN SHARE MODE;
SELECT * FROM t WHERE id=1000 FOR UPDATE;

总结

多版本并发控制(MVCC) 在一定程度上实现了读写并发,它只在 可重复读(REPEATABLE READ) 和 提交读(READ COMMITTED) 两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容,因为 未提交读(READ UNCOMMITTED),总是读取最新的数据行,而不是符合当前事务版本的数据行。而 可串行化(SERIALIZABLE) 则会对所有读取的行都加锁。
行锁,并发,事务回滚等多种特性都和MVCC相关。

参考资料

https://segmentfault.com/a/1190000037557620
https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html
https://segmentfault.com/a/1190000022732257
https://aspdotnetcore.net/ef-core-readwrite
https://aspdotnetcore.net/mariadb-gtid/