MySQL锁的总结 和 一次插入意向锁的死锁还原分析

锁总结

为什么要加锁？

锁是用于管理对公共资源的并发控制。
也就是说并发的情况下，会出现资源竞争，所以需要加锁。

有什么锁？

MySQL使用悲观锁的思想，假设自己每次拿数据别人都会修改，操作前先拿锁。
InnoDB引擎的锁粒度有表锁和行锁。

常见的三种锁

共享锁(S Lock)也称读锁，允许两个以上事务同时读一行数据。
排他锁(X Lock)也称写锁，允许当前事务删除或更新一行数据。

InnoDB引擎中自动使用的是表级意向锁，分为两种：
意向共享锁(IS Lock) 事务想要获得一张表中某几行的共享锁
意向排他锁(IX Lock) 事务想要获得一张表中某几行的排他锁

其中 意向锁 将锁定对象分为多个层次，看做上图的树形结构。
如果要对页的记录row上X锁，那么得分别给数据库A、表、页上意向锁IX，最后才能对记录row上X锁。

常见的五种锁模式

记录锁(Record Lock) 单行记录锁，锁住索引记录，如果没有则锁定隐式主键。
间隙锁(Gap Lock)	锁定一个范围，不包含当前行。
临键锁(Next-Key Lock) 默认行查询使用，`记录锁+间隙锁合体`，锁定一个范围，包含当前行。解决幻读(Phantom)，如果是主键且唯一，会降级为记录锁。
意向锁(Intention Locks) 为了支持多粒度(表锁与行锁)的锁并存，引入意向锁，是表级锁。
插入意向锁(Insert Intention Locks) 为了插入时保证正确，范围锁的一种，与Gap锁不兼容。

锁的兼容性

锁模式中的兼容性

如何分析死锁

死锁定的概念：两个及其以上的事务，在执行过程中抢锁造成互相等待的现象。

MySQL的InnoDB引擎有死锁检测机制（等待图 wait-for-graph），如果检测到在同一个记录事务之间存在环，就会立即报出死锁，并且回滚undo量小的事务。

已经发生死锁

查询InnoDB最近一段时间的总体情况，检查 LATEST DETECTED DEADLOCK段

show engine innodb status \G 

日志中会出现的事务状态:

starting index read 			表示事务状态为根据索引读取数据。
fetching rows		 			表示事务状态在row_search_for_mysql中被设置，表示正在查找记录。
updating or deleting 			表示事务已经真正进入了Update/delete的函数逻辑（row_update_for_mysql）
thread declared inside InnoDB 	说明事务已经进入innodb层。通常而言 不在innodb层的事务大部分是会被回滚的。

日志中会出现的四种锁算法：

记录锁（LOCK_REC_NOT_GAP）: 	lock_mode X locks rec but not gap
间隙锁（LOCK_GAP）:	 lock_mode X locks gap before rec
Next-key 锁（LOCK_ORNIDARY）: 	lock_mode X
插入意向锁（LOCK_INSERT_INTENTION）: 	lock_mode X locks gap before rec insert intention

show status只能查最近一次死锁的几条SQL，完整死锁日志需要手动开启。

show variables like 'log_error';				#查看MySQL错误日志，启动加--defaults-file参数指定路径。
set global innodb_print_all_deadlocks = 1;		#开启死锁日志记录，将死锁日志记录到错误日志里面：

正在阻塞中，等待锁释放。

重现死锁的时候，可以在阻塞中很方便的查到当前用的锁状态和事务状态，通过下面的SQL查询。

select * from information_schema.INNODB_TRX \G			当前事务信息	
select * from information_schema.INNODB_LOCKS \G		当前锁的信息
select * from information_schema.INNODB_LOCK_WAITS \G	查看当前事务的等待

记一次死锁还原分析

案例是MySQL版本5.6，隔离级别是Repeatable-Read(可重复读)。
初始化数据

CREATE TABLE `kv` (
  `key` varchar(50) NOT NULL,
  `val` varchar(50) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `kv` SET `key`='a',`val` = '1';

事务2	事务1	旁白
begin;
SELECT * FROM kv WHERE key = ‘b’ FOR UPDATE;		事务2获取X临键锁
SELECT * FROM kv WHERE key = ‘a’ FOR UPDATE;	begin;	事务2获取X记录锁
	SELECT * FROM kv WHERE key = ‘b’ FOR UPDATE;	事务1获取X临键锁
	SELECT * FROM kv WHERE key = ‘a’ FOR UPDATE;	事务1获取X记录锁等待，阻塞
INSERT INTO kv SET key=‘b’,val = ‘2’;		事务2尝试获取X插入意向锁，死锁

备注：间隙锁和间隙锁并不冲突，插入意向锁和间隙锁冲突。又因临键锁是间隙锁+记录锁，所以同理。

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:	#事务1
TRANSACTION 3925, ACTIVE 48 sec starting index read	#事务编号为 3925 ，活跃48秒，starting index read 表示事务状态为根据索引读取数据。
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 8, OS thread handle 0x7fc7ab8c7700, query id 242 0699cfbc3c12 172.25.0.2 root statistics
SELECT * FROM `kv` WHERE  `key` = 'a' FOR UPDATE

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:    	#事务1等待获取锁
RECORD LOCKS space id 12 page no 3 n bits 72 index `PRIMARY` of table `mytest`.`kv` trx id 3925 lock_mode X locks rec but not gap waiting		
#事务1走的是主键索引PRIMARY，等待的锁类型是lock_mode X，locks rec but not gap说明是记录锁。
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) TRANSACTION: #事务2
TRANSACTION 3924, ACTIVE 61 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 7, OS thread handle 0x7fc7ab909700, query id 246 0699cfbc3c12 172.25.0.2 root update
INSERT INTO `kv` SET `key`='b',`val` = '2'

*** (2) HOLDS THE LOCK(S):	#事务2持有的锁
RECORD LOCKS space id 12 page no 3 n bits 72 index `PRIMARY` of table `mytest`.`kv` trx id 3924 lock_mode X locks rec but not gap
#事务2持有的锁类型是lock_mode X，同样是记录锁。
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:	#事务2正在等待被授予。
RECORD LOCKS space id 12 page no 3 n bits 72 index `PRIMARY` of table `mytest`.`kv` trx id 3924 lock_mode X insert intention waiting
#事务2等待授予插入意向锁。
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)	#回滚了权重较低的事务1

分析日志得出：

T2 手上两把锁，一把记录锁，一把临键锁。
T1 手上只拿着一把临键锁，记录锁因为冲突正在等着 T2 松手。
此时 T2 还想拿一把插入意向锁，但是得等 T1 手上的临键锁 松手。
形成环回等待图，InnoDB主动抛出死锁异常，回滚了只有两条SQL的事务1。

为了验证FOR UPDATE没查到b的数据使用了临键锁，有了情况二：

事务2	事务1	旁白
begin;
SELECT * FROM kv WHERE key = ‘b’ FOR UPDATE;	begin;	事务2获取X临键锁
	INSERT INTO kv SET key=‘b’,val = ‘2’;	事务1尝试获取X插入意向锁，阻塞

阻塞时可以用这个SQL查看。

可以看到lock_type虽然是RECORD，但是注意看lock_data显示supremum pseudo-record ，这就是InnoDB为了解决幻读问题的临键锁(Next-key Lock)。
需要注意的是：supremum pseudo-record有可能是间隙锁，需要结合死锁日志里的heap no判断。heap no 1是间隙锁。

死锁总结

1.合理的设计好数据表，每次查询必须用到索引，最好落到聚合索引上，大事务拆分成小事务，死锁的概率很小，具体数学分析可以参考《MySQL技术内幕-InnoDB存储引擎》里的死锁概率。

2.遇到死锁，很大可能是两个或多个事务之间加锁顺序的不一致导致的，顺序不一致很大可能是业务设计得不合理。按照上面的死锁例子，select同一张表里的主键a和b可以拆分成小事务执行，最后有可能会拆分表结构的优化，当然，最后还得具体看业务。

参考资料：
《MySQL技术内幕-InnoDB存储引擎》
Mysql Innodb 中的锁 - 范孝鹏
解决死锁之路（终结篇）- 再见死锁