Mysql(InnoDB)的SQL加锁分析

一、背景

工作经常遇到在编写并发场景sql时考虑欠缺，造成过一些bug和sql死锁。于是乎，笔者查阅了不少相关资料。 本文就MySQL/InnoDB的加锁问题，展开较为深入的分析与讨论，主要是介绍一种思路，运用此思路，拿到任何一条SQL语句，都能完整的分析出这条语句会加什么锁？会有什么样的使用风险？甚至是分析线上的一个死锁场景，了解死锁产生的原因。

注：MySQL是一个支持插件式存储引擎的数据库系统。本文下面的所有介绍，都是基于InnoDB存储引擎，其他引擎的表现，会有较大的区别。

二、InnoDB的多版本并发控制MVCC

InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处就是：读不加锁，读写不冲突。

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

现在以MySQL InnoDB为例来看看：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)。

select * from table where ?

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

　select * from table where ? lock in share mode;

    select * from table where ? for update;

    insert into table values (…);

    update table set ? where ?;

    delete from table where ?;

所有以上的五条语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句（lock in share mode;），对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

一个Update SQL （update set x where ?）操作的具体流程:

当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。

MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。

因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。

Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。(针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。)

三、事务隔离级别Isolation Level

不同的事物隔离级别下，对SQL加的锁有所区别。本文不打算讨论数据库理论中，是如何定义这4种隔离级别