mysql update 会自动加锁吗_超全面的MySQL语句加锁分析

说在前面的话

本文是用来系统阐述在MySQL中，不同语句在各种条件下的加锁情况，并不是解释各种锁是什么(或者说加锁的本质是什么)，大家如果不理解什么是MVCC、ReadView、正经记录锁、gap锁、next-key锁、插入意向锁这些概念的，可以参考MySQL的官方文档，或者直接参照《MySQL是怎样运行的：从根儿上理解MySQL》这本小册(里边有比官方文档更贴心，更详细的解释，文章中涉及到的所有概念均在小册中有详细解释。

建议：

        1. 本篇文章不适合碎片化时间阅读，最好使用电脑观看，或者将字体跳到最小效果好一些

        2. 可能一下子看不完，关注 + 收藏 + 好看 + 转发一波

        3. 不要跳着看

事前准备

建立一个存储三国英雄的hero表：

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孙权', '吴');

然后现在hero表就有了两个索引(一个二级索引，一个聚簇索引)，示意图如下：

语句加锁分析

其实啊，“XXX语句该加什么锁”本身就是个伪命题，一条语句需要加的锁受到很多条件制约，比方说：

• 事务的隔离级别

• 语句执行时使用的索引(比如聚簇索引、唯一二级索引、普通二级索引)

• 查询条件(比方说=、=<、>=等等)

• 具体执行的语句类型

在继续详细分析语句的加锁过程前，大家一定要有一个全局概念：加锁只是解决并发事务执行过程中引起的脏写、脏读、不可重复读、幻读这些问题的一种解决方案(MVCC算是一种解决脏读、不可重复读、幻读这些问题的一种解决方案)，一定要意识到加锁的出发点是为了解决这些问题，不同情景下要解决的问题不一样，才导致加的锁不一样，千万不要为了加锁而加锁，容易把自己绕进去。当然，有时候因为MySQL具体的实现而导致一些情景下的加锁有些不太好理解，这就得我们死记硬背了～

我们这里把语句分为3种大类：普通的SELECT语句、锁定读的语句、INSERT语句，我们分别看一下。

普通的SELECT语句

普通的SELECT语句在：

• READ UNCOMMITTED隔离级别下，不加锁，直接读取记录的最新版本，可能发生脏读、不可重复读和幻读问题。

• READ COMMITTED隔离级别下，不加锁，在每次执行普通的SELECT语句时都会生成一个ReadView，这样解决了脏读问题，但没有解决不可重复读和幻读问题。

• REPEATABLE READ隔离级别下，不加锁，只在第一次执行普通的SELECT语句时生成一个ReadView，这样把脏读、不可重复读和幻读问题都解决了。

  不过这里有一个小插曲：

  # 事务T1，REPEATABLE READ隔离级别下
  mysql> BEGIN;
  Query OK, 0 rows affected (0.00 sec)
  mysql> SELECT * FROM hero WHERE number = 30;
  Empty set (0.01 sec)# 此时事务T2执行了：INSERT INTO hero VALUES(30, 'g关羽', '魏'); 并提交
  mysql> UPDATE hero SET country = '蜀' WHERE number = 30;
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  mysql> SELECT * FROM hero WHERE number = 30;
  +--------+---------+---------+
  | number | name    | country |
  +--------+---------+---------+
  |     30 | g关羽   | 蜀      |
  +--------+---------+---------+
  1 row in set (0.01 sec)
  

  在REPEATABLE READ隔离级别下，T1第一次执行普通的SELECT语句时生成了一个ReadView，之后T2向hero表中新插入了一条记录便提交了，ReadView并不能阻止T1执行UPDATE或者DELETE语句来对改动这个新插入的记录(因为T2已经提交，改动该记录并不会造成阻塞)，但是这样一来这条新记录的trx_id隐藏列就变成了T1的事务id，之后T1中再使用普通的SELECT语句去查询这条记录时就可以看到这条记录了，也就把这条记录返回给客户端了。因为这个特殊现象的存在，你也可以认为InnoDB中的MVCC并不能完完全全的禁止幻读。

• SERIALIZABLE隔离级别下，需要分为两种情况讨论：

  • 在系统变量autocommit=0时，也就是禁用自动提交时，普通的SELECT语句会被转为SELECT ... LOCK IN SHARE MODE这样的语句，也就是在读取记录前需要先获得记录的S锁，具体的加锁情况和REPEATABLE READ隔离级别下一样，我们后边再分析。

  • 在系统变量autocommit=