目录
记录一下MySQL中事务的实现机制和锁，以及不同隔离级别下的测试笔记。
事务隔离级别
数据准备
-- 创建表
CREATE TABLE t(
a INT PRIMARY KEY,
b INT,
c INT
);
-- 插入数据
INSERT INTO t VALUES (1,1,1);
INSERT INTO t VALUES (2,2,2);
INSERT INTO t VALUES (3,3,3);
INSERT INTO t VALUES (4,4,4);
INSERT INTO t VALUES (5,5,5);
INSERT INTO t VALUES (6,6,6);
READ UNCOMMITTED（读未提交）
-- 查询当前事务隔离级别 默认：REPEATABLE-READ
SELECT @@tx_isolation;
-- 设置 读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 事务A
begin;
update t set c = 99 where a = 6;
-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select * from t where a = 6;
+---+------+------+
| a | b | c |
+---+------+------+
| 6 | 6 | 99 |
+---+------+------+
-- 事务B可以查询事务A未提交的数据
-- 一旦事务A回滚，事务B读到的就是 脏数据
隔离级别最弱的，会出现“脏读”，一般不会使用。
违反了ACID原则。
READ COMMITTED（读已提交）
-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin;
update t set c = 88 where a = 6;
-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin;
select * from t where a = 6;
+---+------+------+
| a | b | c |
+---+------+------+
| 6 | 6 | 99 |
+---+------+------+
-- 事务A
commit;
-- 事务B
select * from t where a = 6;
+---+------+------+
| a | b | c |
+---+------+------+
| 6 | 6 | 88 |
+---+------+------+
不同的事务之间，可以读取其他事务已经提交的数据。
但是会有一个问题：事务B多次读取，可能读取的数据不一致。
称作：不可重复读。
REPEATABLE READ（可重复读）
-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;
-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
begin;
-- 事务A
update t set c = 77 where a = 6;
commit;
-- 事务B
select * from t where a = 6;
+---+------+------+
| a | b | c |
+---+------+------+
| 6 | 6 | 88 |
+---+------+------+
-- 即使事务A已提交，事务B查询的还是旧数据，解决了“**不可重复读**”的问题
-- 事务B结束后，才能查看事务A提交的数据。
REPEATABLE READ 解决了READ COMMITTED 中“不可重复读”的问题。
在对事务隔离级别的定义中，REPEATABLE READ会有“幻行”的问题。
InnoDB实现了MVCC（多半版本并发控制），解决了“幻行”的问题。
注意
事实上，事务A和事务B并非完全隔离，InnoDB通过ReadView和事务ID来判断哪些数据对于当前事务是可见的，哪些是不可见的。
例如：
- 事务A开启，但是没有查询(没创建ReadView)。
- 事务B开启，修改数据，提交事务。
- 事务A可以查询到事务B修改的数据。
事务A查询、创建ReadView时，事务B已经提交（不活跃），事务A会将事务B修改的数据也读出来。
具体的判断依据会在后面记录。
SERIALIZABLE（可串行化）
-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
begin;
-- 事务B
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
begin;
-- 事务A
update t set c = 66 where a = 6;
-- 事务B
select * from t where a = 6;
-- SELECT操作会被阻塞，事务A会给查询出来的数据行加 排它锁。
-- 事务B不可读不可写，直到事务A结束，释放行锁。
在 SERIALIZABLE 隔离级别下，事务会给查询到的数据行加共享锁，其他事务可读不可写。
给写入的数据加排它锁，其他事务不可读，不可写。
SERIALIZABLE 是最强的隔离级别，也是并发性能最差的隔离级别，一般也很少使用。
InnoDB默认的隔离级别（REPEATABLE READ）通过实现MVCC来最大程度的给减少给数据行加锁，在遵循ACID的同时最大程度的来支持高并发。
保存点
对于一个事务，要么全部执行，要么全部回滚。
如果事务涉及到很多操作，但是在最后一步操作失误，如果全部回滚，意味着所有的操作都要重来一遍。
保存点(SAVEPOINT) 提供了一个“存档”的功能，可以让事务回滚到指定的位置。
通过“版本链”的方式实现，后面会讲到。
-- 事务A
begin;
UPDATE t SET c = 999 WHERE a = 6;
-- 设置保存点
SAVEPOINT s;
UPDATE t SET c = 999 WHERE a = 5;
-- 回滚到保存点
ROLLBACK TO s;
SELECT * FROM t;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
| 6 | 6 | 999 |
+---+------+------+
-- 删除保存点
RELEASE SAVEPOINT s1;
类似于“游戏存档”的功能。
MVCC实现机制
在InnoDB中，数据以“行”的格式保存在磁盘中，MySQL支持多种行格式。
具体的“行格式”可以查看笔者以前的博客。
InnoDB会在数据行中保存两个隐藏列：
- TRX_ID：事务ID，每次数据被修改都会保存相应事务的ID。
- ROLL_PTR：回滚指针，数据行被修改时，会生成一个版本链，通过回滚指针连接，用于事务回滚时恢复数据。
版本链
数据行被修改时，会生成一条“版本链”。
不同的数据行版本之间，通过“回滚指针”连接，事务回滚时，通过 回滚指针 来恢复数据。
ReadView
InnoDB中，通过ReadView来判断：不同的数据行版本中，哪些版本对于当前事务是可见的，哪些是不可见的。
在 READ UNCOMMITTED 隔离级别下，事务读取的总是 最新的版本，所以不需要ReadView。
在 SERIALIZABLE 隔离级别下，通过对数据行加锁来限制读写，也不需要ReadView。
只有 READ COMMITTED 和 REPEATABLE READ 需要ReadView，二者创建的时机不同。
-
READ COMMITTED
可以访问其他事务已提交的数据，所以每次查询都会创建新的ReadView。 -
REPEATABLE READ
第一次查询就创建ReadView，之后的查询都会直接使用已有的ReadView，可以保证即使其他事务已提交，当前事务仍然认为它是活跃的。
核心内容
ReadView中主要包含的核心内容：
- m_ids：创建ReadView时活跃的事务ID集合。
- min_trx_id：创建ReadView时，活跃的最小事务ID。
- max_trx_id：创建ReadView时，下一个应该分配的事务ID(递增)。
- creator_trx_id：创建ReadView的当前事务ID。
max_trx_id不代表m_ids中的最大值，因为有的事务可能在创建ReadView之前就已经提交，也就不会出现在m_ids中。
判断依据
- 访问的数据行版本TRX_ID等于creator_trx_id，代表当前事务在访问自己修改的数据，可以访问。
- 访问的数据行版本TRX_ID小于min_trx_id，说明当前事务开启前，该版本的事务已经提交，可以访问。
- 访问的数据行版本TRX_ID大于max_trx_id，说明该版本的事务在ReadView创建之后开启，不可访问。
- 访问的数据行版本TRX_ID在min_trx_id和max_trx_id之间，则要判断是否在m_ids中，如果在：说明事务活跃，不可访问。如果不在：说明事务已经提交，可以访问。
1、看不到ReadView创建以后启动的事务
2、看不到ReadView创建时活跃的事务
INNODB_TRX
活跃的事务信息保存在：INFORMATION_SCHEMA.INNODB_TRX 表中，采用 MEMORY 存储引擎，速度非常快。
事务ID是递增的，且 读和写的事务ID是分开记录的。
查看活跃的事务ID
BEGIN;
SELECT * FROM t;
SELECT trx_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
COMMIT;
-- 读 281479607707384
BEGIN;
UPDATE t SET c = 99 WHERE a = 6;
SELECT trx_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID = CONNECTION_ID();
COMMIT;
-- 写 8206301
锁
锁的类型
- 读锁：共享锁、S锁
- 写锁：排他锁，X锁
只有“读读”锁不会冲突，对于一个资源，可以加多个读锁，其他锁加多个均会冲突、阻塞。
SELECT操作不会加任何锁，InnoDB利用MVCC来支持高并发，读都可以读，但不能同时写。
手动加锁
读锁
-- 事务A 加读锁
begin;
select * from t where a = 1 lock in share mode;
-- 事务B
-- 可以查询
select * from t where a = 1;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | 1 | 1 |
+---+------+------+
-- 可以再加读锁
select * from t where a = 1 lock in share mode;
-- 再加写锁 会阻塞
select * from t where a = 1 for update;
写锁
-- 事务A 加写锁
begin;
select * from t where a = 1 lock in share mode;
-- 事务B
-- 再加读锁/写锁 均会阻塞
-- SELECT查询是可以，查询不会加任何锁
锁的范围
表锁
-- 读锁
lock tables t read;
-- 写锁
lock tables t write;
行锁
-
LOCK_REC_NOT_GAP
行记录锁，只锁住 数据行。 -
LOCK_GAP
间隙锁，锁定一个范围，但不包括记录本身。 -
LOCK_ORDINARY
间隙锁，锁定一个范围，且包括记录本身。
对于不同的事务隔离级别，InnoDB锁住的数据范围不同。
READ COMMITTED 锁范围
-
使用主键
只对主键对应的数据行加锁。 -
使用唯一索引
只对唯一索引和对应数据行加锁。 -
使用普通索引
对满足条件的索引和对应数据行加锁。 -
不使用索引
对满足条件的索引和对应数据行加锁。
REPEATABLE READ 锁范围
-
使用主键
只对主键对应的数据行加锁。 -
使用唯一索引
只对唯一索引和对应数据行加锁。 -
使用普通索引
对符合条件的索引、数据行以及间隙加锁。 -
没有使用索引
对表中所有的记录和间隙加锁。
意向锁
- IS锁：意向共享锁。
- IX锁，意向排他锁。
当事务在数据行中加锁时，会先在表中加对应的意向锁。
目的：为了快速判断表中的数据行是否有加锁，没有意向锁标记，需要遍历每一行去判断。
死锁
多个事务在互相占用对方锁住的资源，都在等待对方释放锁，造成死循环。
-- 事务A
begin;
select * from t where a = 1 for update;
-- 事务B
begin;
select * from t where a = 2 for update;
-- 事务A
select * from t where a = 2 for update;-- 阻塞
-- 事务B
select * from t where a = 1 for update;-- 死锁
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
MySQL有死锁检测机制，一旦发现死锁会自动回滚占用资源相对较少的事务来释放锁。
使用合理的索引，可以减少死锁的概率。
扫一扫
4465
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
