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

来源:互联网 / 作者:SKY / 2019-06-25 15:16 / 点击:
本文中,我们详细介绍MySQL InnoDB存储引擎各种不同类型的锁,以及不同SQL语句分别会加什么样的锁。

当前事务的隔离级别

SQL是一致性非锁定读(consistent nonlocking read)还是DML(INSERT/UPDATE/DELETE)或锁定读(locking read)

SQL执行时是否使用了索引,所使用索引的类型(主键索引,辅助索引、唯一索引)

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

一、隔离级别(isolation level)

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

Read Uncommitted。下面简称RU

Read Committed。下面简称RC

Repeatable Read(MySQL的默认隔离级别)。下面简称RR

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是否都需要加锁呢?

普通SELECT 时使用一致性非锁定读,不加锁;

锁定读SELECT 使用锁定读,加锁;

此外,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时,是否有什么不同呢?是的,两种隔离级别下,拍得快照的时间点不同

RC时,同一个事务内的每一个一致性读总是设置和读取它自己的最新快照。也就是说,每次读取时,都再重新拍得一个最新的快照(所以,RC时总是可以读取到最新提交的数据)。

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两种模式。

阅读延展

1
3