MySQL锁整理

I.实验目的

通过对mysql中各种读写操作的加锁情况的分析, 解构死锁场景, 辅助分析死锁出现的原因, 以更高效地解决死锁问题.

 II. 实验原理

在这里主要对一些做实验需要了解的知识和概念进行介绍.

事务

死锁的主体其实就是事务(transaction), 所以讲死锁要讲一下事务的概念和属性.

事务是sql语句执行的单位, 事务在需要更新数据的时候, 需要加锁以保证一致性.

事务在执行的时候, 需要满足ACID4个属性:

索引

Innodb的索引主要分两类: 二级索引(secondary index)和聚簇索引(clustered index). 

二级索引就是我们平时用的最普通的索引, 而聚簇索引不仅存储了索引key值, 还存储了真实的记录内容，同时还会存储事务ID和回滚指针. Innodb得一般实践是在主键上面创建了聚簇索引, 又叫primary index, 跟secondary index相对.

锁

数据库通过锁来保证读写操作的一致性.

Innodb中使用的最多的行级锁(record lock), 所有的行级锁都是作用在索引上面的. Innodb通过用行锁代替表锁大大提高了对并发的支持. 

从模式层面分, 锁主要包括读锁(shared lock, 后面简称S锁)和写锁(exclusive lock, 后面简称X锁), 这个读写的概念, 在行级锁和表级锁中都有体现. 

与行级锁对应的还有表级锁(table lock, 简单来说就是锁定整张表, 比如要删除所有数据), 意向锁(intension lock, 按读写分, 后面简称IS, IX).

对某张表加意向锁表示有对该表加同类型(X,S)的行锁的操作. 通俗点讲, 意向锁就是行锁同表锁进行交涉的代言人, 如果意向锁与某个表锁不兼容, 其实表示表上面加的某个行锁同表锁不兼容. 

关于锁之间的兼容性问题, 有个经典的表格, 表格里面的X表示的表锁, 未涉及行锁.

行锁之间基本符合上图中X和S之间的兼容关系. 

行锁有可以细化分类, 包括普通记录锁(record lock), 间隙锁(gap lock), next-key锁. 

普通记录锁就是之锁定一个索引的entry, 而gap lock会锁定一个开区间(a,b), next-key锁是record lock和gap lock的聚合, 锁定一个左开右闭的区间, (ab]. 这3种锁如何锁定, 如何确定范围, 会在后面的试验中讲到, 这里就先不举例子了. 

III. 实验环境准备

mysql版本: 5.6.16-64.2 Percona Server.

隔离级别:repeatable read.

数据库引擎设置: innodb.

创建测试表:

CREATE TABLE `test_lock` (

  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,

  `key_uniq` varchar(100) NOT NULL DEFAULT '',

  `name_index` varchar(100) NOT NULL DEFAULT '',

  `code_index` varchar(100) NOT NULL DEFAULT '',

  `no_index` varchar(100) NOT NULL DEFAULT '',

  `int_index` int(11) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_key_uniq` (`key_uniq`),

  KEY `idx_code` (`code_index`),

  KEY `idx_name` (`name_index`),

  KEY `idx_int` (`int_index`)

) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4;

添加测试数据:

INSERT INTO `test_lock` VALUES (1,'uniq1','fangxuexxx','beijing_city','tt',0),(2,'uniq2','xx','beijing_city','tt',0),(3,'uniq3','bbb','shanghai_city','tt',0),(5,'uniq4','dd','kkk','tt',0),(6,'uniq0','aa','beijing_cit','jump',0);

开启查看事务的加锁状态的设置, 具体可参考如下两篇wiki:

一个是innodb的, 一个是percona的.

开启innodb开关后, 可以看到锁定了哪张表, 哪个索引.

开启了percona的开关之后, 可以看到具体锁定了那条记录, 以及锁定的范围.

https://dev.mysql.com/doc/refman/5.6/en/innodb-enabling-monitors.html

https://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_show_status.html

IV. 实验方法

在一个transaction里面, 执行sql语句, 并在transaction提交前, 检查语句加的锁.

transaction	hint
begin;
your sql sequence;	执行相关要测试的sql语句
select trx_id from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID()\G	检查当前事务的id.
show engine innodb status \G	查看innodb 的状态, 在其中找当前事务加锁的信息, 包括当前事务到底加了几种锁, 加了几把锁, 每把锁锁定了什么索引的什么位置或者范围.
rollback;	防止数据发生变化, 直接回滚.

V. 实验过程

1. 一致性非锁定读.

所谓一致性非锁定读, 就是指普通的select语句. 这样的语句是不加任何锁的. 

如果不锁定, 那数据被修改了怎么办? Innodb设计了MVCC机制, 简单讲就是CopyOnWrite, 更新的时候会创建快照. select语句去读取快照数据. 在对于数据更新的实时性要求不高的情况下, 一致性非锁定读极大提高了并发能力.

2. 一致性锁定读

为什么还要有锁定读呢? 因为后面的计算可能会依赖我们读到的数据, 这个数据如果变更, 那后面的计算的结果就是错的. 

举个例子, 我在ATM查到账户有10000元, 银行未锁定这个读, 我在提款之前用支付宝转走了8000, 然后我又在ATM取了5000. 如果中间没有锁定, 银行就赔了.

锁定读分为共享的锁定读和互斥的锁定读.

select * from test_lock lock in shared mode;

select * from test_lock for update;

lock in shared mode 和for update 区别仅仅是, for update 加的所有锁都是X锁, lock in shared mode加的都是S锁, 其他情况完全相同. 

而for update的加锁情况和update基本一致, 请看后面的讲解. 

3. update by primary key

sql序列:

begin;

update test_lock set int_index = 101 where id = 1;

select trx_id  from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID()\G

show engine innodb status \G

rollback;

在查看Innodb 状态的时候, 可以看到如下信息:

2 lock struct(s)说明加了两种类型的锁, 一个是IX锁, 因为要对表里面的一行记录进行更新. 另一个是行级锁, 锁定在Primary索引上面. 

前面我们讲到行级锁也分3种, record lock, gap lock, next-key lock. 日志里面'lock_mode X locks rec but not gap'说明是锁住了记录, 而没有锁定间隙, 这是一个普通的记录锁.

日志底部还有8行, 这8行说明了主键索引到底存储了哪些东西.

从uniq1, fangxuexxx这样的字样可以确认出来, 这是整条记录所有的字段. 其实还包括事务id和回滚指针, 不展开讲这个问题. 

关于Innodb status 输出的日志的格式, 没有特别完整的wiki介绍, 想要更深入了解的同学可以看一下这里的ppt:

https://www.percona.com/live/mysql-conference-2015/sessions/understanding-innodb-locks-and-deadlocks

4. update by unique key

sql序列:

begin;

update test_lock set int_index = 123456 where key_uniq = 'uniq4';

select trx_id  from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID()\G

show engine innodb status \G

rollback;

在查看Innodb状态的时候, 可以看到如下日志信息:

由于有更新, 肯定会有一个IX锁, 后面的实验我们将略过IX锁不讲.

可以看到这个加了两个行锁.一个锁定了idx_key_uniq, 一个锁定了primary.

idx_key_uniq上面的锁, 是一个普通行锁, 锁定了'uniq4'这个entry. secondary的索引entry里面存两个字段, 一个是索引的值: '0: len 5; hex 12345678; asc uniq4;;', 一个是主键的值: '1: len 4; hex 000000005; asc ;;'.

primary索引上面的锁加在id=5的记录上面, 不赘述. 

比较试验3和4, 可以看出, 根据unique key更新, 比根据主键多锁住了一个索引, 也就是unique key对应的索引. 同样可以推理出一个普适的结论, 只要是实际会被更新的记录, 都会在对应的primary index的entry上面加一个锁.

5. update by primary key (key not exists)

sql 序列:

begin;

update test_lock set int_index = 123456 where id = 4;

select trx_id  from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID()\G

show engine innodb status \G

rollback;

那么如果我们要更新的记录不存在, 会不会加锁呢? 如果不会加锁, 是否合理? 如果要加锁, 会加什么样的锁?

看一下这次的Innodb status 日志:

(⊙o⊙)哦, 加了锁的. 那么为什么记录不存在还要加锁呢? 为了保证一致性, 删除的时候如果记录不存在, 那么在当前事务提交之前, 记录都不能存在, 即本事务中看到不存在的记录不能被其他事务插入. 

可以看到加了一把行锁, 锁描述为'lock_mode X locks gap before rec', 说明这是一把间隙锁. 

那么如何判断这把间隙锁锁定的gap呢? 'gap before rec'说明下面紧跟着的索引的位置就是gap的右边界id=5. 间隙锁从当前索引(id=5)向前延伸至最近的存在的index entry, 在此情境下即id=3, 所以当前gap锁定的区间是(3,5).

一个感性的思考gap锁定范围的方法是, 当前要删除4, 4不存在, 那么向右找到了5, 向左找到了3, 便锁定了(3,5).

gap锁就是锁定区间, 不允许在这个区间内修改数据, 不能删除, 也不能插入.

对于记录不存在的情况, gap 锁的锁定范围有几个特殊情况:

1, 把本实验里要删除的id改为100, 查看加锁情况. 

会发现锁定的右边界是supremum(上确界, 即整数的最大值).

2, 把本实验里面要删除的id改为0, 查看加锁情况.

3, 创建一张空表, 尝试删除一条数据, 查看加锁情况. 

这三个特殊情况, 读者可以自己check一下.

6. update by secondary key

begin;

update test_lock set int_index = 777 where code_index = 'beijing_city';

select trx_id  from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID()\G

show engine innodb status \G

rollback;

Innodb status如下: 

问题变得更复杂了. 

两个索引上面被加了锁, primary和idx_code. 

primary上面加了两把锁, 分别锁定了id=1和id=2, 因为这两条记录的city_code='beijing_city'.

再看idx_code上面的锁, 加了3把锁.

上面的两把锁描述为'lock_mode X', 说明这是next-key锁, 会锁定左开右闭的一个区间. 锁定范围的推理方法跟gap lock类似, 分别锁定了(beijing_cit, beijing_city], (beijing_city, beijing_city].

第三把锁是一个间隙锁, 锁定了(beijing_city, kkk).

数轴的排序是按照数字大小, 字符串则是按照字典序(lexical order).

把idx_code上面的3把锁的区间合并一下: (beijing_cit, beijing_city] + (beijing_city, beijing_city] + (beijing_city, kkk)=(beijing_cit, kkk), 即这个区间被锁定了. 

idx_code的区间锁定, 我们来用数轴来解释一下. 假定存在的key有1,3,5,8, 那么如果要更新5, 锁定区间是(3,8). 而在前面的update by unique key的实验里, 如果key 有1,3,5,8, 要更新5, 只会锁定5自己. 为什么两者有区别呢? 为什么secondary key要锁定一个更大的gap呢? 因为unique key如果key=5存在, 那么就不会存在第二个key=5的entry.而secondary key没有这样的唯一性约束, 可以在(beijing_cit, kkk) 区间内存在多个beijing_city记录, 那么执行更新前后状态要保持一致, 在这个区间内既不能insert, 也不能delete, 所以要锁定这个区间. 

7. update by non-index attribute

sql序列:

begin;

update test_lock set int_index = 123 where no_index = 'jump';

select trx_id  from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID()\G

show engine innodb status \G

rollback;

innodb status日志如下:

这里对每一个primary index的entry都加了next-key锁. 

因为no_index字段上面没有索引, 所以这个sql退化为了基于聚簇索引的全表扫描, 那么每一个被扫到的都可能更新, 所以就都加了锁.

对每条记录都加next-key锁, 基本等价于锁表, 增删改都被会block, 所以大家以后开发sql语句的时候也要尽量避免这样的问题. 

8. delete

sql 序列:

begin;

delete from test_lock where id = 1;

select trx_id  from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID()\G

show engine innodb status \G

rollback;

Innodb 状态如下:

对id=1的记录, 加了行锁. 

这个case 和update by primary key加锁情况一样, 更多的实验可以证明, delete加锁情况跟update 基本一致. 

9. insert

sql 序列:

begin;

update test_lock set int_index = 123 where no_index = 'jump';

select trx_id  from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID()\G

show engine innodb status \G

rollback;

Innodb status 日志如下:

这个日志其实有点诡异, 因为只有一个IX锁, 既然没有看不到实际存在的行锁, 为何还要加IX锁呢? 不过insert确实是一个更新操作, 没有加锁也不太科学. 

那么如何继续追查呢? 可以尝试用另一个transaction去检验.

实验过程如下:

transaction1	transaction2	hint
begin;	begin;
insert into test_lock (key_uniq, name_index, code_index) values ('xxx', 'fangxuexxxx', 'code_index_123');
select * from test_lock;		通过transaction1中一个select 查询可以看到插入的id=34
	delete from test_lock where id = 34;	可以看到这个delete语句其实被block住了, 说明有其他事务加了锁.
show engine innodb status \G		在这里检查transaction1的加锁情况.
rollback;	rollback;

运行上面表格的实验, 查看事务的加锁情况的时候, 看到事务1加的锁跟上面一个事务的时候不同了!!!

Innodb status如下:

可以看到事务1对id=34的记录加了一个row lock. 这里看到的id内容是'0: len 4;hex 00000022; asc ";;' 22是34的十六进制表达. 

综述, insert会对插入的记录加一个行锁, 会和跨越这个记录的gap lock发生冲突, 但是在没有block的时候, insert加的锁没有在日志里面显示, 这也为我们分析死锁增加了更多的阻力.

10. update attributes

对于被更新的字段, 是否会加锁呢?

sql 序列:

transaction1	transaction2	hint
begin;	begin;
update test_lock set code_index = 'www' where id = 1;
show engine innodb status \G	这里可以看到, 就加了一个行锁.
	update test_lock set name_index = 'ppp' where code_index = 'beijing_city';	会被block.
show engine innodb status \G		这里可以看到transaction1比上一次查看状态多了一把对idx_code加的锁.
rollback;	rollback;

transaction1中第二次 show engine innodb status 查看的内容如下:

可以推出的结论是, 更新的属性上面如果有索引, 那么其实也是会加锁的, 会加一个普通行锁. 

VI. 实验心得

进行SQL编程的时候, 不是读就是写, 读的时候分析性能的瑞士军刀是explain, 那么写的时候, show engine innodb status\G就是分析锁定情况, 优化并发的的利器. 

在分析锁定, 死锁的问题时, 最有效的方法就是实验, 实践出真知.

工作中遇到的死锁场景会比我们现在分析的case更加复杂, 真正处理问题的时候, 更要结合实际的业务, 分析死锁问题并及时规避. 

VII. 思考题

分析下面语句的加锁情况:

1, update test_lock set name_index = 'fangxue' where no_index = 'tt' limit 1;

2,  selectid from test_lock where no_index = 'tt' limit 1 for update;

3, select * from a join b on ... where ... for update.

more  and more cases to be tested.

VIII. 参考资料

Mysql技术内幕: InnoDb存储引擎

High performance Mysql

深入理解mysql核心技术

MySQL官方wiki

Percona wiki.

Innodb加锁实验