网络数据

当前位置:永利402游戏网站-永利402com官方网站 > 网络数据 > MySQL各类SQL语句的加锁机制

MySQL各类SQL语句的加锁机制

来源:http://www.xtcsyb.com 作者:永利402游戏网站-永利402com官方网站 时间:2019-11-25 15:10

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

 

mysql> select * from test;+----+------+---------+| id | name | address |+----+------+---------+| 3 | 1 | 3 || 6 | 1 | 2 || 7 | 2 | 4 || 8 | 10 | 5 |+----+------+---------+4 rows in set 

 

Round 3:

7.INSERT语句会在插入的行上添加Record lock,Insert语句不会阻止其他事务在同一个gap上插入行。

数据库本质上是一种共享资源,因此在最大程度提供并发访问性能的同时,仍需要确保每个用户能以一致的方式读取和修改数据。锁机制就是解决这类问题的最好武器。

13.如果表上有外键约束,那么任何需要做外键约束检测的DML语句都会在相应的外键上添加S模式的行锁。即便约束失败也会设置这些行锁。

事务 A SELECT FOR UPDATE 语句会拿到表 test 的 Table Lock,此时事务 B 去执行插入操作会阻塞,直到事务 A 提交释放表锁后,事务 B 才能获取对应的行锁执行插入操作。

 

辅助索引下的间隙锁

1.在Repeatable Read和Read Committed事物隔离级别下,SELECT ... FROM语句是一种一致性非锁定读。而在SERIALIZABLE隔离级别下是锁定读,会在扫描的索引记录范围内添加Next-key行锁,不过如果扫描的是唯一索引,那么只会添加Record lock。

总结

这些锁定添加的锁通常是next-key lock,这种锁既锁定扫描到的索引记录,也锁定索引间的gap。不过gap锁可以被显示的禁用,参考的Gap lock部分。

《MySQL 技术内幕 InnoDB 存储引擎》第二版 姜承尧着 About MySQL InnoDB's Lock

3.SELECT ... FROM ... FOR UPDATE在扫描到的索引记录上添加X模式的Next-key行锁,同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。

但是如果事务 A 的 SELECT FOR UPDATE 语句紧跟 WHERE id = 1 的话,那么这条语句只会获取行锁,不会是表锁,此时不阻塞事务 B 对于其他主键的修改操作

MySQL把读操作分为两大类:锁定读和非锁定读(即locking read和nonlocking read),所谓非锁定读就是不对表添加事务锁的读操作,如Repeatable Read和Read Committed隔离级别下的select语句(可能脏读也算?)。MySQL的一致性非锁定读是通过MVCC机制实现的。锁定读是指添加事务锁的读操作,例如select for update和select lock in share mode语句。

当 InnoDB 扫描索引记录的时候,会首先对选中的索引行记录加上行锁,再对索引记录两边的间隙(向左扫描扫到第一个比给定参数小的值, 向右扫描扫描到第一个比给定参数大的值, 以此构建一个区间)加上间隙锁。如果一个间隙被事务 A 加了锁,事务 B 是不能在这个间隙插入记录的。

虽然Insert语句不使用gap行锁,但是会使用一种叫插入意向锁的gap锁,即Insert Inrention Locks。这种锁的作用是为添加行锁做锁冲突检测,具体示例参考的插入意向锁部分。

CREATE TABLE `test`  NOT NULL AUTO_INCREMENT, `name` int NOT NULL, `address` int NOT NULL, PRIMARY KEY , UNIQUE KEY `idex_unique` , KEY `idx_index`  ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

 

Repeatable Read 隔离级别,这是 MySQL 的默认工作级别 检索条件必须有索引(没有索引的话会走全表扫描,那样会锁定整张表所有的记录)

我们这里所说的 “间隙锁” 其实不是 GAP LOCK,而是 RECORD LOCK + GAP LOCK,InnoDB 中称之为 NEXT_KEY LOCK

 

Round 2:

 

下面看个例子,我们建表时指定 name 列为辅助索引,目前这列的取值有 [1,2,10]。间隙范围有 (-∞, 1]、[1,1]、[1,2]、[2,10]、[10, +∞)

 

事务 A SELECT ... WHERE name <= 2 FOR UPDATE; 对 增加间隙锁 事务 B INSERT ... name = 3 阻塞 事务 B INSERT ... name = 300 阻塞 事务 B INSERT ... name = -300 阻塞

 

Round 1:

10.INSERT INTO T SELECT ... FROM S WHERE ...语句会在T表的每个被插入的行上添加X模式的record lock(无gap锁)。

防止间隙内有新数据被插入,因此叫间隙锁 防止已存在的数据,在更新操作后成为间隙内的数据(例如更新 id = 7 的 name 字段为 1,那么 name = 1 的条数就从 2 变为 3)

此外INSERT语句还涉及到主键的重复性检测,示例说明如下:

但是,如果两个事务都针对辅助索引字段 name 进行插入,不需要等待获取锁,因为辅助索引字段即使值相同,在数据库中也是操作不同的记录行,不会冲突。

第一部分:概述

事务 A SELECT ... WHERE name = 2 FOR UPDATE; 对 [1, 10) 增加间隙锁 事务 B INSERT ... name = 1 阻塞 事务 B INSERT ... name = 9 阻塞 事务 B INSERT ... name = 10 成功 事务 B INSERT ... name = 0 成功

 

SELECT FOR UPDATE 下的表锁与行锁

9.REPLACE语句可以看做是INSERT ... ON DUPLICATE KEY UPDATE的简写。

间隙锁可以说是行锁的一种,不同的是它锁住的是一个范围内的记录,作用是避免幻读,即区间数据条目的突然增减。解决办法主要是:

2.SELECT ... FROM ... LOCK IN SHARE MODE在扫描到的索引记录上添加S模式的Next-key行锁,同样的如果扫描的是唯一索引,那么只会添加S模式的Record lock。

首先新建表 test,其中 id 为主键,name 为辅助索引,address 为唯一索引。

 

事务 A SELECT ... WHERE name = 1 FOR UPDATE; 对 增加间隙锁 事务 B INSERT ... name = 1 阻塞 事务 B INSERT ... name = -100 阻塞 事务 B INSERT ... name = 2 成功 事务 B INSERT ... name = 3 成功

 

可见,如果两个事务先后对主键相同的行记录执行 INSERT 操作,因为事务 A 先拿到了行锁,事务 B 只能等待直到事务 A 提交后行锁被释放。同理,如果针对唯一索引字段 address 进行插入操作,也需要获取行锁,图同主键插入过程类似,不再重复。

第二部分:InnoDB中SQL语句的加锁类型

InnoDB 自动使用间隙锁的条件为:

 

先看下 test 表下的数据情况:

11.CREATE TABLE ... SELECT ...语句的加锁机制与INSERT INTO T SELECT ... FROM S WHERE ...完全一致。

InnoDB 锁机制总结

INSERT 方法中的行锁

14.LOCK TABLES也会在表上设置表锁,只是这种表锁并非是InnoDB层的表锁,而是MySQL层的表锁。因此如果死锁涉及到这些表锁时,InnoDB的死锁自动检测机制无法检测到这些表锁。而且由于MySQL层对InnoDB层的行锁机制并不清楚,因此此类表锁甚至可以加在正在使用行锁的InnoDB表上。不过这并不会危及到事务的完整性,具体说明详见:

Update 方法与 Insert 方法结果类似。

如果执行的SQL找不到合适的索引,InnoDB不得不去进行全表扫描,那么InnoDB会把表的每一个聚集索引记录都锁住,这可以看作是表级锁,同样参考的表锁部分。这种全表锁定会导致其他事务无法插入和更改(同样参考链接中的表锁兼容性部分),因此为SQL创建合适的索引是很有必要的,因为表锁(非意向锁)会导致DML操作阻塞。

写在前面

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
--会话A执行:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
--会话B执行:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
--会话C执行:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
--最后会话A在执行:
ROLLBACK;
--最后发现会话B和C形成了死锁。

 

 

 

6.DELETE FROM ... WHERE ...语句会在扫描到的所有记录上添加X模式的next-key lock(即便被删的行不存在),同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。

REPLACE INTO t SELECT ... FROM s WHERE ...或者UPDATE t ... WHERE col IN (SELECT ... FROM s ...)这两种SQL语句对s表的行添加S模式的next-key行锁。

 

关于MySQL的锁机制和事务隔离级别,参考以下两篇博客:

锁定读、update和delete,这些操作通常会在扫描到的索引记录上添加record locks,InnoDB不关心这些行是否会被where条件过滤,因为InnoDB不记得具体的where条件,它只知道哪个索引范围被扫描过。

如果在SQL执行时你需要对次级索引记录加X模式的行锁,那么InnoDB也会检索相应的主键索引并加锁。

 

12.关于AUTO-INC Locks参考的AUTO-INC Locks部分。

8.INSERT ... ON DUPLICATE KEY UPDATE,这种插入语句和普通的INSERT语句区别在于,他会在发生重复性键值错误时向索引记录上添加X行锁,如果是主键那添加X模式的record lock行锁,如果是普通的唯一索引那添加X模式的next-key行锁。这姑且算是对7的死锁问题的一种解决办法吧。

因为开始时会话A在i=1上添加了X模式的行锁,会话BC在做重复性检测时发现已有i=1,于是在各自请求行上的一个S行锁,当A会话rollback后,BC的S行锁都获取到了,此时B和C都需要把S行锁转化为X行锁,但是都不愿意放弃自己的S锁,而S和X是互斥的,因此形成死锁。这个问题其实和SQL Server的更新锁出现的原因一样,只不过SQL Server通过U锁解决了此问题,即重复性检测使用的是U锁,而U锁只能有一个会话获取。

 

4.UPDATE ... WHERE ...语句会在扫描到的所有记录上添加X模式的next-key lock(即便被删的行不存在),同样的如果扫描的是唯一索引,那么只会添加X模式的Record lock。

对于select...for update和select...lock in share mode这种锁定读来说,开始时InnoDB会锁定所有扫描的索引记录,但是最后会释放那些不符合条件的索引记录上的锁(例如被where语句过滤掉的行)。但是在某些情况下由于结果行与源表的联系丢失,导致这些行锁不会被释放,例如:union操作,被扫描的中间结果行会被插入到一个临时表中以便形成最终的结果集,在这种情况下锁定行与原表之间的联系丢失,那么剩余的扫描行直到整个SQL执行结束才会被释放(不是事务执行结束)。

如果事务隔离级别被设置为READ COMMITTED,或者innodb_locks_unsafe_for_binlog设为1而且事物隔离级别不是SERIALIZABLE,那么这两种情况下InnoDB对S表执行一致性非锁定读。否则InnoDB会对S表上的每个行都添加S模式的next-key lock。

官网参考:https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

5.当UPDATE语句修改的是主键索引时,InnoDB会隐式的将所有的次级索引锁定(二级索引都是用主键做书签的,因此修改主键索引是很耗资源的操作)。在插入二级索引记录或者为插入二级索引做重复性检查扫描时(unique index),update也会把受影响的二级索引锁定。

 

本文由永利402游戏网站-永利402com官方网站发布于网络数据,转载请注明出处:MySQL各类SQL语句的加锁机制

关键词: