数据库锁及批量更新死锁处理

数据库锁

锁

间隙锁锁定的是一个间隙范围，而不会锁住某条记录。
共享锁就是读锁，独占锁就是写锁，可以理解为读写锁，读读不互斥，读写互斥，写写互斥，共享锁（ S 锁）、独占锁（ X 锁）指的就是 InnoDB 上的行锁（记录锁）。
意向锁是InnoDB引擎的一种特殊的表锁，在获取共享锁和独占锁之前必须拿到对应类型的意向锁。
乐观锁和悲观锁更多的是体现加锁的思想不同
- 乐观锁是一种无锁的思想，假设并发冲突总是不会发生，提交时检查数据一致性，如果一致性被破坏则放弃提交，更新时带着 version 就是乐观锁。
- 悲观锁假设并发冲突一定会发生，每次操作前都会拿锁，通过锁的互斥顺序执行来控制并发，可以认为数据库中的锁都是悲观锁。

锁实现

1.数据库实例锁

对整个数据实例加锁
加锁命令： Flush tables with read lock (FTWRL) 对数据库实例加全局读锁，让整个库处于只读状态，DDL，DML都会被阻塞
解锁命令： unlock tables ，加锁客户端断开的时候也会自动解锁。
使用场景：做全库的逻辑备份；

2.表锁

Mysql Server 层支持

加锁命令：lock tables
解锁命令：unlock tables，加锁客户端断开的时候也会自动解锁。
使用场景：读密集型；写作不频繁的场景；数据量不大的简单应用；全表更新或者删除；

lock tables t1 read 加读锁，会限制本线程只能读 t1 ，其他线程写 t1 会被阻塞
lock tables t2 write 加写锁，会限制本线程只能读写 t2 ,其他线程不能读也不能写 t2，都会被阻塞

3.行锁

InnoDB 引擎支持行锁，共享锁（S）和独占锁（X），执行更新操作时，会自动加 X 锁，对于普通的查询语句，InnoDB 不会主动加任何锁，可以显示的加锁。

select * from t where ... lock in share mode // 加共享锁 S
select * from t where ... for update         // 加独占锁 X

排它锁（X锁，独占锁）
- 修改数据语句 update、delete、insert 都会自动给涉及到的数据加上行级排他锁， select 语句默认不会加任何锁，如果查询的时候要加行级排他锁可以使用 select …for update 语句，加行级共享锁可以使用 select … lock in share mode 语句。
- 事务 1 如果对某张表加了表级排他锁，表示事务 1 可以对该表中的所有记录进行查询和修改，而其它事务只能查询不能修改。并且其它事务不能再往这张表加任何类型的表级锁，也不能给表中的数据行加任何的行级锁。
共享锁（S锁）
- 数据行对象 A 可以共存多个行级共享锁，这被称为行级共享锁兼容。加了行级共享锁的数据行对象 A 不能再加行级排他锁，所以行级共享锁和行级排他锁是不兼容的。
- 当事务 1 为某张表添加了表级共享锁，表示事务 1 可以查看表中的所有记录，但不能修改，而且其它事务也只能查看数据不能修改数据。但是其它事务可以再往这张表添加表级共享锁和意向共享锁，其它事务也可以往这张表中的记录添加行级共享锁；但是其它事务不能再往这张表添加表级的排他锁，也不能添加意向排他锁，其它事务也不能往这张表中的记录添加行级排他锁。

更新锁

只有行级更新锁。

当事务 1 给数据行对象 A 加上更新锁后，代表数据行对象 A 将在稍后被更新。更新锁允许其他事务在事务 1 操作更新之前读取数据行对象 A，但不可以修改。其他事务修改数据行对象 A 之前会先往数据行对象 A 加行级排他锁，但是发现数据行对象 A 已存在 U 锁，所以加行级排他锁失败，只能等待 U 锁释放。
更新锁与行级共享锁兼容；更新锁与表级共享锁不兼容；更新锁与更新锁互不兼容；更新锁与行级排他锁不兼容，更新锁与表级排他锁不兼容。
因此数据行对象 A 不可以再添加更新锁，但是可以添加行级共享锁，但是添加行级共享锁的意义不大，因为事务 1 找到需要更新的数据时，更新锁直接转为行级排他锁，开始更新数据，不需要等待其他事务释放行级共享锁，所以在有更新锁的数据资源上加行级共享锁就毫无意义了。

意向锁

InnoDB 的行锁是加载索引上的，如果我们的更新没有走索引导致扫描全表，就会锁全表，锁的是主键索引。

InnoDB 还支持一种特殊的表锁意向锁，我们先来看一个例子：

事务A：update t set k = 1 where id = 6
事务B：update t set k = 1 
事务A：commit

事务A 会拿 id = 6 这一行的独占锁，事务B 对整个表进行修改，要对整个表所有行加独占锁，那么需要遍历主键索引树的每个节点，看有没有被别的事务加了共享锁或者独占锁，最终发现 id = 6 被加了独占锁，事务B加锁失败，阻塞。这种场景性能很差，如果要对全表加锁，需要遍历索引树检测锁状态。InnoDB 使用意向锁来解决这个问题

乐观锁：认为冲突概率低，操作数据时，不会锁定数据，只有在提交修改时，才检查。如果数据已被修改，则回滚，否则提交。mysql并没有内置乐观锁机制，需要在代码中实现，常见的实现方式是，使用时间戳或者版本号字段，记录每一次对数据的修改。
- 标志SQL：（1）select id,version from .xxx …；（2）update xxx set … where … and version = 旧version；
- 使用场景：（1）低冲突环境；（2）读多写少场景；（3）短事务操作；（4）分布式系统；（5）互联网应用；
- 风险：（1）冲突检测：只有提交时才检测，有冲突会导致所有都回滚；（2）依赖于版本管理；
悲观锁：一种并发控制方法。认为冲突概率高，每次读写前都加锁；
- 使用方式：参考前面提到的共享锁（S锁）和排他锁（X锁）；
- 使用场景：（1）写操作比较多；（2）并发冲突高；（3）业务需要强一致性的场景；
- 缺点：（1）性能开销；（2）并发降低；（3）死锁；（4）锁超时；四、意向锁

意向锁是一种不与行锁冲突的表级锁，就是为了解决锁全表场景的锁状态判断的性能问题。意向锁也是InnoDB自动添加的。

常见的意向锁

1.意向共享锁（Intent Share Lock） IS 锁

事务 T1 在给数据行对象添加行级 S 锁前，要先获得 IS 锁。(事务在加共享锁之前必须拿到表的意向共享锁)。如果表被加了 IS 锁，说明某个事务对这个表中的某些数据行加了行级 S 锁。当其它事务想要在这个表上加一个表级排他锁时，发现这个表已经加了意向共享锁，那么就不可以加表级的排他锁了。

2.意向排他锁（Intent Exclusive Lock）IX 锁

事务在请求行级 X 锁前，要先获得 IX 锁（事务在加独占锁之前必须拿到表的独占共享锁）。
事务 T1 修改 user 表的数据行对象 A，会给数据行对象 A 上一把行级的排他锁，但是在给数据行对象 A 上行级排他锁前会先给 user 表上一把意向排他锁，这时事务 T2 要给 user 表上一个表级的排他锁就会被阻塞。

3.共享意向排他锁（Share Intent Exclusive Lock） SIX 锁

共享意向排他锁的意思是，某事务要读取整个表，并更新其中的某些数据。

意向锁是表级锁和行锁是不互斥的，只有在需要锁全表的行锁的情况下，才会出现互斥的情况，所以意向锁不是传统意义上的表锁，性能比较高。

考虑到意向锁，事务A 会拿 id = 6 的行锁和表的意向独占锁，事务A 需要锁全表行锁，直接加锁失败。

4.间隙锁

where条件是一个范围时，数据库会锁定区间数据，主要是解决幻读问题。

使用场景：（1）防止幻读；（2）范围查询；

缺点：（1）性能影响；（2）死锁；（3）复杂性；（4）锁定范围过大，可能导致不必要的锁定冲突；

对索引记录之间的间隙进行加锁，当使用范围查询记录或者更新数据，InnoDB 会给满足条件的的索引记录加锁，而满足查询条件但是不存在的记录集合，称为间隙 （GAP），InnoDB 也会对这个"间隙"加锁，也叫 GAP Lock，这是为了防止拿锁期间其他的事务插入数据，导致出现幻读现象，读提交级别因为不考虑幻读问题，所以不需要加间隙锁。

间隙锁和被锁的间隙之间是互斥的，间隙锁之间是不存在互斥的。

这里要注意间隙锁锁的是索引记录项之间的间隙，而并不是索引值之间的间隙，后面 next key-lock 会有演示

5.Next-Key-Lock临键锁

可以理解为一种特殊的间隙锁，临键锁可以解决幻读的问题。

当事务拥有某一行记录的临键锁时，会锁住一段左开右闭的区间。比如后面截图中的3条数据，就生成了4个临键锁，临键锁如下：（1）(-∞，20]；（2）(20, 25]；（3）(25, 30]；（4）(30, +∞]；
当更新age=25的记录时，不能增加或者修改age为(25, 30]之间的数

是 InnoDB 加行锁和间隙锁的算法，是一个前开右闭的区间

update t set k = 1 where id = 5

这里会对 id = 5 记录加 Next key-Lock（0，5] 实际就是（0，5）之间的间隙锁和 id = 5 的行锁组合，当然因为是id是唯一索引，Next key-Lock 会退化成 id = 5 的行锁。

Next key-Lock 加锁规则如下：

（1）、只有访问到的索引记录才会加锁，这里要理解访问到的记录并不一定是查询到的结果

select * from t where id >= 5 and id < 10 for update

Mysql 会通过主键索引查询到 id = 5 的索引记录，注意这里是等值查询，继续往右扫描满足条件的索引记录，从这开始就是范围查询了，找到下个记录 id = 10 ,不满足条件退出，那么这里就访问到了 id = 5 ，id = 10 两条记录，都会被加上 Next key-Lock。

(2)、索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

SQL1: update t set k = 1 where id = 5
SQL2: select * from t where id >= 5 and id < 10 for update

SQL1 会加（0，5] Next key-Lock，因为 id 是主键索引，会退化成 id = 5 的行锁，SQL2 会对加（0，5]（5，10] 两个Next key-Lock，id = 5 是等值匹配最终退化成 id = 5 的行锁和 5，10]

(3)、非唯一索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

select * from t where k >=5 and k <= 10 for update

按照加锁规则，会加上（0，5] ，（5，10] ，（10，15]，因为 k = 10 等值查询，最后一条记录是 k = 15，所以退化成 (10，15)，最终加的Next key-Lock 是（0，5] ，（5，10] ， (10，15)

4、间隙锁锁的是索引记录项之间的间隙，而并不是索引值之间的间隙

事务A：update set k = 1 where k = 5
事务B：update set k = 0 where k = 10 blocked

分析下这两个事务，如果按照索引的值那我们很容易得到事务A 的间隙锁为 (0 ,5 )(5 ,10)，两边都是开区间，那么事务B 应该不会被阻塞，而实际上事务B会被阻塞，这是因为间隙锁是加载索引记录项之间的。

我们看索引 k 的结构，事务A 加的间隙锁应该是 ((0,0)(5,5))，((5,5)(15,5))，((15,5)(10,10)) 三个间隙锁，事务B 更新操作相当于插入一条(10,0) 的记录,此时需要在间隙锁中插入记录 ((0,0) (10,0)(5,5))，加锁失败阻塞

**5、**前面说了 Next key-Lock 实际上是InnoDB加锁的算法，是间隙锁和行锁的组合，并不是一个整体，InnoDB申请一个 key 的 Next key-Lock 按照访问记录的顺序去加行锁或者间隙锁。

事务A：select id from t where k = 10 lock in share mode;
事务B：update t set k = 1 where k = 10 ；（blocked）
事务A：insert into t values(8,8)
事务B：DeadLock ERROR

事务A 加 next-key lock ( 5，10 ] 和间隙锁 (10，15 )，具体的顺序是先加 (5，10 ) 间隙锁、id = 10 的行锁、 (10，15 )间隙锁全部加锁成功。
事务B 同样需要加加 next-key lock ( 5，10 ] 和间隙锁 (10，15 )，具体的顺序是先加 (5，10 ) 间隙锁成功，id = 10 的行锁，注意此时加锁失败阻塞。
事务A 插入( 8，8) 被事务B (5，10 ) 间隙锁阻塞。此时发生死锁，事务B被回滚。

6、记录锁

mysql的innodb引擎的一种锁定机制，用于锁定和控制单个行记录的访问。

记录锁作用在索引上，对于没有主键和唯一键的表，innodb会自动添加隐藏的聚簇索引，并在该索引上加锁。

批量更新死锁

show processlist

show processlist：主要是查询数据库中哪些线程正在执行，针对比较慢的线程（time的数值比较大的线程）我们可以将其kill掉。此外，show full processlist 返回的结果是实时变化的。

解读：

Id：链接mysql 服务器线程的唯一标识，可以通过kill来终止此线程的链接。

User：当前线程链接数据库的用户

Host：显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户

db: 线程链接的数据库，如果没有则为null

Command: 显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）

Time: 线程处在当前状态的时间，单位是秒

State：显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个 sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成

Info: 线程执行的sql语句，如果没有语句执行则为null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句

结果处理：

针对执行时间比较长的线程，我们可以直接将其kill掉，直接执行 kill Id号即可。