5.MySQL行锁、表锁、间隙锁详解

一、表锁

表锁不依赖于储存引擎（不管你是MySQL的什么存储引擎，对于表锁的策略都是一样的），开销小（因为力度大）。表锁不会产生死锁问题，但是表锁会影响并发率。  表锁不依赖索引，当索引失效的时候，行锁会升级成表锁。索引失效的其中一个方法是对索引自动 or 手动的换型。a 字段本身是 integer，我们加上引号，就变成了 String，这个时候索引就会失效了。

二、页锁

页锁就是在 页的粒度 上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我 们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销 介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

三、行锁

在MySQL的InnoDB引擎支持行锁，与Oracle不同，MySQL的行锁是通过索引加载的，也就是说，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。

1、行锁种类

1、Record Lock：单行记录上的锁
2、Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
3、Next-Key Lock：等于Gap Lock + Record Lock，锁定一个范围，
并且锁定记录本身【次策略就是解决数据库泛读的】

锁力度： Next-Key Lock > Gap Lock > Record Lock

Record Lock：单独在记录上加锁适合，比如主键记录更新。例如：select * from user where user_id =7 for update;

间隙锁：适合范围更新，比如范围更新。 例如：select * from user where user_id < 7 for update;

Next-Key Lock：此锁有叫临键锁，是mysql解决幻读试用的。例如：insert语句

2、行锁特点注意

1、行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了
2、两个事务不能锁同一个索引
3、insert，delete，update在事务中都会自动默认加上排它锁

3、行锁的问题

行锁力度Record Lock、Gap Lock、Next-Key Lock会一点点升级，会导致锁定与自己不相干的记录。会严重的影响MySql的插入性能。【Next-Key Lock锁定数据范围】

4、行锁范围

Record Lock：锁定一条记录

Gap Lock：锁定的是间隙

Next-Key Lock: 锁定的是前开后闭区间，例如：锁定[5,+无穷大]

5、行锁优化

1、尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

2、合理设计索引，尽量缩小锁的范围

3、尽可能减少索引条件，避免间隙锁

4、尽量控制事务大小，减少锁定资源量和时间长度

6、表锁与Next-Key Lock区别？

Next-Key Lock 是基于索引的一种行锁需要索引支持，表锁不需要索引支持适合各种引擎。 InnoDB如果表无索引默认主键为索引。 是否能说明InnoDB场景下没有区别，如何证明？

五、其他锁

1、自增长锁

自增锁是一种比较特殊的表级锁。并且在事务向包含了 AUTO_INCREMENT 列的表中新增数据时就会去持有自增锁，假设事务 A 正在做这个操作，如果另一个事务 B 尝试执行 INSERT语句，事务 B 会被阻塞住，直到事务 A 释放自增锁。

2、外键锁

外键主要用于引用完整性得约束检查。在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样可以避免表锁。对于外键值的插入或更新，首先需要查询父表中的记录，即SELECT 父表.但是对于父表的SELECT 操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题。因此这时使用的是 SELECT .... LOCK IN SHARE MODE 方式，即主动对父表加一个S锁，如果这时父表上已经这样加X锁，子表上的操作会被阻塞。

六、死锁

1、死锁概率

死锁应该非常少发生，若经常发生，则系统是不可用的。此外，死锁的次数应该还要少于等待，因为至少需要2次等待才会产生一次死锁，从数学角度来分析，死锁发生的概率问题

当前数据库中有n+1个线程执行，即当前总共有n+1事务，假设每个事务所做的操作相同，若每个事务由r+1个操作组成，每个操作从R行数据中随机操作一行数据，并占用对象的锁，每个事务在执行完最后一个步骤释放锁占用的所有锁资源，最后，假设nr<<R即线程操作的数据只占所有数据的一小部分

1、在上述模型下，事务获得一个锁需要等待的概率是多少？当事务获得一个锁，其他任何一个事务获得锁的情况为

(1+2+3...+r)/(r+1) ≈ r/2

2、由于每个操作为从R行数据中取一条数据，每行数据被取到的概率为1/R,因此，事务中每个操作需要等待的概率PW为

PW=NR/2R

3、事务由r个操作锁组成，因此事务发生等待的概率PW(T)为

PW(T)=1-(1-PW)r≈r*PW≈nr2/2R

死锁是由于产生概率，也就是事务互相等待发生的，如果死锁的长度为2，即两个等待的节点间发生死锁，那么其概率为

一个事务发生死锁的概率≈PW(T) 2/n≈nr4/4R2

由于大部分死锁的长度为2，因此上述的公式基本代表了一个事务发生死锁的概率。从整个系统来看，任何一个事务发生死锁的概率为

系统中任何一个事务发生死锁的概率≈n2r4/4R2

上述公式可以发现，由于nr<<R,因此，发生死锁的概率是非常低的，同时，事务发生死锁的概率与以下几点因素有关：

1、事务中事务的数量n，数量越多发生死锁的概率越大
2、每个事务操作的数量r，每个事务操作的数量越大，发生死锁的概率越大
3、操作数据的集合R，越小发生死锁的概率越大

2、如何解决死锁

1、等待直到超时

mysql事务等待超时默认时间是50秒（innodb_lock_wait_timeout=50s），如果锁超过50秒会报错。

#查看锁超时时间
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
#修改锁超时时间
SET GLOBAL innodb_lock_wait_timeout=500;

2、死锁自动检查

打开（innodb_deadlock_detect=on）配置，发生死锁主动回滚一条事务，让其他事务继续执行。默认值是：innodb_rollback_on_timeout=off。

3、手工处理

#查看哪些表被锁
show OPEN TABLES where In_use > 0;
#查看正在运行的sql进程
show processlist; 
#找到info对比然后kill进程
kill id

3、降低死锁方法

1、以固定的顺序访问表和行。比如两个更新数据的事务，事务A 更新数据的顺序 为1，2；事务B更新数据的顺序为2，1。这样更可能会造成死锁。

2、大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。要把SQL扫行范围搞小点

3、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4、降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5、为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。