MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁

阅读提示

1. 本文所参考的MySQL文档版本是8.0，做实验的MySQL版本是8.0.13
2. 本文主要参考了MySQL官方文档 InnoDB锁定和事务机制
3. 本文还参考了何登成的 MySQL加锁处理分析、一个最不可思议的MySQL死锁分析 以及阿里云RDS-数据库内核组的 常用SQL语句的MDL加锁源码分析
4. MySQL是插件式的表存储引擎，数据库的锁是和存储引擎相关的，本文讨论的锁都是InnoDB存储引擎的锁

文章正文开始

“加什么样的锁”与以下因素相关

1. 当前事务的隔离级别
2. SQL是一致性非锁定读(consistent nonlocking read)还是DML(INSERT/UPDATE/DELETE)或锁定读(locking read)
3. SQL执行时是否使用了索引，所使用索引的类型(主键索引，辅助索引、唯一索引)

我们先分别介绍这几个因素

一、隔离级别(isolation level)

数据库事务需要满足ACID原则，“I”即隔离性，它要求两个事务互不影响，不能看到对方尚未提交的数据。数据库有4种隔离级别(isolation level)，按着隔离性从弱到强(相应的，性能和并发性从强到弱)分别是

1. Read Uncommitted。下面简称RU
2. Read Committed。下面简称RC
3. Repeatable Read(MySQL的默认隔离级别)。下面简称RR
4. Serializable

“I”即隔离性正是通过锁机制来实现的。提到锁就会涉及到死锁，需要明确的是死锁的可能性并不受隔离级别的影响，因为隔离级别改变的是读操作的行为，而死锁是由于写操作产生的。

-- 查看事务的 全局和session 隔离级别( MySQL 5.7.19及之前使用tx_isolation)  
select @@global.transaction_isolation, @@session.transaction_isolation;  
-- 设置 全局 事务隔离级别为repeatable read  
set global transaction isolation level repeatable read  
-- 设置 当前session 事务隔离级别为read uncommitted  
set session transaction isolation level read uncommitted  

事务隔离级别设置和查看的详细语法请见：https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

二、一致性非锁定读和锁定读

InnoDB有两种不同的SELECT，即普通SELECT 和 锁定读SELECT。锁定读SELECT 又有两种，即SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE;锁定读SELECT 之外的则是 普通SELECT 。

不同的SELECT是否都需要加锁呢?

1. 普通SELECT 时使用一致性非锁定读，不加锁;
2. 锁定读SELECT 使用锁定读，加锁;
3. 此外，DML(INSERT/UPDATE/DELETE)时，需要先查询表中的记录，此时也使用锁定读，加锁;

FOR SHARE 语法是 MySQL 8.0 时加入的，FOR SHARE 和 LOCK IN SHARE MODE 是等价的，但，FOR SHARE 用于替代 LOCK IN SHARE MODE，不过，为了向后兼容，LOCK IN SHARE MODE依然可用。

1、 一致性非锁定读(consistent nonlocking read)

InnoDB采用多版本并发控制(MVCC, multiversion concurrency control)来增加读操作的并发性。MVCC是指，InnoDB使用基于时间点的快照来获取查询结果，读取时在访问的表上不设置任何锁，因此，在事务T1读取的同一时刻，事务T2可以自由的修改事务T1所读取的数据。这种读操作被称为一致性非锁定读。这里的读操作就是普通SELECT。

隔离级别为RU和Serializable时不需要MVCC，因此，只有RC和RR时，才存在MVCC，才存在一致性非锁定读。

一致性非锁定读在两种隔离级别RC和RR时，是否有什么不同呢?是的，两种隔离级别下，拍得快照的时间点不同

1. RC时，同一个事务内的每一个一致性读总是设置和读取它自己的最新快照。也就是说，每次读取时，都再重新拍得一个最新的快照(所以，RC时总是可以读取到最新提交的数据)。
2. RR时，同一个事务内的所有的一致性读 总是读取同一个快照，此快照是执行该事务的第一个一致性读时所拍得的。

2、锁定读(locking read)

如果你先查询数据，然后，在同一个事务内 插入/更新 相关数据，普通的SELECT语句是不能给你足够的保护的。其他事务可以 更新/删除 你刚刚查出的数据行。InnoDB提供两种锁定读，即：SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE。它俩都能提供额外的安全性。

这两种锁定读在搜索时所遇到的(注意：不是最终结果集中的)每一条索引记录(index record)上设置排它锁或共享锁。此外，如果当前隔离级别是RR，它还会在每个索引记录前面的间隙上设置排它的或共享的gap lock(排它的和共享的gap lock没有任何区别，二者等价)。

看完背景介绍，我们再来看一下InnoDB提供的各种锁。

三、InnoDB提供的8种不同类型的锁

InnoDB一共有8种锁类型，其中，意向锁(Intention Locks)和自增锁(AUTO-INC Locks)是表级锁，剩余全部都是行级锁。此外，共享锁或排它锁(Shared and Exclusive Locks)尽管也作为8种锁类型之一，它却并不是具体的锁，它是锁的模式，用来“修饰”其他各种类型的锁。

MySQL5.7及之前，可以通过information_schema.innodb_locks查看事务的锁情况，但，只能看到阻塞事务的锁;如果事务并未被阻塞，则在该表中看不到该事务的锁情况。

MySQL8.0删除了information_schema.innodb_locks，添加了performance_schema.data_locks，可以通过performance_schema.data_locks查看事务的锁情况，和MySQL5.7及之前不同，performance_schema.data_locks不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁，也就是说即使事务并未被阻塞，依然可以看到事务所持有的锁(不过，正如文中最后一段所说，performance_schema.data_locks并不总是能看到全部的锁)。表名的变化其实还反映了8.0的performance_schema.data_locks更为通用了，即使你使用InnoDB之外的存储引擎，你依然可以从performance_schema.data_locks看到事务的锁情况。

performance_schema.data_locks的列LOCK_MODE表明了锁的类型，下面在介绍各种锁时，我们同时指出锁的LOCK_MODE。

1、共享锁或排它锁(Shared and Exclusive Locks)

它并不是一种锁的类型，而是其他各种锁的模式，每种锁都有shard或exclusive两种模式。

（编辑：厦门网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!