MySQL锁详解

按照锁粒度进行划分

​

• 根据加锁的范围
  • 全局锁，表级锁，行锁
• 全局锁
  • MySQL提供了一个加全局读锁的方法
  • Flush tables with read lock(当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。)
  • 全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
  • 使用全局锁
    • 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
    • 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
  • 既然要全库只读，为什么不使用 set global readonly=true ？
    • 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。
    • 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
• 表级锁
  • 表锁
    • 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • 元数据锁MDL
    • MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
    • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
    • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
• 行锁
  • 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  • 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
  • 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。(持有的时间最少)
  • MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁
  • 行锁是借助索引来实现的，也就是说，如果你的查询没有命中任何的索引，那么 InnoDB 引擎是用不了行锁的，只能使用表锁。

从数据库管理的角度对锁进行划分

共享锁

• 允许事务读一行数据

也叫读锁或 S 锁，共享锁锁定的资源可以被其他用户读取，但不能修改。在进行 SELECT 的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。

给 product_comment 在表上加共享锁，可以使用下面这行命令：

LOCK  TABLE product_comment READ;

给某一行加上共享锁

SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE user_id = 912178  LOCK  IN  SHARE  MODE

排它锁

(select * from table for update)

也叫独占锁、写锁或 X 锁。排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改。

常用角度对进行划分

乐观锁

乐观锁（Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者时间戳机制实现。

在表中设计一个版本字段 version，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行 UPDATE … SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改，修改就不会成功。

它会先尝试对资源进行修改，在写回时判断资源是否进行了改变，如果没有发生改变就会写回，否则就会进行重试，在整个的执行过程中其实都没有对数据库进行加锁；

乐观锁在数据库中通常指利用 CAS 的思路进行的更新操作。一般的使用形态就是下面这样的。

SELECT * FROM your_tab WHERE id = 1; // 在这里拿到了 a = 1
// 一大堆的业务操作
UPDATE your_tab SET a = 3, b = 4 WHERE id = 1 AND a =1

在上面的这个语句里面，预期数据库中 a 的值为 1 才会进行更新。如果此时数据库中的值已经被修改了，那么这个 UPDATE 语句就会失败。业务方通过检测受影响的行数是否为 0，来判断更新是否成功。

悲观锁

悲观锁（Pessimistic Locking）也是一种思想，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。

它会在获取资源前对资源进行加锁，确保同一时刻只有有限的线程能够访问该资源，其他想要尝试获取资源的操作都会进入等待状态，直到该线程完成了对资源的操作并且释放了锁后，其他线程才能重新操作资源；

悲观锁是指在写入数据时直接加锁。还拿上面这个例子来说吧，就是从最开始的 SELECT 语句就直接加上了锁。

SELECT * FROM your_tab WHERE id = 1 FOR UPDATE; // 在这里拿到了 a = 1
// 一大堆的业务操作
UPDATE your_tab SET a = 3, b = 4 WHERE id = 1

在加上锁之后，就可以直接更新了。这个时候不需要担心别人可以在 SELECT 和 UPDATE 之间将 a 更新为别的值。

乐观锁适合读操作多的场景，相对来说写的操作比较少。它的优点在于程序实现，不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。

悲观锁适合写操作多的场景，因为写的操作具有排它性。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止读 - 写和写 - 写的冲突。

乐观锁不会存在死锁的问题，但是由于更新后验证，所以当冲突频率和重试成本较高时更推荐使用悲观锁，而需要非常高的响应速度并且并发量非常大的时候使用乐观锁就能较好的解决问题，在这时使用悲观锁就可能出现严重的性能问题；在选择并发控制机制时，需要综合考虑上面的四个方面（冲突频率、重试成本、响应速度和并发量）进行选择。

• 死锁
  • 当出现死锁以后，有两种策略
    • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout （默认50s）来设置。
    • 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
• 在开发的时候如何安排正确的事务语句
  • 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。
• 两阶段锁
  • 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放， 而是要等到事务结束时才释放。

锁的种类一般分为乐观锁和悲观锁两种，InnoDB 存储引擎中使用的就是悲观锁，而按照锁的粒度划分，也可以分成行锁和表锁。

InnoDB 实现了标准的**行级锁，**也就是共享锁（Shared Lock）和互斥锁（Exclusive Lock）；共享锁和互斥锁的作用其实非常好理解：

• 共享锁（读锁）：允许事务对一条行数据进行读取；
• 互斥锁（写锁）：允许事务对一条行数据进行删除或更新；

共享锁之间是兼容的，而互斥锁与其他任意锁都不兼容：

锁的粒度

排他锁(写锁)

select … for update 其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

其他事务不能读取，也不能写。

共享锁(读锁)

其他事务可以读，但不能写。

无论是共享锁还是互斥锁其实都只是对某一个数据行进行加锁，InnoDB 支持多种粒度的锁，也就是行锁和表锁；为了支持多粒度锁定，InnoDB 存储引擎引入了意向锁（Intention Lock），意向锁就是一种表级锁。

意向锁也分为两种：

• 意向共享锁：事务想要在获得表中某些记录的共享锁，需要在表上先加意向共享锁；
• 意向互斥锁：事务想要在获得表中某些记录的互斥锁，需要在表上先加意向互斥锁；

锁的算法

三种锁的算法：Record Lock、Gap Lock 和 Next-Key Lock。

Record Lock

记录锁（Record Lock）是加到索引记录上的锁，假设我们存在下面的一张表 users：

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    age INT,
    PRIMARY KEY(id),
    KEY(last_name),
    KEY(age)
);

如果我们使用 id 或者 last_name 作为 SQL 中 WHERE 语句的过滤条件，那么 InnoDB 就可以通过索引建立的 B+ 树找到行记录并添加锁，但是如果使用 first_name 作为过滤条件时，由于 InnoDB 不知道待修改的记录具体存放的位置，也无法对将要修改哪条记录提前做出判断就会锁定整个表。

Gap Lock

记录锁是在存储引擎中最为常见的锁，除了记录锁之外，InnoDB 中还存在间隙锁（Gap Lock），间隙锁是对索引记录中的一段连续区域的锁；当使用类似 SELECT * FROM users WHERE id BETWEEN 10 AND 20 FOR UPDATE; 的 SQL 语句时，就会阻止其他事务向表中插入 id = 15 的记录，因为整个范围都被间隙锁锁定了。

虽然间隙锁中也分为共享锁和互斥锁，不过它们之间并不是互斥的，也就是不同的事务可以同时持有一段相同范围的共享锁和互斥锁，它唯一阻止的就是其他事务向这个范围中添加新的记录。

SELECT * FROM your_tab WHERE email='your_email' FOR UPDATE

那么这条查询语句此时也是使用了记录锁。类似地，如果 email=‘your_email’ 这条记录不存在，那么会变成一个间隙锁。

举个例子，如果数据库中只有 id 为（1，4，7）的三条记录，也就是 id= 3 这个条件没有命中任何数据，那么这条语句会在（1，4）加上间隙锁。所以你可以看到，在生产环境里面遇到了未命中索引的情况，对性能影响极大。

间隙锁是锁住了某一段记录的锁。直观来说就是你锁住了一个范围的记录。比如说你在查询的时候使用了 <、<=、BETWEEN 之类的范围查询条件，就会使用间隙锁。

SELECT * FROM your_tab WHERE id BETWEEN 50 AND 100 FOR UPDATE

间隙锁会锁住 (50，100) 之间的数据，而 50 和 100 本身会被记录锁锁住。类似地，<= 这种查询你也可以认为 = 的那个值会被记录锁锁住。

如果你的表里面没有 50，那么数据库就会一直向左，找到第一个存在的数据，比如说 40；如果你的表里面没有 100，那么数据库就会一直向右，找到第一个存在的数据，比如说 120。那么使用的间隙锁就是 (40，120)。如果此时有人想要插入一个主键为 70 的行，是无法插入的，它需要等这个 SELECT 语句释放掉间隙锁。

间隙锁我们一般都说两边都是开的，即端点是没有被间隙锁锁住的。记录锁和记录锁是排它的，但是间隙锁和间隙锁不是排它的。也就是说两个间隙锁之间即便重叠了，也还是可以加锁成功的。

间隙锁和临键锁是在可重复读的隔离级别下才有效果的。

一致性锁定读

在默认配置下，事务的隔离级别为可重复读，InnoDB存储引擎的select操作使用一致性非锁定读，在某些情况下，用户需要显式的对数据库的读取操作进行加锁以保证数据逻辑的一致性

InnoDB支持2种一致性的操作

• select … for update (加了一个x锁，排它锁，其他事务不能对已锁定的行加任何锁)
• select … lock in share mode （加了一个s锁，共享锁，其他事务可以向被锁定的行加s锁，但是如果加x锁，会被阻塞）

锁的3个算法

行锁的3个算法

InnoDB存储引擎的3种行锁的算法

• Record Lock 单个行记录上的锁
  • 总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，这时会创建一个隐式主键来锁定
• Gap Lock 间隙锁，锁定一个范围，但不包括记录本身
• Next-Key lock:Record Lock + Gap Lock 锁定一个范围，并且锁定记录本身
  • InnoDB对于行的查询都是采用的这种锁定算法

脏读

脏页是指在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中的页和磁盘中的页的数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入到了重做日志文件中，而所谓脏数据是指事物对缓冲池中行记录的修改，并且还没有提交。

脏读指的是在不同事物下，当前事物可以读到另外事物未提交的数据。简单来说就是可以读到脏数据。

不可重复读

不可重复读指的是在一个事物内多次读取同一数据集合，在这个事物还没结束时，另外一个事物也访问了该同一数据集合，并做了DML操作，因此，在第一个事物中的两次读数据之间，由于第二个事物的修改，那么第一个事物两次读取的数据可能是不是一样的，这么就发生了在一个事物内的两次读到的数据是不一样的情况。

不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的数据却是已经提交的数据，但其实违反了数据库事物的一致性的要求。

行锁升级为表锁的原因

• SQL 语句中未使用到索引，或者说使用的索引未被数据库认可（相当于没有使用索引）。
• 当“值重复率”低时，甚至接近主键或者唯一索引的效果，“普通索引”依然是行锁；当“值重复率”高时，MySQL 不会把这个“普通索引”当做索引，即造成了一个没有索引的 SQL，此时引发表锁。

锁与索引

在 MySQL 的 InnoDB 引擎里面，锁是借助索引来实现的。或者说，加锁锁住的其实是索引项，更加具体地来说，就是锁住了叶子节点。

一个表有很多索引，锁的是哪个索引呢？其实就是查询最终使用的那个索引。万一查询没有使用任何索引呢？那么锁住的就是整个表，也就是此时退化为表锁。

如果查询条件的值并不存在，例如：

SELECT * FROM your_tab WHERE id = 15 FOR UPDATE

id = 15 的值根本不存在，那么怎么锁？InnoDB 引擎会利用最接近 15 的相邻的两个节点，构造一个临键锁。

此时如果别的事务想要插入一个 id=15 的记录，就不会成功。

范围查询

SELECT * FROM your_tab WHERE id > 33 FOR UPDATE

InnoDB 引擎会构造一个 (33，supremum] 的临键锁，锁住整个范围。supremum 你可以直观理解为 MySQL 认为的一个虚拟的最大值。

释放锁时机

大部分人在学习锁的时候有一个误区，就是认为锁是在语句执行完毕之后就立刻释放掉的。其实并不是，它是在整个事务结束之后才释放的。换句话来说，当一个事务内部给数据加上锁之后，只有在执行 Rollback 或者 Commit 的时候，锁才会被释放掉。

参考

面对信仰编程