MySQL中的锁

Posted on by luozhiyun

MySQL中的锁

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。在备份过程中整个库完全处于只读状态。

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

如果使用的是InnoDB，那么可以使用官方自带的逻辑备份工具mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而不需要FTWRL。

对于MyISAM这种不支持事务的引擎，就需要使用FTWRL命令了。

表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁的语法是 lock tables … read/write。可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。

对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是MDL（metadata lock)。MDL不需要显式使用，在访问一个表的时候会被自动加上。

MDL是为了保证在查询表中的数据的时候，同时在做修改表结构操作时的数据一致性。因此，在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

  • MDL读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

行锁

行锁就是针对数据表中行记录的锁。比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。

共享锁和独占锁

共享锁，英文名：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。

独占锁，也常称排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁

兼容性 x s
x 不兼容 不兼容
s 不兼容 兼容

锁定读的语句

读取一条记录时需要获取一下该记录的S锁，其实这是不严谨的。如果只是普通的读，那么是不会加锁的。想要在读取记录时获取记录的锁有两种SELECT语句：

  1. 对读取的记录加S锁：

    SELECT ... LOCK IN SHARE MODE;

  2. 对读取的记录加X锁:

    SELECT ... FOR UPDATE;

写操作所加的锁

  • DELETE

    对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取一下这条记录的X锁，然后再执行delete mark操作。

  • UPDATE

    要分三种情况：

    • 如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁。
    • 如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。也就是会获取X锁和隐式锁。
    • 如果修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了。

  • INSERT

    通过一种称之为隐式锁来保护这条新插入的记录在本事务提交前不被别的事务访问。

两阶段锁协议

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

行锁

作用是把一条记录锁上，防止其他事务都这条记录做修改。行锁是有S锁和X锁之分的，兼容如上图所示。

16a5ddeea00f72b7-9097531

死锁

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
4d0eeec7b136371b79248a0aed005a52
事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。

隐试锁Gap Lock以及next-key lock

Gap Lock以及next-key lock是为了解决幻读的。

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。

间隙锁，锁的就是两个值之间的空隙。如下，表t，初始化插入了6个记录，这就产生了7个间隙。

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

e7f7ca0d3dab2f48c588d714ee3ac861
当你执行 select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。

间隙锁之间是不存在冲突的，例如：
7c37732d936650f1cda7dbf27daf7498这里session B并不会被堵住。因为表t里并没有c=7这个记录，因此session A加的是间隙锁(5,10)。而session B也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。

如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

间隙锁造成的死锁

我用两个session来模拟并发，并假设往表里插入一条id=9的数据。

df37bf0bb9f85ea59f0540e24eb6bcbe

  1. session A 执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10);

  2. session B 执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；

  3. session B 试图插入一行(9,9,9)，被session A的间隙锁挡住了，只好进入等待；

  4. session A试图插入一行(9,9,9)，被session B的间隙锁挡住了。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

加锁语句分析

例子

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

image-20200510184125738

READ COMMITTED隔离级别

普通的SELECT语句

普通的SELECT语句在:

  • READ UNCOMMITTED隔离级别下，不加锁，直接读取记录的最新版本，可能发生脏读、不可重复读和幻读问题。
  • READ COMMITTED隔离级别下，不加锁，在每次执行普通的SELECT语句时都会生成一个ReadView，这样解决了脏读问题，但没有解决不可重复读和幻读问题。
  • REPEATABLE READ隔离级别下，不加锁，只在第一次执行普通的SELECT语句时生成一个ReadView，这样把脏读、不可重复读和幻读问题都解决了
  • SERIALIZABLE隔离级别下，需要分为两种情况讨论：
    • 在系统变量autocommit=0时，也就是禁用自动提交时，普通的SELECT语句会被转为SELECT … LOCK IN SHARE MODE这样的语句，这种情况是加S锁
    • 在系统变量autocommit=1时，也就是启用自动提交时，普通的SELECT语句并不加锁，只是利用MVCC来生成一个ReadView去读取记录。

锁定读的语句

对于使用主键进行等值查询的情况

  • 使用SELECT … LOCK IN SHARE MODE来为记录加锁，加的是S型行锁。

  • 使用SELECT … FOR UPDATE来为记录加锁，加的是X型行锁。

  • 使用UPDATE …来为记录加锁

    如：

    UPDATE hero SET country = '汉' WHERE number = 8;

    这条UPDATE语句并没有更新二级索引列，所以加的是X型行锁。

    如果UPDATE语句中更新了二级索引列，比方说：

    UPDATE hero SET name = 'cao曹操' WHERE number = 8;

    该语句的实际执行步骤是首先更新对应的number值为8的聚簇索引记录，再更新对应的二级索引记录。所以

    1. 为number值为8的聚簇索引记录加上X型行锁。
    2. 为该聚簇索引记录对应的idx_name二级索引记录加上X型行锁。

    image-20200510190222541

  • 使用DELETE …来为记录加锁，同样是X行锁

对于使用主键进行范围查询的情况

  • 使用SELECT … LOCK IN SHARE MODE来为记录加锁

    SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

    1. 先到聚簇索引中定位到满足number <= 8的第一条记录，也就是number值为1的记录，然后为其加锁。

    2. 判断一下该记录是否符合范围查询的边界条件

      对于number值为1的记录是符合这个条件的，所以会将其返回到server层继续处理。

    3. 该记录返回到server层继续判断。

      如果该记录符合剩余的条件，那么就把它发送给客户端，不然的话需要释放掉在该记录上加的锁。

    4. 然后刚刚查询得到的这条记录（也就是number值为1的记录）组成的单向链表继续向后查找，得到了number值为3的记录，然后重复上面的步骤。

    但是这个过程有个问题，就是当找到number值为8的那条记录的时候，还得向后找一条记录（也就是number值为15的记录），就得为这条记录加锁，然后判断该记录不符合number <= 8这个条件，又要释放掉这条记录的锁，这个过程导致number值为15的记录先被加锁，然后把锁释放掉，过程就是这样：

    image-20200510191746442

    如果你先在事务T1中执行：

    # 事务T1
BEGIN;
SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

    然后再到事务T2中执行：

    # 事务T2
BEGIN;
SELECT * FROM hero WHERE number = 15 FOR UPDATE;

    是没有问题的，因为在T2执行时，事务T1已经释放掉了number值为15的记录的锁，但是如果你先执行T2，再执行T1，由于T2已经持有了number值为15的记录的锁，事务T1将因为获取不到这个锁而等待。

  • 使用SELECT ... FOR UPDATE和上面一致，不过加的是X型行锁

  • 使用UPDATE ...来为记录加锁，如果没有更新二级索引列，加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。

    如果UPDATE语句中更新了二级索引列：

    UPDATE hero SET name = 'cao曹操' WHERE number >= 8;
    1. 为number值为8的聚簇索引记录加上X型行锁。
    2. 为对应的idx_name二级索引记录加上X型行锁。
    3. 为number值为15的聚簇索引记录加上X型行锁。
    4. 为对应的idx_name二级索引记录加上X型行锁。
    5. 为number值为20的聚簇索引记录加上X型行锁。
    6. 为对应的idx_name二级索引记录加上X型行锁。

    image-20200510202410302

  • 使用DELETE ...来为记录加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。

使用二级索引进行等值查询

  • 使用SELECT ... LOCK IN SHARE MODE

    SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

    因为有回表操作，所以先要对二级索引记录加S型正经记录锁，然后再给对应的聚簇索引记录加S型正经记录锁。

    上面我们说过，update语句在为二级索引进行更新的时候，会先对聚簇索引加锁，再对二级索引进行加锁，所以假设上边这个语句在事务T1中运行，然后事务T2中运行下边一个我们之前介绍过的语句：

    UPDATE hero SET name = '曹操' WHERE number = 8;
    • 事务T2持有了聚簇索引记录的锁，事务T1持有了二级索引记录的锁。
    • 事务T2在等待获取二级索引记录上的锁，事务T1在等待获取聚簇索引记录上的锁。

    两个事务都分别持有一个锁，而且都在等待对方已经持有的那个锁，这种情况就是所谓的死锁。

  • 使用SELECT ... FOR UPDATE与SELECT ... LOCK IN SHARE MODE语句的加锁情况类似，只是加的是X型行锁。

  • 使用UPDATE ...来为记录加锁与上面类似，不过不过如果被更新的列中还有别的二级索引列的话，对应的二级索引记录也会被加锁。

使用二级索引进行范围查询

  • 使用SELECT ... LOCK IN SHARE MODE来为记录加锁，如：

    SELECT * FROM hero FORCE INDEX(idx_name)  WHERE name >= 'c曹操' LOCK IN SHARE MODE;

    加的是S型行锁，加锁顺序是对一条二级索引记录加锁完后，会接着对它相应的聚簇索引记录加锁，完后才会对下一条二级索引记录进行加锁，以此类推～

    image-20200510203401562

  • 使用UPDATE ...来为记录加锁

    假设该语句执行时使用了idx_name二级索引来进行锁定读，那么它的加锁方式和上边所说的SELECT ... FOR UPDATE语句一致。如果有其他二级索引列也被更新，那么也会为对应的二级索引记录进行加锁，就不赘述了。

全表扫描的情况

如：

SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

由于country列上未建索引，所以只能采用全表扫描的方式来执行这条查询语句，扫描过的行会先加锁，然后再释放掉：

image-20200510204306561

对于UPDATE ...和DELETE ...的语句来说，在遍历聚簇索引中的记录，都会为该聚簇索引记录加上X型行锁，然后：

  • 如果该聚簇索引记录不满足条件，直接把该记录上的锁释放掉。
  • 如果该聚簇索引记录满足条件，则会对相应的二级索引记录加上X型行锁。

REPEATABLE READ隔离级别下

REPEATABLE READ会多使用一个gap锁来解决幻读的问题。

对于使用主键进行等值查询的情况

  • SELECT ... LOCK IN SHARE MODE来为记录加锁，如：

    SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

    主键具有唯一性，一个事务中两次执行上述语句并不会发生幻读，因此只需要加上S型锁。

    如果我们要查询主键值不存在的记录，比方说：

    SELECT * FROM hero WHERE number = 7 LOCK IN SHARE MODE;

    由于number值为7的记录不存在，所以需要在number值为8的记录上加一个gap锁，也就是不允许别的事务插入number值在(3, 8)这个区间的新记录。画个图表示一下：

    image-20200510210036750

其余语句使用主键进行等值查询的情况与READ COMMITTED隔离级别下的情况类似，这里就不赘述了。

对于使用主键进行范围查询的情况

  • 使用SELECT ... LOCK IN SHARE MODE语句来为记录加锁

    SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;

    因为主键本身就是唯一的，所以我们不用担心在number值为8的前边有新记录插入，只需要保证不要让新记录插入到number值为8的后边就好了，所以：

    • 为number值为8的聚簇索引记录加一个S型行锁。
    • 为number值大于8的所有聚簇索引记录都加一个S型next-key锁

    image-20200510210254516

    与READ COMMITTED隔离级别类似：

    SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

    这个语句会为number值为1、3、8、15这4条记录都加上S型锁，但是REPEATABLE READ隔离级别下，在判断number值为15的记录不满足边界条件 number <= 8 后，并不会去释放加在该记录上的锁！！！

    image-20200510210618355

  • 使用UPDATE ...来为记录加锁

    如果UPDATE语句未更新二级索引列，和上面一致，不过加的是X型next-key锁。

    如果UPDATE语句中更新了二级索引列，比方说：

    UPDATE hero SET name = 'cao曹操' WHERE number >= 8;

    会先对聚簇索引记录以及Supremum记录加X型next-key锁，然后再将对应的idx_name二级索引记录加X型行锁。

    image-20200510210909720

    同样，如果是：

    UPDATE hero SET name = 'cao曹操' WHERE number <= 8;

    那么加锁如图所示：

    image-20200510211007372

对于使用唯一二级索引进行等值查询

这种情况和上面的主键加锁相似，不过加锁顺序是先对二级索引记录加锁，再对聚簇索引加锁。

使用唯一二级索引进行范围查询的情况

  • 使用SELECT ... LOCK IN SHARE MODE语句来为记录加锁

    SELECT * FROM hero FORCE INDEX(uk_name) WHERE name >= 'c曹操' LOCK IN SHARE MODE;

    二级索引记录都会被加S型next-key锁，它们对应的聚簇索引记录也会被加S型行锁。

    image-20200510211406127

使用普通二级索引进行等值查询的情况

  • 使用SELECT ... LOCK IN SHARE MODE语句来为记录加锁

    SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;
    • 对所有name值为'c曹操'的二级索引记录加S型next-key锁，它们对应的聚簇索引记录加S型行。
    • 对最后一个name值为'c曹操'的二级索引记录的下一条二级索引记录加gap锁。

    image-20200510211742392

    全表扫描的情况

    SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

    在REPEATABLE READ隔离级别下，InnoDB存储引擎并不会真正的释放掉锁，所以聚簇索引的全部记录都会被加锁，并且在事务提交前不释放。

    image-20200510211859796

INSERT语句

遇到重复键（duplicate key）

如果插入时已存在记录的主键或者唯一二级索引列与待插入记录的主键或者唯一二级索引列相同，如：

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO hero VALUES(20, 'g关羽', '蜀');
ERROR 1062 (23000): Duplicate entry '20' for key 'PRIMARY'

在生成报错信息前，其实还需要做一件非常重要的事情 —— 对聚簇索引中number值为20的那条记录加S锁。

  • 在READ COMMITTED隔离级别下，加的是S型锁
  • 在REPEATABLE READ/SERIALIZABLE隔离级别下，加的是S型next-key锁。

如果是唯一二级索引列值重复，把普通二级索引idx_name改为唯一二级索引uk_name ，再插入如：

mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO hero VALUES(30, 'c曹操', '魏');
ERROR 1062 (23000): Duplicate entry 'c曹操' for key 'uk_name'

不管是哪个隔离级别，针对在插入新记录时遇到重复的唯一二级索引列的情况，会对已经在B+树中的唯一二级索引记录加next-key锁。

如果我们使用的是INSERT ... ON DUPLICATE KEY ...这样的语法来插入记录时，如果遇到主键或者唯一二级索引列值重复的情况，会对B+树中已存在的相同键值的记录加X锁。