Lou Chen

MySQL锁机制

Lou.Chen2022年11月5日
大约 15 分钟

锁的分类

从数据操作的类型

  • 共享锁(Share Lock，S锁)：也叫读锁，针对同一份数据，多个事务读操作可以同时进行而不会互相影响。
  • 排它锁(Exclusive Lock，X锁)：也叫独占锁/写锁，当前写操作没有完成前，其他事务的不能对数据进行读和写

共享锁和排它锁的兼容性：

  • 读锁只能兼容读锁，不能兼容写锁

  • 写锁不能兼容读锁和写锁。

兼容性S锁X 锁
S 锁不冲突冲突
X 锁冲突冲突
S锁

手动加S锁:SELECT ... LOCK IN SHARE MODE;

  • 事务1开启后，加了S锁的记录：
    • 本事务可以对其进行读取和修改
    • 其他事务可以加S锁读，但是不能加X锁写
image-20221031113123440
X锁

对于InnoDB 在RR(MySQL默认隔离级别) 而言，对于 update、delete 和 insert 语句， 会自动给涉及数据集加排它锁（X）；

对于普通 select 语句，innodb 不会加任何锁

手动加X锁：SELECT ... FOR UPDATE;

  • 事务1开启后，对于加了X锁的锁记录：
    • 本事务可以对其读取和修改。
    • 其他事务不能对其加S锁读，也不能加X锁写
image-20221031142316482
快照读

快照读（SnapShot Read）是一种一致性不加锁的读，是 InnoDB 存储引擎并发如此之高的核心原因之一。

在可重复读的隔离级别下，事务启动的时候，就会针对当前库拍一个照片（快照），快照读读取到的数据要么就是拍照时的数据，即事务开启那一瞬间数据库中的数据，要么就是当前事务自身插入/修改过的数据。

日常所用的不加锁的查询，都属于快照读。

当前读

快照读，是一种一致性加锁的读，就是读取最新数据，而不是历史版本的数据。在默认insert/update/delete语句时都是使用的当前读

在事务中，手动对记录使用X锁和S锁即可使用当前读，也就是说，在可重复读的情况下，如果使用了当前读，也可以读到别的事务已提交的数据(没有开启事务，否则修改操作会阻塞)

  • SELECT ... FOR UPDATE
  • SELECT ... LOCK IN SHARE MODE
image-20221031150852653
使用当前读和快照读解决幻读问题
  • 使用快照读即普通SELECT方式的去查询数据，即使用不加锁的方式，事务开启的时候对数据拍一个快照，每次查询都从此快照记录查询。
  • 使用当前读即加X锁和产生临键锁的方式去查询数据：
    • image-20221031154958997

从数据操作的颗粒度(表/行/页)

  • 表锁：对整个表加锁，开销小，加锁快，锁粒度最大，不会出现死锁，但是发生锁冲突的概率最高，并发读最低。
  • 行锁：对该行数据加锁，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 页锁：开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM和InnoDB支持的锁

  • MyISAM：支持表锁，在并发写的情况下性能非常差，所以MyISAM更适用于并发读
  • InnoDB：支持表锁和行锁，默认情况下为行锁，所以InnoDB更适用并发写

行锁

  • 在对数据进行UPDATE、DELETE、INSERT操作会加行锁。并且INSERT会使用意向插入锁保证在同一间隙不同事务的插入并发。

  • 在对数据默认进行SELECT操作，InnoDB 不会加任何锁。可以手动加X/S锁产生行锁来避免可重复情况下产生的幻读问题

  • InnoD中行级锁是基于索引实现的，行锁锁的是字段的索引记录，所以行锁使用的条件是必须存在索引，如果 WHERE条件中字段没有命中索引或者索引失效的话，就

    会导致扫描全表对表中的所有行记录进行加锁，即行锁升级为表锁

行锁分析

show status like 'innodb_row_lock%';

image-20221029223954920

记录锁(Record Lock)

  • Record Lock 也就是我们所说的记录锁。只锁定一行记录，它是针对索引记录，即它只锁定记录这一行数据。

记录锁生效条件：

  • 条件列必须为唯一索引列或主键列。
  • 查询语句必须为精准匹配 = ，不能为 >、<、like等。

间隙锁(Gap Lock)

  • Gap Lock 也叫做间隙锁，会锁定一个范围，规则为左开右开，且不包含记录锁
  • 只在 REPEATABLE READ(可重复读) 隔离级别下有效。
插入意向锁

插入意向锁是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

某个事务在插入一条数据前，首先会判断改区域是否有间隙锁，若存在间隙锁，那么插入操作就会阻塞，在此期间会生成一个插入意向锁。表明有事务想在某个区间插入新记录，但是现在处于等待状态，只有等待该间隙锁事务释放，其阻塞的事务才会执行。

插入意向锁解决的是：插入意向锁之间并不冲突，所以在多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

临键锁（Next-key Lock）

  • Record Lock(记录锁)+Gap Lock(间隙锁)，锁定一个范围，规则为左开右开(间隙锁)+记录锁(记录锁)= 左开右闭
  • 只在 REPEATABLE READ(可重复读) 隔离级别下有效。
  • InnoDB默认在可重复的情况下使用快照读，会出现幻读问题。但是可以使用手动加X锁的方式变为当前读，即采用 next-key(临键锁) 锁进行搜索和索引扫描，以防止幻读。

临键锁加锁规则：

  • 唯一非空索引的等值查询

    • 若记录存在，且会只能存在一条该数据，临键锁退化为记录锁
    • 若记录不存在，即会向上查找和向下查找索引，临键会退化成间隙锁

  • 唯一非空索引的范围查询

    • 若记录存在，以当前索引位置为锁范围的起始或者终止位置
    • 若记录不存在，则以上一个索引作为起始范围/下一个索引作为终止范围

  • 普通索引上的等值查询

    • 若记录存在，因为可重复，也会向上查找和向下查找索引, 会同时加临键锁和间隙锁，
    • 若记录不存在，也会向上查找和向下查找索引，临键会退化成间隙锁

  • 普通索引上的范围查询

    • 若记录存在
      • 不等于：当前索引开始
      • 等于：可重复，则需要上一个/下一个索引开始
    • 若记录不存在：则以上一个索引作为起始范围/下一个索引作为终止范围

案例分析

参考：https://www.cnblogs.com/nevererror/p/16251839.htmlopen in new window

id为主键索引，age为普通索引，name无索引

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8;

INSERT INTO user(id,age,name) values(1,1,'1');
INSERT INTO user(id,age,name) values(5,5,'5');
INSERT INTO user(id,age,name) values(10,10,'10');
INSERT INTO user(id,age,name) values(15,15,'15');
INSERT INTO user(id,age,name) values(20,20,'20');
INSERT INTO user(id,age,name) values(25,25,'25');
image-20221101154951341
唯一索引等值查询

等值查询需要保证当前位置或者范围位置的锁定

条件索引结果存在

  • select * from user where id = 15 for update;
    • 直接可以从表中找到记录锁 15 ，且为唯一不可重复，所有不存在间隙锁。
    • 所以最终结果优化为记录锁,所以为结果为 15

条件索引结果不存在

  • select * from user where id = 18 for update;
    • 没有查询到 18 的行锁，即没有找到当前为主索引，需要找到上一个索引 15，下一索引为20，间隙锁为 (15,20)。
    • 所以最终结果优化为间隙锁 (15,20)。
唯一索引范围查询

范围查询需要保证在查询条件范围内的当前位置和范围位置的锁定

条件索引结果存在：则以当前索引作为起始/终止范围

  • select * from user where id > 15 for update；
    • 间隙锁 (15,20)，(20,25)，(25,+∞) 记录锁：20，25
    • 所以最终结果优化为：临键锁：(15,20], (20,25],(25,+∞) ==> 最终范围：(15,+∞)
  • select * from user where id >= 15 for update；
    • 间隙锁 (15,20)，(20,25)，(25,+∞) 记录锁：15,20,25
    • 所以最终结果优化：临键锁： (15,20], (20,25],(25,+∞) ==> 最终范围：[15,+∞)
  • select * from user where id < 15 for update;
    • 间隙锁 (-∞,1)，(1,5)，(5,10),(10,15) 记录锁：1,5,10
    • 所以最终结果优化为 (-∞,15)
  • select * from user where id <= 15 for update;
    • 间隙锁 (-∞,1)，(1,5)，(5,10),(10,15) 记录锁：1,5,10,15
    • 所以最终结果优化为 (-∞,15]
  • select * from user where id >= 10 and id<=15 for update;
    • 满足查询条件：[10,+∞) U (-∞,15]
    • 最终范围：[10,15]

条件索引结果不存在：则以上一个索引作为起始范围/下一个索引作为终止范围

  • select * from user where id > 18 for update；
    • 找到上一个索引记录15，下一个索引记录 20 ，25，间隙锁(15,20)，(20,25)。记录锁：20,25
    • 最终结果优化为临键锁 (15,20],(20,25),(25,+∞) ==> 最终范围 (15,+∞)
  • select * from user where id >= 18 for update；
    • 与select * from user where id > 18 for update；同理
  • select * from user where id < 18 for update;
    • 间隙锁：(-∞,1),(1,5),(5,10),(10,15)，找下一个索引20，间隙锁（15,20)。记录锁:1,5,10,15
    • 最结果为优化为临键锁(-∞,1](1,5] (5,10] (10,15] ==> 最终范围：(-∞,20)
  • select * from user where id <= 18 for update;
    • 与select * from user where id < 18 for update;同理
  • select * from user where id>=13 and id <= 18 for update;
    • 满足查询条件：(10,+∞) U (-∞,20)
    • 最终范围：(10,20)
普通索引等值查询

条件索引结果存在：

  • select * from user where age = 15 for update;

    • 行锁：15。 因为15可以重复，所以需要加间隙锁：上一个索引10，间隙锁 (10,15)。下一个索引20，间隙锁(15,20)

    • 最终优化结果为临键锁：(10,15] 间隙锁(15,20) ==>最终范围:(10,20)

      • age=10并且id>10的数据也会锁定

      • age=20并且id<20的数据也会锁定

条件索引结果不存在：

  • select * from user where age = 18 for update;

    • 18的记录锁未找到。即需要加间隙锁：上一个索引15，间隙锁 (10,15)。下一个索引20，间隙锁(15,20)

    • 最终范围：(15,20)

      • age=15并且id>15的数据也会锁定

      • age=20并且id<20的数据也会锁定

普通索引范围查询

条件索引结果存在：

  • select * from user where age > 15 for update;

    • 间隙锁：(15,20),(20,25),(25,+∞) 记录锁：20，25
    • 优化为临键锁：(15,20],(20,25],(25,+∞)
    • 最终范围：(15,+∞)

  • select * from user where age >= 15 for update;

    • 因为15可能重复，需要找上一个索引，间隙锁：(10,15),(15,20),(20,25),(25,+∞) 记录锁：15,20,25
    • 优化为临键锁：(10,15],(15,20],(20,25],(25,+∞)
    • 最终范围：(10,+∞)
      • age=10并且id>10的数据也会锁定

  • select * from user where age < 15 for update;

    • 间隙锁 (-∞,1)，(1,5)，(5,10),(10,15) 记录锁：1,5,10
    • 最终范围： (-∞,15)

  • select * from user where age <= 15 for update;

    • 因为15可能重复，需要找下个索引20，间隙锁： (-∞,1)，(1,5)，(5,10),(10,15),(15,20) 记录锁：1,5,10,15
    • 所以最终结果优化为 (-∞,20)
      • age=20并且id<20的数据也会锁定

条件结果不存在：

  • select * from user where age > 18 for update;

    • 当前18索引记录不存在，需要依赖上一个索引15，间隙锁：(15,20),(20,25) 记录锁：20,25
    • 优化为临键锁：(15,20],(20,25],(25,+∞)
    • 最终范围：(15,+∞)
      • age=15并且id>15的数据也会锁定

  • select * from user where age >= 18 for update;

    • 同select * from user where age > 18 for update;

  • select * from user where age < 18 for update;

    • 当前18索引记录不存在，需要依赖下一个索引20，间隙锁： (-∞,1)，(1,5)，(5,10),(10,15),(15,20) 记录锁：1,5,10,15
    • 最终范围：(-∞,20)
      • age=20并且id<20的数据也会锁定

  • select * from user where age <= 18 for update;

    • 同select * from user where age < 18 for update;

表锁

MyISAM 引擎在执行 select 时会自动给相关表加读锁，在执行 update、delete 和 insert 时会自动给相关表加写锁。所以MyISAM不适合做写主表的引擎，因为

锁表后，其他线程不能做任何操作，大量的更新会使查询难得到锁，从而造成大量线程阻塞。

MySQL 的表级锁有两种模式：

  • 表共享读锁（Table Read Lock）。
  • 表独占写锁（Table Write Lock）。

其兼容性如下：

兼容性S锁X 锁
S 锁不冲突冲突
X 锁冲突冲突

如下表信息：

CREATE TABLE `user` (
  `id` int DEFAULT NULL,
  `money` int DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE index id_index on user(id);
CREATE index money_index on user(money);

为表添加写/读锁

  • 为表添加读锁：lock table 表名 read;

  • 为表添加写锁：lock table 表名 write;

  • 同时为表添加写锁和读锁：lock table 表1 read,表2 write;

  • 解锁所有表的锁：unlock tables;

查看所有表的加锁情况

show open tables;

当In_use为1时，代表此表加了锁，为0时没有加锁

image-20221031102850284

表锁分析

show status like 'table%';

image-20221031110437050
  • Table_locks_immediate
    • 产生表级锁定的次数，表示可以立即获得锁的查询次数，每立即获得锁值加1
  • Table_locks_waited
    • 出现表级锁定而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在较严重的表级锁争用情况

表共享读锁

  • 对于加了读锁的表：
    • 不能在本会话中查询和操作其它表，也不能更新此表
    • 其他会话可以读此表，但是不能更新此表
image-20221031104144476

表独占写锁

  • 表加了写锁后：
    • 本会话中不能查询和操作其他表，可以更新此表
    • 其他会话不能对此表进行读写
image-20221031105828754

意向锁

意向锁的作用

意向锁是表级锁，当需要对一张表加写锁和读锁的时候，首先要判断，该表是否存在行锁，但是如果一行行去遍历判断的话，性能太差。所以需要在对数据加行锁之前，首先需要对表加意向锁，然后另一个事务需要对其表加锁的时候就可以使用意向锁来判断能否能对表加锁，所以意向锁只是一个作为判断能否为表加锁的标志，仅此而已。

  • 加表读锁：兼容意向共享锁。阻塞意向排他锁
  • 加表写锁：阻塞意向共享锁和意向排他锁
意向锁的分类
  • 意向共享锁（Intention Shared Lock，IS 锁）：当对数据行加S锁的时候，会给表IS锁
  • 意向排他锁（Intention Exclusive Lock，IX 锁）：当对数据行加X锁的时候，会给表加IX锁

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁时候，InooDB 会对数据表加对应意向锁。

意向锁之间是互相兼容的:

IS 锁IX 锁
IS 锁兼容兼容
IX 锁兼容兼容

加表锁之前获取的意向锁兼容性：

IS 锁IX 锁
S 表锁兼容互斥
X 表锁互斥互斥

全局锁

加全局锁：flush tables with read lock

释放全局锁：unlock tables

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

  • 对数据的增删改操作，比如 insert、delete、update等语句；
  • 对表结构的更改操作，比如 alter table、drop table 等语句。

全局锁应用场景是

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

死锁

在支持并发操作的系统中，不同的线程对资源出现循环依赖，线程之间互相持有对方需要的资源，导致线程都进入无限等待的状态，称之为死锁。

  • 事务A先获取到id=1的行锁，然后事务B获取到id=2的行锁；
  • 接着事务A要获取id=2的行锁，发现被事务B持有，阻塞；
  • 事务B要获取id=1的行锁，发现被事务A持有，阻塞；
  • 两个事务进入死锁状态。

当出现死锁后，有两种处理策略：

  • 直接进入等待，直到连接超时，超时时间可通过innodb_lock_wait_timeout设置。
  • 发起死锁检测，发现死锁后主动回滚死锁中的一个事务，让其他事务正常执行。将参数innodb_deadlock_detect设置为on，表示开启死锁检测。