一、引出
在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。 为保证数据的一致性,需要对 并发操作进行控制
,因此产生了 锁
。 锁机制
也为实现 MySQL 的各个隔离级别提供了保证。锁冲突
也是影响数据库并发访问性能的一个重要因素。
1.1 并发事务 读-读
读-读
情况,即并发事务相继 读取相同的记录
。读取操作本身不会对记录有任何影响,并不会引起什么问题,所以允许这种情况的发生。
1.2 并发事务 写-写
写-写
情况,即并发事务相继对相同的记录做出改动。在这种情况下会发生 脏写
的问题,任何一种隔离级别都不允许这种问题的发生。在多个未提交事务相继对一条记录做改动时,需要让它们 排队执行
,这个排队的过程其实是通过 锁
来实现的。 锁其实是一个 内存中的结构
,在事务执行前本来是没有锁的,也就是说一开始是没有 锁结构
和记录进行关联的。
当一个事务想对这条记录做改动时,首先会看看内存中有没有与这条 记录关联
的锁结构,当没有的时候就会在内存中生成一个锁结构与之关联。
1.3 并发事务 读-写或写-读
即一个事务进行读取操作,另一个进行改动操作。这种情况下可能发生 脏读
、不可重复读
、幻读
的问题。 各个数据库厂商对 SQL标准 的支持都可能不一样。比如 MySQL 在 REPEATABLE READ 隔离级别上就已经 解决了幻读问题。
1.4 并发问题的解决方案
方案一:读操作利用多版本并发控制( MVCC
),写操作进行 加锁
。
普通的 SELECT 语句在 READ COMMITTED 和 REPEATABLE READ 隔离级别下会使用到 MVCC 读取记录。 在 READ COMMITTED
隔离级别下,一个事务在执行过程中每次执行 SELECT 操作时都会生成一个 ReadView,ReadView的存在本身就保证了 事务不可以读取到未提交的事务所做的更改
,也就是避免了脏读现象; 在 REPEATABLE READ
隔离级别下,一个事务在执行过程中只有 第一次执行SELECT操作
才会生成一个ReadView,之后的SELECT操作都 复用
这个ReadView,这样也就避免了不可重复读和幻读的问题。
一些业务场景不允许读取记录的旧版本,而是每次都必须去 读取记录的最新版本
。 比如,在银行存款的事务中,你需要先把账户余额读出来,然后将其加上本次存款的数额,然后再写到数据库中。 在将账户余额读取出来后,就不想让别的事务再访问该余额,直到本次存款事务操作完成,其他事务才可以访问账户的余额。
采用 MVCC
方式的话,读-写操作彼此并不冲突,性能更高。 采用 加锁
方式的话,读-写操作彼此需要排队执行,影响性能。 一般情况下我们当然愿意采用 MVCC 来解决读-写操作并发执行的问题。
二、分类
2.1 按操作类型:读锁、写锁
也称为 共享锁
、英文用 S
表示,语法为 select ... for share
; 如果当前事务执行了该语句,那么它会为读取到的记录加S锁,这样允许别的事务继续获取这些记录的读锁
。 如果别的事务想要获取这些记录的写锁
,那么它们会阻塞,直到当前事务提交后将这些记录上的读锁
释放掉。
也称为 排他锁
、英文用 X
表示,语法为 select .... for update
。 当前写操作没有完成前,它会阻断其他写锁和读锁;即在给定的时间里只有一个事务能执行写入,并防止其他用户读取正在写入的同一资源。
平常所用到的 写操作
无非时 update 、delete、insert这三种:
场景 说明 delete 对一行记录坐delete操作的过程其实是先在B+树中定位到这条记录的位置,然后获取这条记录的写锁
,再执行delete mark
操作,我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取写锁
的锁定读
update 对一条记录做update操作时分为三种情况,见下表 insert 一般情况下,新插入一条记录的操作并不加锁,通过一种称之为隐式锁
的结构来保护这条新插入的记录再本事务提交前不被别的事务访问。
update 场景 说明 未修改该记录的键值,并且被更新的列占用的存储空间在修改前后未发生变化。 则先在B+树中定位到这条记录的位置,然后再获取一下记录的写锁
,最后在原记录的位置进行修改操作。 未修改该记录的键值,并且至少有一个被更新的列占用的存储空间在修改前后发生变化。 先在B+树中定位到这条记录的位置,然后再获取一下记录的写锁
,将该记录彻底删除掉(就是把记录彻底移入垃圾链表),最后再插入一条新记录,新插入的记录由insert操作提供的隐式锁进行保护。 修改该记录的键值 相当于再原记录上做delete
操作之后再来一次insert
操作,加锁操作就需要按照delete
和insert
的规则进行了。
默认获取不到锁的情况下会一直等待,直到 innodb_lock_wait_timeout
超时; 在8.0版本中,select … for update,select …for share 后添加 nowait
、skip locked
语法,跳过锁等待,或者跳过锁定; nowait 会立即报错返回,而skip locked也会立即返回,只是返回的结果不包含被锁定的行。
2.2 按操作粒度
为了尽可能的提高数据库的并发度,每次锁定的数据范围越小越好; 理论上每次只锁定当前操作的数据的方案会得到最大的并发度,但是管理锁是很耗资源
的事情(涉及获取,检查,释放锁等动作); 因此数据库操作需要在并发响应
和系统性能两方面进行平衡,这样就产生了锁粒度(Lock granularity)
的概念。
2.2.1 表锁:S/X、IS/IX、AUTO-INC、MDL
LOCK TABLES t READ ;
LOCK TABLES t WRITE ;
锁类型 自己可读 自己可写 自己可操作其他表 他人可读 他人可写 读锁 是 否 否 是 否,等 写锁 是 是 否 否,等 否,等
在对某个表执行增删改查语句时,InnoDB
存储引擎是不会为这个表添加表级别的S锁或者X锁的;MyISAM
在执行查询语句前,会给涉及的所有表加读锁,在执行增删改操作前,会给涉及的表加写锁。 在对某个表执行一些诸如 ALTER TABLE
、 DROP TABLE
这类的 DDL
语句时,其他事务对这个表并发执行诸如增删改查的语句会发生阻塞
;同理,某个事务中对某个表执行增删改查语句时,在其他会话中对这个表执行 DDL 语句也会发生阻塞;这个过程其实是通过在 server层
使用一种称之为 元数据锁
(英文名:Metadata Locks
,简称 MDL
)结构来实现的。 在一些特殊情况下,比方说 崩溃恢复
过程中会用到表锁;程序员尽量避免使用因为它们并 不会提供什么额外的保护
,只是会降低并发能力
。
InnoDB supports multiple granularity locking which permits coexistence of row locks and table locks
. Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later
for a row in a table. The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table. 如果另一个任务 试图在该表级别上应用共享或排它锁
,则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁,而 只需检查表上的意向锁 。 IX,IS是表级锁,不会和行级的X,S锁发生冲突,只会和表级的X,S发生冲突。 意向锁是由存储引擎自己维护的,用户无法手动操作意向锁 ,在为数据行加共享 / 排他锁之前,InooDB 会先获取该数据行所在数据表的对应意向锁。
SELECT column FROM table . . . FOR SHARE ;
SELECT column FROM table . . . FOR UPDATE ;
X IX S IS X Conflict Conflict Conflict Conflict IX Conflict Compatible Conflict Compatible S Conflict Conflict Compatible Compatible IS Conflict Compatible Compatible Compatible
表的 id 字段声明了 AUTO_INCREMENT,意味着在书写插入语句时不需要为其赋值,系统会自动为它赋上递增的值:
插入场景 说明 Simple inserts 可以 预先确定要插入的行数
(当语句被初始处理时)的语句,包括没有嵌套子查询的单行和多行 INSERT…VALUES() 和 REPLACE 语句。 Bulk inserts 事先不知道要插入的行数
(和所需自动递增值的数量)的语句,比如 INSERT … SELECT , REPLACE … SELECT 和 LOAD DATA 语句Mixed-mode inserts 是 Simple inserts 语句但是指定部分新行的自动递增值
。例如 INSERT INTO teacher (id,name) VALUES (1,‘a’), (NULL,‘b’), (5,‘c’), (NULL,‘d’) 只是指定了部分id的值,另一种类型的“混合模式插入”是 INSERT … ON DUPLICATE KEY UPDATE 。
通过自增锁 innodb_autoinc_lock_mode
防止并发场景下生成相同的 ID,参考 官网 :
取值 说明 0 传统锁定模式每当执行insert的时候,都会得到一个表级锁(AUTO-INC锁),使得语句中生成的auto_increment为顺序,且在binlog中重放的时候,可以保证master与slave中数据的auto_increment是相同的。 1 连续锁定模式优化 Simple inserts 场景,通过在 mutex(轻量锁) 的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁, 它只在分配过程的持续时间内保持,而不是直到语句完成。 2 交错锁定模式默认 。1. 自动递增值保证在所有 并发执行
的所有类型的 insert 语句中是唯一且 单调递增
的;2. 多个语句可以同时生成数(即,跨语句交叉编号),但为任何给定语句插入的行生成的值 可能不是连续的
;3. NOT SAFE
when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log.
MDL 的作用是,保证读写的正确性。 比如,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个 表结构做变更 ,增加了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。 因此,当对一个表做增删改查操作的时候,加 MDL读锁;当要对表做结构变更操作的时候,加 MDL 写锁。
2.2.2 行锁:记录锁、间隙锁、临键锁、插入意向锁
行锁(Row Lock)也称为记录锁,只在存储引擎层实现;优点:锁定粒度小,发送锁冲突概率低
,可以实现的并发度高
;缺点:对于锁的开销比较大
,加锁会比较慢,容易出现死锁
情况;InnoDB与MyISAM的最大不同有两点:一支持事务,二是采用了行级锁。
记录锁也就是仅仅把一条记录锁上,官方的类型名称为:LOCK_REC_NOT_GAP
。 比如我们把 id 值为 2 的那条记录加一个记录锁,对周围的数据没有影响:
Mysql 在可重复读
的隔离级别下是可以解决幻读问题的,解决方案有两种,可以使用MVCC(一致性非锁定读),也可以采用加锁
方案解决。 但是在使用加锁方案解决的时候有个大问题,就是事务在第一次执行读取操作时,那些幻影记录尚不存在,我们无法给这些 幻影记录
加上 记录锁
。 InnoDB提出了一种称之为Gap Locks
的锁,官方的类型名称为:LOCK_GAP
,我们可以简称为gap锁
。 比如,把id值为8的那条记录加一个gap锁的示意图如下:
图中id值为8的记录加了gap锁,意味着 不允许别的事务在id值为8的记录前边的间隙插入新记录 ,其实就是 id 列的值 (3, 8)这个区间的新记录是不允许立即插入的。 gap锁的提出仅仅是为了防止插入幻影记录而提出的。
Next-Key Locks
是在InnoDB存储引擎、事务级别在可重复读的情况下使用的数据库锁;有时候我们既想 锁住某条记录
,又想 阻止
其它事务在该记录前边的 间隙插入新记录
; InnoDB 就提出了一种称之为 Next-Key Locks
的锁,官方的类型名称为:LOCK_ORDINARY
;
begin ;
select * from student where id <= 8 and id > 3 for update ;
next-key锁
的本质就是一个 记录锁
和 间隙锁的
组合体,它既能保护好该条记录,又能阻止别的事务将新记录插入被保护记录前边的 间隙
。
插入意向锁(Insert Intention Locks)
一个事务在 插入
一条记录时需要判断一下插入位置是不是被别的事务加了 gap锁
,如果有的话,插入操作需要等待,直到拥有 gap 锁的那个事务提交。 InnoDB 规定事务在等待的时候也需要在内存中生成一个锁结构,表明有事务 想在某个间隙中插入
新记录,但是现在在等待。 InnoDB 就把这种类型的锁命名为 Insert Intention Locks
,官方的类型名称为 LOCK_INSERT_INTENTION
,我们称为 插入意向锁
。 插入意向锁是在插入一条记录行前,由 INSERT 操作产生的一种间隙锁(不是意向锁),并不会阻止别的事务继续获取该记录上任何类型的锁。
2.2.3 页锁
页锁就是在 页的粒度
上进行锁定,锁定的数据资源比行锁要多,因为一个页中可以有多个行记录。 当我们使用页锁的时候,会出现数据浪费的现象,但这样的浪费最多也就是一个页上的数据行。 页锁的开销介于表锁和行锁之间,会出现死锁。锁定粒度介于表锁和行锁之间,并发度一般。
每个层级的锁数量是有限制的,因为锁会占用内存空间,锁空间的大小是有限的
。 当某个层级的锁数量超过了这个层级的阈值时,就会进行 锁升级
。 锁升级就是用更大粒度的锁替代多个更小粒度的锁,比如InnoDB 中行锁升级为表锁,这样做的好处是 占用的锁空间降低了,但同时数据的并发度也下降了
。
2.3 按对待锁的态度
乐观锁和悲观锁并不是锁,而是锁的设计思想。
2.3.1 悲观锁(Pessimistic Locking)
悲观锁总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞
,直到它拿到锁。 即共享资源每次只能给一个线程使用,其它线程阻塞,用完后再把资源转给其它线程;比如行锁、表锁等、读锁、写锁等,都时在操作之前先上锁,当其他线程想要访问数据的时候,都需要阻塞挂起。 Java中的 synchronized
、ReetrantLock
等 独占锁
都是 悲观锁的思想实现。
2.3.2 乐观锁(Optimistic Locking)
乐观锁认为对同一数据的并发操作不会总发生,属于小概率事件,不用每次都对数据上锁; 只在更新的时候判断一下在此期间别人有没有去更新这个数据,也就是不采用数据库自身的锁机制,而是通过程序来实现。 在程序上,我们可以采用 版本号机制
或者 CAS机制
实现。在Java中 java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁的CAS方式实现的。
在表中设计一个 版本字段 version ,第一次读的时候,会获取 version 字段的取值。 然后对数据进行更新或删除操作时,会执行 UPDATE ... SET version = version + 1 WHERE version = version
。 此时如果已经有事务对这条数据进行了更改,修改就不会成功。
时间戳和版本号机制一样,也是在更新提交的时候,将当前数据的时间戳和更新之前取得的时间戳进行比较,如果两者一致则更新成功,否则就是版本冲突。
2.3.3 适用场景
乐观锁 适合 读操作多
的场景,相对来说写的操作比较少。它的优点在于 程序实现
,不存在死锁
问题,不过适用场景也会相对乐观,因为它阻止不了除了程序以外的数据库操作。悲观锁 适合 写操作多
的场景,因为写的操作具有 排它性
。采用悲观锁的方式,可以在数据库层面阻止其他事务对该数据的操作权限,防止 读 - 写 和 写 - 写 的冲突。
2.4 按加锁的方式
2.4.1 隐式锁
一个事务在执行 insert
操作时,如果即将插入的 间隙
已经被其他事务加了 gap锁
,那么本次 insert
操作会阻塞,并且当前事务会在该间隙上加一个 插入意向锁
,否则一般情况下insert操作是不加锁的。 如果一个事务首先插入了一条记录(此时并没有在内存生产与该记录相关联的锁结构),然后另一个事务立即使用 select ... Lock IN share mode
或 select ... for update
语句来操作这条记录,也就要获取这条记录的 S/X锁。 这个时候前边提过的 事务id
又要起作用了,我们把聚簇索引和二级索引中的记录分开看以下:
索引类型 操作说明 聚簇索引 1、对于聚簇索引记录来说,有一个 trx_id
隐藏列,该隐藏列记录着最后改动该记录的 事务id
;2、当前事务新插入一条聚簇索引记录后,该记录的 trx_id
隐藏列代表的的就是当前事务的 事务id
;3、如果其它事务此时想对该记录添加 S锁
或者 X锁
时,首先会看一下该记录的trx_id
隐藏列代表的事务是否是当前的活跃事务
;4、如果是的话,那么就帮助当前事务创建一个 X锁
(也就是为当前事务创建一个锁结构, is_waiting
属性是 false
),然后自己进入等待状态(也就是为自己也创建一个锁结构, is_waiting
属性是 true
)。 二级索引 1、对于二级索引记录来说,本身并没有 trx_id
隐藏列;2、在二级索引页面的 PageHeader
部分有一个 PAGE_MAX_TRX_ID
属性,该属性代表对该页面做改动的最大的 事务id
;3、如果 PAGE_MAX_TRX_ID
属性值小于当前最小的活跃 事务id
,那么说明对该页面做修改的事务都已经提交了;4、否则就需要在页面中定位到对应的二级索引记录,然后回表找到它对应的聚簇索引记录,然后再重复 情景一
的做法。
即一个事务对新插入的记录可以不显式的加锁(生成一个锁结构),但是由于 事务id
的存在,相当于加了一个 隐式锁
;别的事务在对这条记录加S锁
或者X锁
时,由于隐式锁
的存在,会先帮助当前事务生成一个锁结构
,然后自己再生成一个锁结构后进入等待状态
。 隐式锁是一种延迟加载的机制 ,从而来减少加锁的数量;隐式锁在实际内存对象中并不含有这个锁信息,只有当产生锁等待的时候,隐式锁转化成显式锁。
InnoDB的每条记录中都有一个隐含的trx_id字段,这个字段存在于聚集索引的B+Tree中。 在操作一条记录前,首先根据记录总的trx_id检查该事务是否是活动的事务(未提交或者回滚)。如果是活动的事务,首先将隐式锁
转换为显式锁
(就是为该事务添加一个锁)。 检查是否有锁冲突,如果有,创建锁,并设置为waiting状态。如果没有冲突不加锁,跳到5。 等待加锁成功,被唤醒,或者超时。 写数据,并将自己的trx_id写入trx_id字段。
2.4.2 显式锁
通过特定的语句进行加锁,我们一般称之为显示加锁; 显示加共享锁:
select . . . . lock in share mode
显示加排它锁:
select . . . . for update
2.5 全局锁
全局锁就是对 整个数据库实例
加锁。 当你需要让整个库处于 只读状态
的时候,可以使用这个命令,之后其他线程的以下语句会被阻塞:数据更新语句(数据的增删改)、数据定义语句(包括建表、修改表结构等)和更新类事务的提交语句。 全局锁的典型使用场景是 全库逻辑备份
。 全局锁的命令:
Flush tables with read lock
2.6 死锁
两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁。
update account set balance = balance - 100 where name = 'A'
update account set balance = balance + 100 where name = 'B'
update account set balance = balance - 100 where name = 'B'
update account set balance = balance + 100 where name = 'A'
InnoDB 默认采用 wait-for graph 算法主动地检测死锁
; 首先数据库会保存 锁的信息链表
和 事务等待链表
两部分信息,然后构建一个以事务为顶点,锁为边的有向图,判断有向图是否存在环,存在即有死锁; 一旦检测到回路、有死锁,存储引擎会选择 回滚undo量最小的事务
,让其他事务继续执行; 检测死锁会增加操作的复杂度,可以通过 innodb_deadlock_detect=on
参数进行关闭,关闭后死锁关联的事务会根据 innodb_lock_wait_timeout
参数设置的时间直至超时。 At any time, issue SHOW ENGINE INNODB STATUS to determine the cause of the most recent deadlock. That can help you to tune your application to avoid deadlocks .
三、监控
3.1 状态变量
mysql> show status like 'innodb_row_lock%' ;
+
| Variable_name | Value |
+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 71892 |
| Innodb_row_lock_time_avg | 23964 |
| Innodb_row_lock_time_max | 46616 |
| Innodb_row_lock_waits | 3 |
+
5 rows in set ( 0.00 sec)
mysql>
变量 说明 Innodb_row_lock_current_waits 当前正在等待锁定的数量; Innodb_row_lock_time
从系统启动到现在锁定总时间长度(等待总时长/毫秒); Innodb_row_lock_time_avg
每次等待所花平均时间(等待平均时长/毫秒); Innodb_row_lock_time_max 从系统启动到现在等待最长的一次所花的时间; Innodb_row_lock_waits
系统启动后到现在总共等待的次数(等待总次数);
3.2 信息表
表 说明 information_schema.INNODB_TRX 查看阻塞事务的锁 information_schema.data_locks 不但可以看到阻塞该事务的锁,还可以看到该事务所持有的锁。 information_schema.data_lock_waits 阻塞时长
四、间隙锁加锁规则及案例
间隙锁是在可重复读隔离级别下才会生效的。
原则 1 :加锁的基本单位是 next-key lock,next-key lock 是前开后闭区间。原则 2 :查找过程中访问到的对象才会加锁。任何辅助索引上的锁,或者非索引列上的锁,最终都要回溯到主键上,在主键上也要加一把锁。优化 1 :索引上的等值查询,给唯一索引加锁的时候, next-key lock 退化为行锁。也就是说如果 InnoDB 扫描的是一个主键、或是一个唯一索引的话,那InnoDB只会采用行锁方式来加锁优化 2 :索引上(不一定是唯一索引)的等值查询,向右遍历时且最后一个值不满足等值条件的时候, next-keylock 退化为间隙锁。一个 bug :唯一索引上的范围查询会访问到不满足条件的第一个值为止。
CREATE TABLE ` test` (
` id` int ( 11 ) NOT NULL ,
` col1` int ( 11 ) DEFAULT NULL ,
` col2` int ( 11 ) DEFAULT NULL ,
PRIMARY KEY ( ` id` ) ,
KEY ` c` ( ` col1` )
) ENGINE = InnoDB ;
insert into test values ( 0 , 0 , 0 ) , ( 5 , 5 , 5 ) ,
( 10 , 10 , 10 ) , ( 15 , 15 , 15 ) , ( 20 , 20 , 20 ) , ( 25 , 25 , 25 ) ;
4.1 唯一索引等值查询间隙锁
根据原则 1 ,加锁单位是 next-key lock , session A 加锁范围就是 (5,10] ; 同时根据优化 2 ,由于表 test 中没有 id=7 的记录,next-key lock 退化成间隙锁,因此最终加锁的范围是 (5,10)。
4.2 非唯一索引等值查询锁
根据原则 1 ,加锁单位是 next-key lock ,左开右闭,5是闭上的,因此会给 (0,5] 加上 next-key lock。 要注意 c 是普通索引,因此仅访问 c=5 这一条记录是不能马上停下来的(可能有col1=5的其他记录),需要向右遍历,查到c=10 才放弃。根据原则 2 ,访问到的都要加锁,因此要给 (5,10] 加 next-key lock 。 但是同时这个符合优化 2 :等值判断,向右遍历,最后一个值不满足 col1=5 这个等值条件,因此退化成间隙锁 (5,10) 。 根据原则 2 , 只有访问到的对象才会加锁,这个查询使用覆盖索引,并不需要访问主键索引,所以主键索引上没有加任何锁,这就是为什么 session B 的 update 语句可以执行完成。 但 session C 要插入一个 (7,7,7) 的记录,就会被 session A 的间隙锁 (5,10) 锁住 这个例子说明,锁是加在索引上的
。 执行 for update 时,系统会认为你接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁。 如果你要用 lock in share mode来给行加读锁避免数据被更新的话,就必须得绕过 覆盖索引的优化
,因为覆盖索引不会访问主键索引,不会给主键索引上加锁。
4.3 主键索引范围查询锁
select * from test where id= 10 for update ;
select * from tets where id>= 10 and id< 11 for update ;
开始执行的时候,要找到第一个 id=10 的行,因此本该是 next-key lock(5,10] 。 根据优化 1 ,主键 id 上的等值条件,退化成行锁,只加了 id=10 这一行的行锁。 它是范围查询, 范围查找就往后继续找,找到 id=15 这一行停下来,不满足条件,因此需要加 next-key lock(10,15] 。 session A 这时候锁的范围就是主键索引上,行锁 id=10 和 next-key lock(10,15] 。首次 session A 定位查找 id=10 的行的时候,是当做等值查询来判断的,而向右扫描到 id=15 的时候,用的是范围查询判断。
4.4 非唯一索引范围查询锁
在第一次用 col1=10 定位记录的时候,索引 c 上加了 (5,10] 这个 next-key lock 后,由于索引 col1 是非唯一索引,没有优化规则,也就是 说不会蜕变为行锁,因此最终 sesion A 加的锁是,索引 c 上的 (5,10] 和 (10,15] 这两个 next-keylock 。 这里需要扫描到 col1=15 才停止扫描,是合理的,因为 InnoDB 要扫到 col1=15 ,才知道不需要继续往后找了。
4.5 唯一索引范围查询锁 bug
session A 是一个范围查询,按照原则 1 的话,应该是索引 id 上只加 (10,15] 这个 next-key lock ,并且因为 id 是唯一键,所以循环判断到 id=15 这一行就应该停止了。 但是实现上, InnoDB 会往前扫描到第一个不满足条件的行为止,也就是 id=20 。而且由于这是个范围扫描,因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。
4.6 非唯一索引上存在等值的例子
这里,给表 t 插入一条新记录:insert into t values(30,10,30);也就是说,现在表里面有两个c=10的行
delete 语句加锁的逻辑,其实跟 select … for update 是类似的; 这时, session A 在遍历的时候,先访问第一个 col1=10 的记录。同样地,根据原则 1 ,这里加的是 (col1=5,id=5) 到 (col1=10,id=10) 这个 next-key lock 。 由于c是普通索引,所以继续向右查找,直到碰到 (col1=15,id=15) 这一行循环才结束。根据优化 2 ,这是一个等值查询,向右查找到了不满足条件的行,所以会退化成 (col1=10,id=10) 到 (col1=15,id=15) 的间隙锁。
这个 delete 语句在索引 c 上的加锁范围,就是上面图中蓝色区域覆盖的部分。这个蓝色区域左右两边都是虚线,表示开区间,即 (col1=5,id=5) 和 (col1=15,id=15) 这两行上都没有锁
4.7 limit 语句加锁
session A 的 delete 语句加了 limit 2 。你知道表 t 里 c=10 的记录其实只有两条,因此加不加 limit 2 ,删除的效果都是一样的。但是加锁效果却不一样; 明确加了 limit 2 的限制,因此在遍历到 (col1=10, id=30) 这一行之后,满足条件的语句已经有两条,循环就结束了。 因此,索引 col1 上的加锁范围就变成了从( col1=5,id=5)到( col1=10,id=30) 这个前开后闭区间。
4.8 一个死锁的例子
session A 启动事务后执行查询语句加 lock in share mode ,在索引 col1 上加了 next-keylock(5,10] 和间隙锁 (10,15) (索引向右遍历退化为间隙锁); session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ,进入锁等待; 实际上分成了两步,先是加 (5,10) 的间隙锁,加锁成功;然后加 col1=10 的行锁,因为sessionA上已经给这行加上了读锁,此时申请死锁时会被阻塞 然后 session A 要再插入 (8,8,8) 这一行,被 session B 的间隙锁锁住。由于出现了死锁, InnoDB 让 session B 回滚
4.9 order by 索引排序的间隙锁 1
begin ;
select * from test where id> 9 and id< 12 order by id desc for update ;
首先这个查询语句的语义是 order by id desc ,要拿到满足条件的所有行,优化器必须先找到 “ 第一个 id<12 的值 ” 。 这个过程是通过索引树的搜索过程得到的,在引擎内部,其实是要找到 id=12 的这个值,只是最终没找到,但找到了 (10,15) 这个间隙。( id=15 不满足条件,所以 next-key lock 退化为了间隙锁 (10,15) 。) 然后向左遍历,在遍历过程中,就不是等值查询了,会扫描到 id=5 这一行,又因为区间是左开右闭的,所以会加一个next-key lock (0,5] 。 也就是说,在执行过程中,通过树搜索的方式定位记录的时候,用的是 “ 等值查询 ” 的方法。
4.10 order by 索引排序的间隙锁 2
由于是 order by col1 desc ,第一个要定位的是索引 col1 上 “ 最右边的 ”col1=20 的行。这是一个非唯一索引的等值查询:左开右闭区间,首先加上 next-key lock (15,20] 。 向右遍历,col1=25不满足条件,退化为间隙锁 所以会加上间隙锁(20,25) 和 next-key lock (15,20] 。 在索引 col1 上向左遍历,要扫描到 col1=10 才停下来。同时又因为左开右闭区间,所以 next-key lock 会加到 (5,10] ,这正是阻塞session B 的 insert 语句的原因。 在扫描过程中, col1=20 、 col1=15 、 col1=10 这三行都存在值,由于是 select * ,所以会在主键 id 上加三个行锁。 因此, session A 的 select 语句锁的范围就是:1) 索引 col1 上 (5, 25) ;2) 主键索引上 id=15 、 20 两个行锁。
4.11 update 修改数据的例子-先插入后删除
根据 col1>5 查到的第一个记录是 col1=10 ,因此不会加 (0,5] 这个 next-key lock 。session A 的加锁范围是索引 col1 上的 (5,10] 、 (10,15] 、 (15,20] 、 (20,25] 和(25,supremum] 。
之后 session B 的第一个 update 语句,要把 col1=5 改成 col1=1 ,你可以理解为两步:1) 插入 (col1=1, id=5) 这个记录;2) 删除 (col1=5, id=5) 这个记录。 接下来 session B 要执行 update t set col1 = 5 where col1 = 1 这个语句了,一样地可以拆成两步:1) 插入 (col1=5, id=5) 这个记录;2) 删除 (col1=1, id=5) 这个记录。 第一步试图在已经加了间隙锁的 (1,10) 中插入数据,所以就被堵住了。