mysql innodb 解锁_Mysql-Innodb 锁总结

1.全局锁：(限制 DML , DDL[修改表结构])

全局读锁： Flush tables with read lock

Flush tables 做的是将缓存刷回硬盘，with read lock 给所有表加读锁，对于大部分 lock，当客户端连接断开的时候，锁一般会释放。

如果在主库上使用此命令，则写业务停摆。在从库上使用此命令，则来自主库的 binlog 无法被执行，主从同步会延后

全局只读属性：set global readonly = true

此命令不等同于锁，而是设置数据库的全局可读性，客户端连接断开也不会使得此属性还原，慎用。

readonly 可能用于从库，不适用于读锁

2.表级锁

表锁：lock tables tableName read/write　　/　　unlock tables

属于 lock，不是属性，也不是 latch(线程同步工具)，所以客户端连接断开时，也会解锁

并且同一线程(客户端连接)，就算是自己拥有读锁，也不能进行写操作。

MDL锁(metadata lock)：

1.增删查改时加 MDL 读锁

2.改表结构(DDL)加写锁

需要注意的是，MDL锁在 Mysql 的实现使用了一把锁，但是这把锁会记录两个链表

一条链表是记录该Lock 已经被获取了的类型，称为 granted ，比如有 N 个连接请求了 MDL 读锁，那么 granted 链表就应该

有 N 个状态为 MDL_SHARED_XXX 的节点

还有一条链表是 waiting，表示因为和 granted 链表的任一锁类型冲突而导致需要等待加上的锁

官方给他们的定义：

假设一下线程要去获取锁 A， 获取类型是 type，那么该线程需要做两件事，查看自己要获取的 type 是否和

A的两条链表的锁类型都兼容。才能获得锁。否则阻塞，并且把自己的 type 加入 waitting 链表。

所以有一种情况：三个客户端连接分别执行 A,B,C。且按A->B->C的顺序执行。

Transaction A: select * from t;

Transaction B: alter table t change clomun 'x' 'a'  int null default null;

Transaction C: select * from t;

最终 B 和 C 会被堵住，原因就是 A 在 granted 链表中放入了 SHARED 类型的锁节点

B获取失败，在 waitting 链表放入 EXCLUSIVE 类型的锁节点

C检查链条链表，发现和自己的锁 waitting 链表中的 EXCLUSIVE 不兼容，所以C也被阻塞。

3.行锁：

两阶段锁协议：连接在事务中获得的行锁，都在事务结束才会释放。而MDL写锁不会有类似现象(MDL读锁会)。

行锁调优：涉及 竞争度激烈的行 的语句应该尽量放在 事务的后面，这样的话占有这个行的锁的时间会尽可能短，因为离事务结束更近。

占用这个锁的时间会相对短，如此一来，这个竞争激烈的行的锁就会更快释放。

比如说，订单交易的时候，事务中会扣除客户账户余额，和商店营收。多个客户购买的话，就会同时去竞争商店营收这一行数据。

所以在事务中可以先改用户账户余额，再去改商店营收。或者可以将商店营收改成多个行，然后把不同用户路由到不同行上去修改

对应营收，求总营收的时候需要将所有营收行锁读锁，然后读取。

4.间隙锁 + 行锁 = net-key lock

间隙锁 主要为了防止幻读，同时也表明了，加锁的对象不一定是行，也可以是间隙，比如一张表有两条记录 id = 1 和 id = 5

，并且id是主键，那么在主键上的范围(1, 5)就是一个间隙，锁住了这个间隙，其他客户端连接(线程)就不能对这个间隙插入内容，但是可以对这个间隙(不存在的值)做 update 和 delete。注意，间隙锁是不包含行记录的，锁行记录的是行锁。 间隙锁锁的是插入意图，不是更新和删除意图

只有在可重复读隔离级别的情况下，才可能出现幻读的情况，幻读指的是当前事务重复读取的情况下，下一次读取读取到了上一次读取不存在的行。如果是之前读取的了的某一行的内容变了，严格来说不算幻读。在可重复读的情况下，应该叫做脏读。

间隙锁的加锁规则：

1. 当使用 update，delete，或者带 for update 或者 lock in share mode 语句时会加锁, 且加锁的单位是 next-key lock

2. 只有访问到的对象会加锁，此处对象可以是单单辅助索引或者带有数据行的聚簇索引，如果是访问不存在的行，也就是访问间隙的话，就只会加上间隙锁(辅助索引和聚簇索引都一样，就算是辅助索引，因为是访问两个存在行中的不存在行，所以访问到右边的存在行就会停下来，根据索引等值查询优化，不满足条件的右边行的行锁被排出 next-key lock ，所以只剩下间隙锁)

3. 唯一索引(注意不只是聚簇索引)的等值查询会使原本要加的 next-key lock 退化 行锁

4. 非唯一索引的等值查询会扫描(只有非唯一索引上的等值查询会扫描，因为唯一索引是不能重复的)到最后一个不满足条件时停下，并且最后一个不满足条件的行造成的 next-key lock 会退化成 间隙锁

5. next-key lock 以右值为标准 形成 做开右闭 区间,在innodb中有 suprenum 表示最大值,(x, suprenum] 表示最后一个next-key lock 区间

6. 主键在范围查询时，本应该在遍历到最后一个满足条件的行后结束遍历(因为主键唯一)，但是还是会遍历到不满足条件为止，这导致多加了一个 next-key lock，比如假设有 id(主键) = 10, 15, 20 的行，条件本来是 where id > 10 and id <= 15，那么按理说加的锁是

(10,  15]，因为已经找到 id = 15 的了，接下去不可能再有 id <= 15 的行，但是还是会继续遍历到 20，不满足条件，停止。所以最后加的锁是 (10, 15] 和 (15, 20]

7.正序 加锁的范围：

1. 对于 col >= x and col < y 是从 x 的等值查询 开始的，并且范围查询，直到遇到 col >= y 的行

2. 对于 col > x and col < y 是从 x 这一行向右范围查询，直到遇到 col >= y

3. 对于 col >=x and col <= y 和 7.1 一样，只不过遇到 col > y 才停止

4. 对于 col > x and col <= y 和 7.2 一样，从x 这一行向右范围查询，直到遇到 col > y 等行停止

8.倒序 加锁的范围

1. 对于 col >= x and col < y by desc 是从 y 开始向左范围查询，直到遇到 col < x

2. 对于 col > x and col < y by desc和 8.1 一样，从y开始向左范围查询，直到遇到 col <= x

3. 对于 col >= x and col <= y by desc，从 y 的等值查询开始，向左范围查询，直到遇到 col < x

4. 对于 col > x and col <= y by desc，从 y 的等值查询开始，向左范围查询，直到遇到 col <= x

9. 多个线程对同一个间隙加锁不互斥，间隙锁本身是一种读锁

10.next-key lock 是分阶段加上去的，先加间隙锁，再加行锁。所以如果有线程 A 先持有行锁，线程 B 再去持有间隙锁且要求A的行锁，线程A再去要求B持有的间隙锁，会造成死锁。

假设只有 id = 1, c = 123 , a = 345 这一行，且c是索引

A : update table set a = a + 1 where c = 123; (持有 (-max, 123] , (123, suprenum] 这两个next-key lock))

B : update table set a = a + 2 where c = 123; (加上了 (-max,123) 这个间隙锁，并且堵在了 c = 123 这一行的行锁上)

A : insert into tables values (2, 100, 666); (请求 B 持有的 (-max，123) 导致死锁)

11.limit限制扫描行数，减少访问对象，减少锁的锁定对象 比如非唯一索引c上 有 10 12 15 三个值

如果：

A: update table set a = a + 1 where c > 10 limit 1(只会加(10,12]的next-key lock)

B: insert into table values (123, 13, 123) 仍然能成功(c = 13)

12.间隙锁拓展：间隙锁依赖于两条记录之间

比如非唯一索引c有四条记录 A, B ,C, D

那么存在三个间隙锁 (A, B) , (B , C)，(C , D)

倘若现已加 (C, D] next-key lock ，那么删除 C 这一行的时候，(C,D] 会变成 (B, D]

倘若使用 update table set c = m  where c = A 　　　　( B < m < D)

执行此语句的线程将被 block，因为上述语句相当于在 被间隙锁锁住的 (B,D) 中插入 c = m 的记录

再删除 c = A 的记录

插入带来的锁

插入不会带来间隙锁 或 next-key lock 但是会在插入的记录上加上排他锁(主键或唯一索引和辅助索引上)，所以事务插入一条记录之后，其他事务插入同样的记录不行(辅助索引上值相同也不行)。但是可以在间隙插入事务。

意向表锁：

当要加表锁的时候，会先给表加意向排他锁。

加表锁的时候，会先加意向排他锁，如果有其他事务加了意向排他锁，则会阻塞。

免去了加表锁的时候一行行遍历查看是否有行锁的情况。