MySQL的锁

Sun, Jun 6, 2021 阅读时间 2 分钟

数据库的锁

锁的作用就是确保每一个用户都能以一致的方式读取和写入数据。MySQL中的锁分为两种：闩锁latch和lock锁。

  • latch：轻量级的锁，用于锁定mysql应用程序中的一些对象，要求锁定的时间必须非常短，mysql中分为mutex（互斥锁）和rwlock（读写锁），是应用程序级别的，就是我们在程序中使用的mutex和rwmutex。通过命令show engine innodb mutex可以查看，一般mysql的开发人员会关注。

    通过命令show engine innodb mutex可以观察当前数据库中的latch信息。

    Mysql> show engine innodb mutex;
+--------+-----------------------------+-------------+
| Type   | Name                        | Status      |
+--------+-----------------------------+-------------+
| InnoDB | rwlock: dict0dict.cc:920    | waits=106   |
| InnoDB | rwlock: log0log.cc:582      | waits=24    |
| InnoDB | rwlock: btr0sea.cc:240      | waits=1694  |
| InnoDB | rwlock: btr0sea.cc:240      | waits=1179  |
| InnoDB | rwlock: btr0sea.cc:240      | waits=22050 |
| InnoDB | sum rwlock: buf0buf.cc:1563 | waits=3929  |
+--------+-----------------------------+-------------+
6 rows in set (0.003 sec)

  • lock：用来锁定数据库中的对象，表、页、行，并且仅在事务提交或回滚之后才能释放（不同事务隔离级别的释放时机不同）。这也是我们本篇文章主要讨论的锁。

MySQL中的Lock

MySQL支持全局锁、表锁、行锁(InnoDB存储引擎支持)。

全局锁

用来锁住整个数据库，命令是Flush tables with read lock，可以让整个库处于只读状态。

表锁

表锁分两种，一种是表锁，另一种是元数据锁(metadata lock，MDL)。

表锁

就是锁住有一张表，命令是lock tables ... read/write，可以用unlock命令主动释放锁，或者当连接断开时也会自动释放。一般不用这个锁，影响面太大，并且读锁和写锁的机制还需要注意：

  • 读锁：给表加了读锁后，自己也不能对其进行修改，所有连接都只能读取该表。
  • 写锁：给表加了写锁后，自己可以读写该表，其他连接的读写都被阻塞。

MDL

MySQL5.5引入，用于隔离DML操作和DDL操作之间的干扰。不需要显示使用，会在访问一个表时自动加上，MDL是保证读写时，表的元数据（字段，类型等）不会变化。

当对一个表做增删改查DML操作时，自动加MDL读锁，读锁之间不互斥，即所有DML操作都可以并行。

当对表结构做DDL操作时，加MDL写锁，写锁和其他锁都互斥，即在修改表结构时，其它线程的DDL操作，和DML操作，都会被阻塞。

注意：修改表结构时自动添加的MDL写锁，会阻塞后续的所有DML操作！

如上图，session C开始执行DDL，session D都会被阻塞住。

原因：申请MDL锁的操作会形成一个队列，队列中写锁的优先级高于读锁，所以一旦出现写锁等待时，后续的读锁也要等待读锁先获取到锁，之后自己才能获取，所以写锁等待时，后续的读锁都会阻塞。

读锁和写锁

这里讨论的读锁和写锁针对的是普通的表锁和行锁。

  • 共享锁：S Lock，读锁。
  • 排他锁：X Lock，写锁。

对于普通表锁和行锁来说，它们的读锁和写锁的兼容性如下：

写锁和任何锁都不兼容，读锁和读锁都是兼容的。

意向锁

意向锁是表级别的锁，而且针对的也是表级别的读锁和写锁。当想要给一个表中的某一行加读锁或者写锁时，就需要先给表加个意向锁，它的作用是为了快速的判断一个表中有没有被加行锁，否则就需要一行行看才能知道这个表有没有被加行锁。如果表被加了行锁，那么对表加表锁时就需要受到限制。

它同样也有两种：

  • 意向共享锁，IS Lock，意向读锁，当事务想要获得一张表中某些行的共享锁时，就会给表加个意向读锁。
  • 意向排他锁，IX Lock，意向写锁，当事务想要获得一张表中某些行的排他锁时，就会给表加个意向写锁。

意向锁和表级别的读锁写锁之间的兼容性如下：

可以看到：意向锁之间都是兼容的；表的写锁和其他所有锁都是不兼容的；意向写锁和表级别的读锁和写锁都是不兼容的。

一个事务想要加行锁的过程：

  1. 事务要获取一个表中某些行的S锁/X锁时，需要先获得表的IS/IX锁
  2. 当要获取IS锁时，发现表被加了X锁，表X锁和其他所有锁都不兼容，所以需要阻塞
  3. 当要获取IX锁时，发现表被加了S锁，IX锁和S锁不兼容，所以也要阻塞
  4. 当要获取IS/IX锁时，发现表被加了其他事务的意向锁，不会阻塞，因为意向锁之间都是不阻塞的，可以继续看想要加锁的这行有没有被锁住

一个事务对表加表级别的读锁和写锁时的过程：

  1. 如果事务要对表加X锁，发现表上被加了某个意向锁，X锁和所有意向锁都不兼容，则会阻塞
  2. 如果事务要加表的S锁，发现表被加了IS锁，则不会阻塞，因为IS锁和S锁是兼容的
  3. 如果事务要加表的S锁，发现表被加了IX锁，则还是会阻塞，因为IX锁和S锁也是不兼容的

InnoDB的行锁

行锁的两阶段协议

  1. 行锁是需要的时候才加上的，可以主动加，或者当修改某行时也会自动加
  2. 行锁必须等到事务提交或回滚才能释放

由于以上的两阶段协议的存在，所以我们在处理事务时，尽量在一个事务中，并发量可能最高的行锁往后放，让它锁定的时间尽量短些，提高并发效率。

InnoDB的锁定一致性读

顾名思义，就是通过加锁的方式，实现一致性读。

  • 写锁，通过语句select ... for update可以在读的时候给这些行加上写锁
  • 读锁，通过语句select ... lock in share mode可以在读的时候给这些行加上读锁

InnoDB行锁的算法

InnoDB的行锁都是针对索引的，只有在需要根据索引进行读写操作时，才可以加上行锁，如果筛选条件不是索引列，那么无论如何，都会加的是表锁。

InnoDB的行锁包括三种算法：Record Lock，Gap Lock和Next-Key Lock。三个都是排他锁。

  • Record Lock：记录锁，普通的行锁，可以锁住一行的索引记录。

    比如：

    select *from table where id = 1 for update

    以上语句将会给id=1的行加上行的record lock。需要注意id列必须是主键或者唯一索引，且是等式查询，以保证选定的只有一行，否则上述语句就会变成临键锁。如果不是等式查询，而是>、<、like、between and等，也会退化成临键锁。

    当然，当通过更新语句更新一行时，会自动给这行加上记录锁的X Lock。

  • Gap Lock：间隙锁，可以锁住索引之间的间隙，防止间隙内的插入操作，由于只锁定间隙，所以它是个左开右开的区间，即它只能阻塞两个相邻索引之间的插入操作。

    比如执行以下语句时：

    SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

    id在区间(1,10)将会被加上间隙锁，当向(1,10)中插入新记录时，就会被阻塞。

    间隙锁，只能在RR隔离级别下才会产生。作用是为了阻止多个事务将记录插入到同一个范围内，造成幻读(Phantom Problem)的问题。间隙锁和间隙锁之间是不冲突的，多个间隙锁可以同时加到一个范围上。

    产生间隙锁的条件：

    1. 使用普通索引的等值查询，而不是主键，唯一这种保证唯一性的索引。
    2. 虽然是唯一索引，但是查的是多个索引值。
    3. 虽然是唯一索引，但是查的是个范围时。

    用户可以通过以下方式显式的关闭间隙锁：

    • 事务的隔离级别设为READ COMMITTED
    • 将参数innodb_locks_unsafe_for_binlog设为1

    在上述的配置下，除了外键约束和唯一性检查依然需要使用间隙锁，其余情况将仅使用普通行锁进行锁定。需要注意，这种设置破坏了事务的隔离性，并可能导致主从复制的不一致。

  • Next-Key Lock：临键锁，既要锁住一个索引的范围，也要锁住范围内的所有行，其实就是间隙锁和记录锁的结合，临键锁的区间是左开右闭的，因为它既要锁住范围，也要锁住右侧的一条记录。

    因为间隙锁只有RR级别才会产生，所以临键锁自然也是只有RR隔离级别才存在。

    临键锁是InnoDB行锁的大多数形态，只有满足一定条件时，它才会降级成为间隙锁，或者记录锁：

    • 当查询未命中记录时，就会降级成为间隙锁，因为没有记录
    • 当查询的行是唯一索引的等值查询时，就会降级成为记录锁，因为不存在间隙

总结

  1. 当使用唯一索引来等值查询的语句时, 如果这行数据存在，不产生间隙锁，而是记录锁。
  2. 当使用唯一索引来等值查询的语句时, 如果这行数据不存在，会产生间隙锁。
  3. 当使用唯一索引来范围查询的语句时，对于满足查询条件但不存在的数据产生间隙(gap)锁，如果存在的记录就会产生记录锁，加在一起就是临键锁。
  4. 当使用普通索引不管是锁住单条，还是多条记录，都只会产生间隙锁，它会把命中记录前后的范围都进行锁定；
  5. 如果一个表上有多个索引，那么根据索引类型的不同，唯一索引上可能会加临键锁，非唯一索引上则会加间隙锁，锁之间是可以叠加的。
  6. 在没有索引上不管是锁住单条，还是多条记录，都会产生表锁；

死锁

死锁是指两个或两个以上的事务在执行的过程中，因争夺锁资源造成的互相等待的现象。若无外力作用，两个事务都会无限制的阻塞下去。

死锁产生的根本原因：事务必须要提交或者回滚后，行锁才会被释放。通过下面一个例子可以容易的分析

事务1 事务2
BEGIN; BEGIN;
UPDATE user SET first_name=“Harry” WHERE id = 3; UPDATE user SET last_name=“Yang” WHERE id = 4;
UPDATE user SET first_name=“Harry” WHERE id = 4; UPDATE user SET first_name=“Yang” WHERE id = 3;
COMMIT; COMMIT;

如果两个事务，凑效同时执行了第一条语句，事务1锁住了id=3的行，事务2锁住了id=4的行，那么接下来执行时，事务1要访问id=4的行，发现该行已经被事务2加了排他锁，事务1只能等待事务2回滚或者提交后锁释放了才能继续执行。同样的，事务2接下来要访问id=3的行，发现这一行被事务1加上了排他锁，所以他就也只能等待事务1提交或回滚，才能继续执行，于是就出现了两个事务分别持有对方想要访问的行的锁，同时也等待着对方释放锁。如果不加外力干涉，这两个事务会永远阻塞下去。

解决这类问题，就要求一种一个事务必须回滚，让另一个事务能继续执行下去。MySQL和InnoDB实现了各种死锁检测和死锁超时检测。大体上可以分为被动检测，和主动检测两种：

  • 被动检测：比如超时检测，当两个事务相互等待时，当一个事务的等待时间超过了阈值时，就要回滚其中一个事务。可以通过参数innodb_lock_wait_timeout参数指定超时时间。

    MySQL> show variables like "innodb_lock_wait_timeout";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.001 sec)

  • 主动检测：wait-for graph(等待图)的方式进行死锁检测。首先，数据库会保存锁的信息链表，和事务的等待链表，根据这两个链表构造出一张有向图，其中点就是事务，边代表一个事务需要等待另一个事务，如果图中存在回路，则代表有死锁。

如果检测出了死锁存在，InnoDB会回滚undo log量最少的事务（这是《MySQL技术内幕》的说法，也有资料说是回滚持有最少行级排他锁的事务，可能是版本不同）。

自增锁

自增锁是一种比较特殊的表级锁，当一个事务中要处理AUTO_INCREMENT时就会去持有自增锁，自增锁的作用就是保证某个字段是自增的。比如：当事务A要插入一行数据时就会持有自增锁，而事务B也要插入时，就会等待事务A释放自增锁，才能接着插入。