一些基础概念
InnoDB中的事务遵循了ACID原则
- A: 原子性，InnoDB中的事务，要么当中的操作全部成功，要么全部失败
- C: 一致性，InnoDB一直处于一致性的状态。假如多条记录(包括跨表的多条记录)在事务中被修改，其它事务在查询时，要么全看到旧的数据值，要么全看到新的数据值，不会出现部分数据是旧值，而另一部分的数据是新值
- I: 隔离性，InnoDB通过锁机制，来实现了事务隔离
- D: 持久性，InnoDB的事务一旦成功提交，那么数据将会持久保存在数据库中——即使发生了突然断电、系统奔溃以及别的一些潜在危险，数据依然安全
事务的隔离级别
Read Uncommit：最松散的级别
- 基本不会使用
- 会读取未提交的数据
- SELECT语句都是无锁模式下执行，会导致脏读
Read Commit：可解决脏读问题
- 简称RC
- 读取已提交的数据
- 解决脏读问题，但存在幻读
- 使用RC级别，还有一些额外的效果：
- 对于update和delete语句，innodb只会保持那些确定要修改或删除的行的锁。而对于那些扫描到、但不符合where条件的行，innodb会在mysql计算了where子句后，会马上释放这些行的记录锁。
- 对于update语句，如果某行已经被锁了，InnoDB会执行一个“半一致性”的读，返回一个最新提交的值到MySQL层，然后MySQL层决定该行是否需要update，如果确认需要update，则MySQL重新读取该行并且InnoDB层尝试锁住该行。
- RC级别下：
- 对于consistent read，都有它们各自的最新的数据快照
- 对于locking-read，update，delete等语句，innodb只锁住索引记录，而不会锁住索引记录之前的间隙
Repeatable Read：MySQL默认的级别，可解决幻读问题
- RR级别，MySQL的默认级别
- 解决了幻读问题
- RR级别下：
- 在同一个事务中，Consistent Reads读取的同一个快照，该快照由第一个read所确定
- 对于locking reads(select ... for update或者select ... lock in share mode)，update，delete语句，则根据该SQL语句是否用到了唯一索引去检索唯一记录，来决定锁的使用情况
- 如果用到了唯一索引去检索唯一的记录，InnoDB则只会在该索引记录上加锁（record lock），而不会加上它之前的间隙锁
- 如果是其它类型的查询，则使用gap lock或next-key lock锁住扫描过的区域，从而阻止了其它会话往间隙中插入数据
Serializabale：最严格的级别，基于锁串行执行事务
- 串行化
- 最严格的隔离级别，但除了某些特定场景，基本不会使用
Consistent read
- 可以称之为一致读/快照读/非锁读
- 即普通的SELECT语句（不带for update或lock in share mode）
- consistent read可以看到某个时刻之前所有已提交的事务所做的修改，而看不到该时刻点之后才提交的事务做的修改
- 当事务隔离级别是 REPEATABLE READ（这也是mysql默认的事务隔离级别）的时候，事务中第一个SELECT语句会确定该事务中所有consistent read所读取的数据库快照。
- 当事务隔离级别是READ COMMITTED时，事务中的每个consistent read都会确定和读取各自的快照。
- 例子:
- 开始一个事务,将name=test1的行更新为name=test
- select * from student 三次查询的时间点分别是:事务开始前,事务进行中,事务结束
加锁读
- 加锁读，即select ... for update 或者 select ... lock in share mode
- Locking read要么被阻塞，要么会返回最新提交的数据
- 例子:阻塞中直到超时,或事务提交后会返回最新数据
幻读
- 在同一个事务中，同样一个查询语句，执行两遍，但结果集不一样。例如，一个SELECT语句执行了两次，但第二次执行时返回了一行，而该行数据并没有在第一次执行时返回，那么该行就成为phantom row。
- 隔离级别 repeatable read可以解决幻读问题
- 隔离级别 read commited，会导致幻读
- 例子:
- 事务隔离级别为Read commit时，幻读才会出现
- set session transaction isolation level READ COMMITTED;
- 同一事务中读到的test2的score不同.
- 事务隔离级别为Read commit时，幻读才会出现
InnoDB中的锁
- 查看锁的详细信息
- 设置系统变量 innodb_status_output_locks=ON，然后执行命令show engine innodb status 就可以看到。
- set GLOBAL innodb_status_output_locks=ON;
- show engine innodb status;
- 输出中TRANSACTIONS一节有锁的信息。
- 设置系统变量 innodb_status_output_locks=ON，然后执行命令show engine innodb status 就可以看到。
- 查看锁的使用概况
- show status like 'Table_locks%';
- Table_locks_immediate
- Table_locks_waited
- show status like 'Table_locks%';
锁的分类
1. Shared and Exclusive Locks
- 共享锁和排它锁，又称为读写锁。
- InnoDB实现了标准的行级锁。行级锁又分共享锁和排它锁。
- 共享锁（s）：允许持有该锁的事务去读取该行
- 排它锁（x）：允许持有该锁的事务去更新或删除该行
- 如果事务T1持有了行A的共享锁，那么其它的事务都可以持有行A的共享锁并读取行A。但如果有事务此时要获取行A的排它锁，则事务会被挂起，直到其它事务释放了行A的共享锁。
- 如果事务T1持有了行A的排它锁，其余事务无论申请的是行A的共享锁抑或排它锁，都需要等待T1释放排它锁。
2. Intention Locks
- 意图锁，是表级别的锁
- 意图锁有两种
- Intention shared (IS)：事务T意图在表t中获取某些行的s锁
- Intention exclusive (IX)：事务T意图在表t中获取某些行的x锁
- 意图锁有如下协议在一个事务获取表t中的某行的s锁之前，事务必须事先获取到了表t的IS锁或IX锁
- 在一个事务获取表t中的某行的x锁之前，事务必须事先获取到表t的IX锁
- 兼容性矩阵
-
X
IX
S
IS
X
Conflict
Conflict
Conflict
Conflict
IX
Conflict
Compatible
Conflict
Compatible
S
Conflict
Conflict
Compatible
Compatible
IS
Conflict
Compatible
Compatible
Compatible
- 例子：
-
语句SELECT ... LOCK IN SHARE MODE会在指定表上获取IS锁
语句SELECT ... FOR UPDATE则会在指定表上获取IX锁
语句LOCK TABLES ... WRITE会在指定表上获取X锁
语句LOCK TABLES ... READ会在指定表上获取S锁
3. Record Locks
- 记录锁，最典型的行级锁
- A record lock is a lock on an index record，锁的是索引
- 即使一个表没有设置索引，它依然会有一个隐藏的主键索引的
- 对于二级索引，除了会锁住二级索引上的记录外，也会在主键索引上加上记录锁
4. Gap Locks
- 间隙锁
- 间隙锁是用来锁住索引之间的间隙的，也包括第一条索引之前以及最后一条索引之后的区域。
- 一个直观的例子：SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;会锁住列c1的值在[10, 20]之间的间隙，从而会阻止其它事务插入一条c1=15的记录
- 间隙锁是性能和并发之间的权衡，RC级别下不使用，RR级别使用
- 间隙锁的作用是用来防止“幻读”
- 什么是间隙？
- 并不是说GAP是一个不包含任何记录的区域，而是指某个范围(range)，一个gap可能会包含(跨越span)一条或多条索引，也有可能是一个空的间隙。
- 还是以SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;为例，如果列c1上有索引，那么会在对应gap上加锁。但如果c1上没有索引，那么间隙锁会跨域整张表，相当于锁住了整表。
- 如果使用了唯一索引（包括主键索引）来查询唯一的记录（查询到结果），那么在此场景下，间隙锁不会被使用。
- 假设id是唯一索引（或主键索引），并且确实存在id=100的记录，那么以下SQL将不会发生间隙锁：SELECT * FROM child WHERE id = 100 FOR UPDATE;
- 否则，那么上述SQL将会锁住间隙
- 间隙锁是"purely inhibitive"，意思是说间隙锁只会阻止其它事务向当前间隙插入新纪录，而不会阻止不同的事务在同一间隙中获取间隙锁
5. Next-key Locks
- next-key lock是索引上某条记录的的记录锁(record lock)和该记录之前的间隙锁的组合。
- next-key lock主要用来防止幻读。
- 需要注意的是：next-key lock并不等同于record lock+gap lock两个独立的锁，next-key lock本质上应该是一个锁，但锁住了记录和gap。
- 因此， next-key lock和record lock可能会冲突的，一个事务拥有了记录R的record lock，并不代表该事务百分百可以拥有R的next-key lock。[通常是为了防止饥饿而导致的]
- Next-key locks是如何防止“幻读”的：
- 当InnoDB搜索某个索引时，它会将扫描过的索引记录加上S锁或X锁，InnoDB中的行级锁实际是索引记录锁。此外，一个next-key锁除了锁住了一条索引记录外，也会锁住该记录之前的间隙。next-key lock是索引上某条记录的的记录锁(record lock)和该记录之前的间隙锁(gap lock)的组合。
- 这样一来，当一个会话在某个索引记录R上拥有了S或X锁后，根据next-key的策略，另一个会话将无法在R之前的间隙插入记录。
6. Insert Intention Locks
- 插入意图锁
- 用于插入操作之前。插入意图锁的目的是表明一种插入意图，从而使得多个事务同时向同一个gap插入新纪录(但不是同一个插入位置)时，可以避免他们相互等待而造成死锁。
- 例如当前索引上有记录4和8，两个并发的session同时插入记录6，7。他们会分别为(4,8)加上GAP锁，但相互之间并不冲突。
7. Auto-INC Locks
- 自增锁。
- 自增锁是一个表级别的锁，用于自增的列。
MySQL InnoDB的加锁分析
- MySQL和InnoDB是不同的层次
- 流程：
- 当Update SQL被发给MySQL后，MySQL Server会根据where条件，发起一次locking read，读取第一条满足条件的记录
- InnoDB对第一条满足条件的记录加锁，并返回给MySQL Server
- MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录
- 一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止
- 结论：Update操作内部，就包含了一个locking read。同理，Delete操作也一样。Insert操作会稍微有些不同，但当有唯一索引约束或外键约束时，同样会包含了一次locking read
- 以SQL语句 delete from t1 where id = 10 为例，逐一分析以下情形的加锁情况
- 根据事务隔离级别和id列的索引情况
死锁
- id是主键 + RC 隔离级别
- id是唯一索引 + RC 隔离级别
- id是非唯一索引 + RC 隔离级别
- id无索引 + RC 隔离级别
- id是主键+ RR 隔离级别：与id是主键+ RC 相同
- id是唯一索引+ RR 隔离级别：与id是唯一索引+ RC 相同
- id是非唯一索引+ RR 隔离级别
- id是非唯一索引+ RR 隔离级别
- 死锁无可避免——即使事务中只有一条SQL语句，在高并发的场景下也有可能导致死锁
- InnoDB可以自动检测出死锁
- MySQL 5.7增加了innodb_deadlock_detect的配置项，可用来禁止死锁检测
- 但不要随便禁止它，除非：
- 业务上确保死锁极少，SQL都经过严格审核
- 经过充分测试后，性能确实提升明显
- 如果真的禁止了，那只能等到锁超时才能回滚事务，因此需要调小innodb_lock_wait_timeout这个值
- 如何尽可能避免死锁：
- 如有可能，可考虑降低事务隔离级别
- 总是使用良好设计的索引（尤其优先使用唯一索引或主键索引）来访问或操作数据
- 同一个表，也尽量使用固定的索引（尤其推荐主键索引或唯一索引）来操作数据
- 不同的方法访问同一组表时，尽量约定以相同的顺序来访问各个表；对于同一个表，也尽量约定以相同的顺序来访问不同的行
- 事务中不要掺杂过多的业务逻辑（尤其不能在事务中去调用API），让事务尽量小、尽量快结束
- 根据事务隔离级别和id列的索引情况
参考自:
241
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
