数据库 -- 锁分析

MySQL 中提供了两种封锁粒度：行级锁以及表级锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。

但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。

1. 表锁
开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
分为两种模式：

• 表读锁（Table Read Lock）
• 表写锁（Table Write Lock）

在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞！

增加表锁： lock table 表1 read/ write
释放表锁：onlock tables;
查看加锁的表： show open tables;
分析表锁定的严重程度： show status like ‘table%’

• table_locks_immediate: 可立刻获取的锁数
• table_locks_waited: 需要等待的表锁数（该值越大，说明存在越大的锁竞争）

建议：table_locks_immediate / table_locks_waited > 5000, 建议采用InnoDB引擎

2. 行锁
开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

InnoDB实现了以下两种类型的行锁。
共享锁（S锁）：读锁共享，会阻止其他事务获得相同数据集的排他锁。
排他锁（X锁)：会阻塞其他的写锁和读锁。

  注：InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表锁。即InnoDB的行锁是基于索引的！

事务可通过以下语句给记录集加共享锁或排他锁。

• 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
• 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE

3. 间隙锁：Gap Locks：

给一条记录加gap锁只是不允许其他事务往这条记录前边的间隙插入新记录。对于最后一条记录之后的间隙，可以给索引中的最后一条记录所在页面的Supremum记录加上一个gap锁。

例如下面的建表语句：

CREATE TABLE `test` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;

//插入数据
insert into t values(0，0),(5，5),(10,10);

如果用 select * from test for update 要把整个表所有记录锁起来，就形成了4个间隙，分别是 (-∞,0] 、 (0,5] 、 (5,10] 、 (10, +supremum] 。

若此时事务 A 执行 select * from test where id = 7 for update 语句，由于 id=7 这一行并不存在，因此会加上间隙锁(5,10);

注：间隙锁是在可重复读隔离级别下才会生效。

4. Next-Key Locks：

既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，行锁与Gap Locks相结合。

• 注1：next-key lock 是前开后闭区间
• 注2 ：索引上的等值查询，给唯一索引加锁时， next-key lock 退化为行锁。
• 注3 ：索引上（不一定是唯一索引）的等值查询，向右遍历时且最后一个值不满足等值条件的时候， next-key lock 退化为间隙锁。

以下案例采用上面那个表，

案例：唯一索引等值查询间隙锁

当事务A执行 update test set c = 2 where id = 9;
根据注1：加锁范围是(5，10]，又因为这是一个等值查询，且id = 10 不满足，所以根据注2，next-key lock 退化为间隙锁 (5，10)。

5. 意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB提供两种意向锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
  
  注：给表加IS锁时，是不关心表是否有IX锁的，同理给表加IX锁时，是不关心表是否有IS锁或者其他IX锁的。IS和IX锁只是为了避免用遍历的方式来查看表中有没有上锁的记录，只有对表加S锁或者X锁时才会用到。

6. 隐式锁
InnoDB在事务执行过程中，使用两阶段锁协议：

• 随时都可以执行锁定，InnoDB会根据隔离级别在需要的时候自动加锁；
• 锁只有在执行commit或rollback的时候才会释放，并且所有锁都在同一时刻被释放。

注：一个事务在执行INSERT操作时，如果即将插入的间隙已经被其他事务加了gap锁，那么本次INSERT操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁，否则一般情况下INSERT操作是不加锁的。

一个事务对新插入的记录可以不显式的加锁（生成一个锁结构），但是由于事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。

7. 死锁

死锁必须具备以下四个条件：

互斥条件：该资源任意一个时刻只由一个线程占用。
请求与保持条件：一个线程因请求资源而阻塞时，对已获得的资源保持不放。
不剥夺条件:线程已获得的资源在末使用完之前不能被其他线程强行剥夺，只有自己使用完毕后才释放资源。
循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

避免死锁的几种方式：

1）以固定的顺序访问表和行。比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；将两个事务的sql顺序调整为一致，也能避免死锁。
2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

** 8. InnoDB锁的内存结构**

对一条记录加锁的本质就是在内存中创建一个锁结构与之关联，那么是不是一个事务对多条记录加锁，就要创建多个锁结构呢？本着勤俭节约，如果符合下边这些条件：

• 在同一个事务中进行加锁操作
• 被加锁的记录在同一个页面中
• 加锁的类型是一样的
• 等待状态是一样的

那么这些记录的锁就可以被放到一个锁结构中。

锁结构简述

• 锁所在的事务信息：不论是表锁还是行锁，都是在事务执行过程中生成的，哪个事务生成了这个锁结构，这里就记载着这个事务的信息。

小贴士：实际上这个所谓的锁所在的事务信息在内存结构中只是一个指针，不会占用多大内存空间，通过指针可以找到内存中关于该事务的更多信息。

• 索引信息：对于行锁来说，需要记录一下加锁的记录是属于哪个索引的。
• 表锁／行锁信息：表锁结构和行锁结构在这个位置的内容是不同的：
  • 表锁：记载着这是对哪个表加的锁，还有其他的一些信息。
  • 行锁：记载了三个重要的信息：
    • Space ID：记录所在表空间。
    • Page Number：记录所在页号。
    • n_bits：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bits属性代表使用了多少比特位。

小贴士：
并不是该页面中有多少记录，n_bits属性的值就是多少。为了之后在页面中插入了新记录后也不至于重新分配锁结构，所以n_bits的值一般都比页面中记录条数多一些。

• type_mode：这是一个32位的数，被分成了lock_mode、lock_type和rec_lock_type三个部分，如图所示：
  
  锁的模式（lock_mode），占用低4位，可选的值如下：

  • LOCK_IS（十进制的0）：表示共享意向锁，也就是IS锁。
  • LOCK_IX（十进制的1）：表示独占意向锁，也就是IX锁。
  • LOCK_S（十进制的2）：表示共享锁，也就是S锁。
  • LOCK_X（十进制的3）：表示独占锁，也就是X锁。
  • LOCK_AUTO_INC（十进制的4）：表示AUTO-INC锁。

  锁的类型（lock_type），占用第5～8位，不过现阶段只有第5位和第6位被使用：

  • LOCK_TABLE（十进制的16），也就是当第5个比特位置为1时，表示表级锁。
  • LOCK_REC（十进制的32），也就是当第6个比特位置为1时，表示行级锁。

  行锁的具体类型（rec_lock_type），使用其余的位来表示。只有在lock_type的值为LOCK_REC时，也就是只有在该锁为行级锁时，才会被细分为更多的类型：

  • LOCK_ORDINARY（十进制的0）：表示next-key锁。
  • LOCK_GAP（十进制的512）：也就是当第10个比特位置为1时，表示gap锁。
  • LOCK_REC_NOT_GAP（十进制的1024）：也就是当第11个比特位置为1时，表示正经记录锁。
  • LOCK_INSERT_INTENTION（十进制的2048）：也就是当第12个比特位置为1时，表示插入意向锁。

is_waiting属性放到了type_mode这个32位的数字中，LOCK_WAIT（十进制的256） ：也就是当第9个比特位置为1时，表示is_waiting为true，也就是当前事务尚未获取到锁，处在等待状态；当这个比特位为0时，表示is_waiting为false，也就是当前事务获取锁成功。

** 9. 一条简单SQL的加锁实现分析**

索引条件下推（ Index ConditionPushdown，简称ICP）：把查询中与索引有关的查询条件下推到存储引擎中判断，而不是返回到server层再判断。其目的是为了减少回表次数，从而减少IO操作，只适用于二级索引, 且只用于SELECT语句。

示例表

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;

9.1 RR级别
使用主键等值查询

SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

主键具有唯一性，在一个事务中下次再执行这个查询语句的时候肯定不会有别的事务插入多条number值为8的记录，这种情况下，与RC一致，加一个S型正经记录锁即可。

如果要查询主键值不存在的记录：

SELECT * FROM hero WHERE number = 7 LOCK IN SHARE MODE;

由于number值为7的记录不存在，为了防止幻读现象，需要在number值为8的记录上加一个gap锁，即别的事务插入number值在(3, 8)这个区间的新记录。

使用主键进行范围查询

SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;

• 为number值为8的聚簇索引记录加一个S型正经记录锁。
• 为number值大于8的所有聚簇索引记录都加一个S型next-key锁。
  

SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

因没有使用索引记录下推，会把记录为15的也加上next-key锁，不过之后server层判断值为15不满足时，RR隔离级别下，并不会去释放加在该记录上的锁！

update语句未更新二级索引列，与上述加锁一致。

UPDATE hero SET country = '汉' WHERE number >= 8;

UPDATE语句中更新了二级索引列：
number值为15的聚簇索引记录不满足number <= 8的条件，虽然在REPEATABLE READ隔离级别下不会将它的锁释放掉，但是也并不会对这条聚簇索引记录对应的二级索引记录加锁。

UPDATE hero SET name = 'cao曹操' WHERE number <= 8;

对于使用普通二级索引进行等值查询

SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

• 当该值存在时：
• 对所有name值为’c曹操’的二级索引记录加S型next-key锁，它们对应的聚簇索引记录加S型正经就锁。
  

对使用普通二级索引进行范围查询

SELECT * FROM hero WHERE name <= 'c曹操' LOCK IN SHARE MODE;

• 为name值为’c曹操’的二级索引记录加S型next-key锁
• 给name值为’l刘备’的二级索引记录加S型next-key锁，name值为’l刘备’的二级索引记录不满足索引条件下推的条件，不会释放掉该记录的锁就直接报告server层查询完毕了。
• 

UPDATE hero SET country = '汉' WHERE name <= 'c曹操';

索引条件下推这个特性只适用于SELECT语句，UPDATE语句中无法使用，需要先进行回表操作，不过之后在判断边界条件时，虽然name值为’l刘备’的二级索引记录不符合name <= 'c曹操’的边界条件，但是在RR隔离级别下并不会释放该记录上加的锁。