mysql 加锁分析与实践

一序 本文分为两个部分，第一部分主要基于何登成大神的文章。何博士作为阿里数据库内核团队大神。文章更是深入浅出。膜拜一下：原文地址如下 http://hedengcheng.com/?p=771 第二部分介绍常见的实践注意事项。 二 背景 MVCC：Snapshot Read vs Current Read MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) 在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。 在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例： 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析) select * from table where ?; 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。 select * from table where ? lock in share mode; select * from table where ? for update; insert into table values (…); update table set ? where ?; delete from table where ?; 所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁) 其他相关知识点： 聚集索引（主键索引） 官网介绍、 2PL：Two-Phase Locking （锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交） 隔离级别（官网介绍：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html） Read Uncommited，Read Committed，Repeatable Read，Serializable，不做重复介绍。 三 加锁分析case SQL1：select * from t1 where id = 10; SQL2：delete from t1 where id = 10; 需要分析不同的前提，下面是常见的： 组合一：id列是主键，RC隔离级别 组合二：id列是二级唯一索引，RC隔离级别 组合三：id列是二级非唯一索引，RC隔离级别 组合四：id列上没有索引，RC隔离级别 组合五：id列是主键，RR隔离级别 组合六：id列是二级唯一索引，RR隔离级别 组合七：id列是二级非唯一索引，RR隔离级别 组合八：id列上没有索引，RR隔离级别 组合九：Serializable隔离级别 在前面八种组合下，也就是RC，RR隔离级别下，SQL1：select操作均不加锁，采用的是快照读，因此在下面的讨论中就忽略了，主要讨论SQL2：delete操作的加锁。 3.1组合一：id主键+RC 这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL：delete from t1 where id = 10; 只需要将主键上，id = 10的记录加上X锁即可。 结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。 3.2 组合二：id唯一索引+RC 此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。 结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。 3.3 组合三：id非唯一索引+RC 根据此图，可以看到，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。 结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。 3.4组合四：id无索引+RC 由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。 注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。 结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。 3.5 组合五：id主键+RR 上面的四个组合，都是在Read Committed隔离级别下的加锁行为，接下来的四个组合，是在Repeatable Read隔离级别下的加锁行为。 组合五，id列是主键列，Repeatable Read隔离级别，针对delete from t1 where id = 10; 这条SQL，加锁与组合一：（id是主键，Read Committed隔离级别） 一致。 结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。 3.6 组合六：id唯一索引+RR 与组合二：[id唯一索引，Read Committed]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。 3.7 组合七：id非唯一索引+RR 相对于组合三：[id列上非唯一锁，Read Committed]看似相同，其实却有很大的区别。最大的区别在于，这幅图中多了一个GAP锁。 其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP锁应运而生。 如图中所示，有哪些位置可以插入新的满足条件的项 (id = 10)，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。记录[6,c]之前，不会插入id=10的记录；[6,c]与[10,b]间可以插入[10, aa]；[10,b]与[10,d]间，可以插入新的[10,bb],[10,c]等；[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等；而[11,f]之后也不会插入满足条件的记录。因此，为了保证[6,c]与[10,b]间，[10,b]与[10,d]间，[10,d]与[11,f]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。 Insert操作，如insert [10,aa]，首先会定位到[6,c]与[10,b]间，然后在插入前，会检查这个GAP是否已经被锁上，如果被锁上，则Insert不能插入记录。因此，通过第一遍的当前读，不仅将满足条件的记录锁上 (X锁)，与组合三类似。同时还是增加3把GAP锁，将可能插入满足条件记录的3个GAP给锁上，保证后续的Insert不能插入新的id=10的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。 既然防止幻读，需要靠GAP锁的保护，为什么组合五、组合六，也是RR隔离级别，却不需要加GAP锁呢？GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合五，id是主键；组合六，id是unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就避免了GAP锁的使用。 结论：Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。 3.8 组合八：id无索引+RR 结论：在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。 组合九：Serializable Serializable隔离级别，影响的是SQL1：select * from t1 where id = 10; 这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。 结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。 普通的并发插入导致的死锁 create table t1 (a int primary key); 开启三个会话执行： insert into t1(a) values (2); session 1session 2session 3 BEGIN; INSERT.. INSERT (block),为session1创建X锁，并等待S锁 INSERT (block， 同上等待S锁) ROLLBACK，释放锁 获得S锁获得S锁 申请插入意向X锁，等待session3 申请插入意向X锁，等待session2 上述描述了互相等待的场景，因为插入意向X锁和S锁是不相容的。这也是一种典型的锁升级导致的死锁。如果session1执行COMMIT的话，则另外两个线程都会因为duplicate key失败。 这里需要解释下为何要申请插入意向锁，因为ROLLBACK时原记录回滚时是被标记删除的。而我们尝试插入的记录和这个标记删除的记录是相邻的(键值相同)，根据插入意向锁的规则，插入位置的下一条记录上如果存在与插入意向X锁冲突的锁时，则需要获取插入意向X锁。 又一个并发插入的死锁现象 两个会话参与。在RR隔离级别下 例表如下： create table t1 (a int primary key ,b int); insert into t1 values (1,2),(2,3),(3,4),(11,22); session 1session 2 begin;select * from t1 where a = 5 for update;(获取记录(11,22)上的GAP X锁) begin;select * from t1 where a = 5 for update; (同上,GAP锁之间不冲突 insert into t1 values (4,5); (block，等待session1) insert into t1 values (4,5);（需要等待session2，死锁） 引起这个死锁的原因是非插入意向的GAP X锁和插入意向X锁之间是冲突的。 四 数据库实践 4.1 设计阶段 表引擎选择使用innodb不适用myisam: 由于myisam引擎只支持table lock，在使用myisam引擎表过程中，当数据库中出现执行时间较长的查询后就会堵塞该表上的更新动作，所以经常会碰到线程会话处于表级锁等待（Waiting for table level lock）的情况. 表索引设计：避免索引死锁或者index merge。 4.2 开发阶段 因为已经使用了阿里云的RDS数据库，通常是dba监控慢SQL或者异常。 事务处理时间过长，导致并发出现锁等待。 并发事务处理在数据库中经常看到的应用场景，在这种场景下，需要避免大事务，长事务，复杂事务导致事务在数据库中的运行时间加长，事务时间变长则导致事务中锁的持有时间变长，影响整体的数据库吞吐量。 4.3 维护阶段 DDL操作被大查询block。 当应用上线进入维护阶段，则开始会有较多的数据库变更操作，比如：添加字段，添加索引等操作，这一类操作导致的锁故障也是非常频繁的. 尽量设计表保留拓展字段，保留json类型数据。这种字段没法做索引，也不要太长，只做必要的业务展示或者中转。 Metadata lock wait 的含义：为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此 MySQL 引入了 metadata lock ，来保护表的元数据信息。因此在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在 Metadata lock wait。 导致 Metadata lock wait 等待的常见因素包括：活动事务，当前有对表的长时间查询，显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，表上有失败的查询事务等。 总结： 设计开发阶段: 表设计要避免使用myisam存储引擎，改用innodb引擎； 为SQL创建合适的索引，避免多个单列索引执行出错； 避免大事务，长事务，复杂事务导致事务在数据库中的运行时间加长。 管理运维阶段： 在业务低峰期执行上述操作，比如创建删除索引； 在结构变更前，观察数据库中是否存在长时间运行的SQL，未提交的事务； 结构变更期间，监控数据库的线程状态是否存在lock wait。