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

数据库锁

锁

• 间隙锁锁定的是一个间隙范围，而不会锁住某条记录。
• 共享锁就是读锁，独占锁就是写锁，可以理解为读写锁，读读不互斥，读写互斥，写写互斥，共享锁（ 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
1
2

• 排它锁（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
1
2
3

事务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
1

这里会对 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
1

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
1
2

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
1

按照加锁规则，会加上 （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
1
2

分析下这两个事务，如果按照索引的值那我们很容易得到 事务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
1
2
3
4

事务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号即可。