网站开发技术人员怎么接单,对外宣传推广方案,做网站的销售能干什么,网站建设私活MySQL加锁工作原理 前言前置知识- 锁为什么加在索引上锁的粒度优化提高并发性避免全表扫描优化死锁处理解决幻读问题 什么SQL语句会加行级锁MySQL是如何加行级锁场景模拟代码唯一索引等值查询退化为记录锁为什么会退化为记录锁分析加了什么锁为什么会退化为间隙锁为什么我可以插… MySQL加锁工作原理 前言前置知识- 锁为什么加在索引上锁的粒度优化提高并发性避免全表扫描优化死锁处理解决幻读问题 什么SQL语句会加行级锁MySQL是如何加行级锁场景模拟代码唯一索引等值查询退化为记录锁为什么会退化为记录锁分析加了什么锁为什么会退化为间隙锁为什么我可以插入id19的数据?为什么锁住的范围是(19,21) 唯一索引范围查询 前言
上篇我们聊过锁都有哪些类型,那这篇我们聊MySQL什么时候会把锁添加在索引上. 顺便解释一下为什么MySQL加锁会加在索引上. 文章列举的表和数据代码都经过验证,你直接复制粘贴结果不会错(我这个数据库版本下是这样的).
前置知识- 锁为什么加在索引上
MySQL 在执行锁操作时将锁加在索引上而不是直接加在表的数据上这一做法有几个重要的原因。主要是为了提高数据库操作的效率和并发性减少锁的粒度从而提高系统的性能。具体原因如下.
锁的粒度优化
MySQL 使用索引加锁是为了减少锁的粒度使得锁只作用于相关数据范围而不是锁定整个表。通过锁定索引MySQL 能够更精确地定位到需要操作的行从而仅对需要的行加锁而不是对整个表加锁。这样可以显著提升并发性能。
提高并发性
加锁索引使得多个事务可以同时在同一张表上进行不同的数据操作而不会互相干扰。 举个例子如果两个事务同时查询同一个表但它们的查询条件不同并且有索引MySQL 就可以根据索引定位到不同的数据行对它们分别加锁而不需要锁定整个表这样就能让两个事务同时执行从而提高并发性能。
避免全表扫描
在没有索引的情况下MySQL 需要对整个表进行扫描来查找数据而这个过程会锁住整个表。而如果表上有索引MySQL 就可以通过索引快速定位到目标数据从而只锁定满足条件的行。这不仅减少了锁定的范围还大大提高了查询性能。
优化死锁处理
由于 MySQL 将锁加在索引上索引的有序性和结构化可以帮助 MySQL 更好地处理死锁问题。在涉及多个事务的并发操作中通过对索引的加锁可以确保事务按照一定的顺序进行锁定这样可以减少死锁发生的几率。
解决幻读问题
在高并发环境下通过加锁索引MySQL 可以有效防止幻读现象。 通过对索引的加锁可以确保在事务过程中读取的数据范围是稳定的不会因为其他事务的插入或删除操作而导致不一致。 例如如果事务 A 查询某个范围的数据使用了索引扫描事务 B 插入了一些符合该范围的数据。通过索引加锁事务 A 可以确保在整个事务期间数据范围不被改变从而避免幻读。
什么SQL语句会加行级锁
在说 MySQL 是怎么加行级锁的时候是在说 InnoDB 引擎是怎么加行级锁的,因为MyISAM 引擎并不支持行级锁。 普通的 select 语句是不会对记录加锁的除了串行化隔离级别因为它属于快照读是通过 MVCC多版本并发控制实现的。 如果要在查询时对记录加行级锁可以使用下面这两个方式这两种查询会加锁的语句称为锁定读.
//对读取的记录加共享锁(S型锁)
select ... lock in share mode;
//对读取的记录加独占锁(X型锁)
select ... for update;上面这两条语句类型在使用的必须在一个事务中因为当事务提交了锁就会被释放所以在使用这两条语句的时候要加上 begin 或者 start transaction 开启事务的语句。
update 和 delete 操作都会加行级锁且锁的类型都是独占锁(X型锁)
//对操作的记录加独占锁(X型锁)
update table .... where id 1;
//对操作的记录加独占锁(X型锁)
delete from table where id 1;共享锁S锁满足读读共享读写互斥。独占锁X锁满足写写互斥、读读互斥, 读写互斥。 共享锁仅仅共享度,独占锁什么都不共享!
MySQL是如何加行级锁
行级锁加锁规则比较复杂不同的场景加锁的形式是不同的。 MySQL 的行级锁是通过索引来加锁的。具体来说MySQL 在执行 SELECT、UPDATE、DELETE 等操作时会基于索引主键索引、唯一索引或非唯一索引来加锁确保同一时刻只有一个事务对某一行数据进行修改。 但是既然是根据索引类型来加,那就是有规律的,熟悉之后也不算苦难,还是挺有意思的,有种把脑子缠住的美. 有个现象挺有意思的,行级锁上面提到有三类: Record Lock,Gap Lock(里面分纯Gap Lock和Next-key Lock). 你猜猜哪种锁是最常用的. 如果我说这三种锁实际上可以用一种锁来取代,你猜猜是哪种锁. 那肯定是Next-Key Lock. Next-Key Lock 结合了这两种锁的功能——它既能锁住某一行像行锁一样又能锁住行之间的间隙像间隙锁一样。这样就能在很多情况下做到锁住整个查询的区域确保没有其他事务在中间插入数据或者修改数据。
Next-Key Lock 锁住的是 [X, X] 区间前闭后闭的范围.这意味着它锁住了目标行的数据以及行之间的空隙防止其他事务在这个范围内插入新数据或修改现有数据。Gap Lock 锁住的是 ( , ) 区间前开后开的范围.仅仅锁住了行与行之间的空隙防止其他事务插入数据。Record Lock 锁住的是 [X, X]即特定的一行数据.防止修改或删除。
那如果Next-key 像替代Gap Lock 和Record Lock的话,肯定是要有个退化的,根据不同的场景,退化为Gap Lock 或 Record Lock. 那实际上退化的场景我们大概也能想到:
退化为 Gap Lock如果没有实际的数据行被锁住比如查询的范围内没有数据那么 Next-Key Lock 就会退化为锁住空隙即 Gap Lock。退化为 Record Lock如果查询范围只包含单个数据行例如精确查找某一行那么 Next-Key Lock 会退化为 Record Lock即仅锁住这行数据.
场景模拟代码
-- 创建表
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100),salary DECIMAL(10, 2)
);-- 插入一些模拟数据
INSERT INTO employees (id, name, salary) VALUES
(1, Alice, 5000.00),
(2, Bob, 6000.00),
(3, Charlie, 5500.00),
(4, David, 7000.00),
(5, Eva, 6500.00),
(6, Frank, 7500.00);-- 创建唯一索引
CREATE UNIQUE INDEX idx_id_name ON employees(id, name);-- 创建非唯一索引
CREATE INDEX idx_salary ON employees(salary);
唯一索引等值查询
退化为记录锁
场景: 当查询的记录是「存在」的, next-key lock 会退化成「记录锁」
为什么会退化为记录锁
当我们执行唯一索引等值查询时如果查询的记录存在Next-Key Lock 退化为 记录锁Record Lock的原因主要是因为 唯一索引查询 本身能精确定位到单一的记录。这种情况下MySQL 只需要对该行数据加锁而无需再加上对间隙的锁定防止其他事务插入数据。
唯一索引查询的性质 唯一索引查询能精确地定位到一个具体的行。例如当你查询某个表的主键或者一个唯一索引列时MySQL 知道查询的结果只会有一条记录。比如
SELECT * FROM employees WHERE id 2 FOR UPDATE;这条查询会根据唯一索引假设 id 是唯一索引定位到 id 2 这一行数据而 id 2 只能有一个值。这个查询会直接锁住这一行数据。
没有并发插入的风险 由于唯一索引查询只能返回一个结果所以如果查询的记录存在MySQL 不需要担心其他事务插入数据到这个位置因为该行数据唯一插入不可能发生在该记录的范围内。也就是说不存在插入“间隙”的问题。该记录无法删除,因为加了记录锁,其他事务也无法删除该记录,不会出现前后两次查询的结果集不同,也避免了幻读问题.不需要加Gap Lock
对于 唯一索引等值查询查询的条件足够精确.只会匹配一个唯一的值比如 id 2。因为这个值是唯一的只有这一行数据存在。Gap Lock 锁的是行与行之间的“空白”区域防止其他事务在该区域插入新记录。但由于 唯一索引等值查询 只涉及一条数据MySQL 知道不会有其他事务插入数据在该位置所以没有必要加锁该行之间的空隙区间。
分析加了什么锁
我们可以通过SELECT * FROM performance_schema.data_locks;语句来查询事务执行SQL过程中加了什么锁. 比如上面我们执行的语句:
SELECT * FROM employees WHERE id 2 FOR UPDATE;我们可以在Lock_Type字段里看到一共加了两个锁分别为
表锁 X类型的意向锁行锁 X类型的记录锁
在Lock_Mode 可以确认是next-key锁,间隙锁,记录锁:
如果 LOCK_MODE 为 X说明是 next-key 锁如果 LOCK_MODE 为 X, REC_NOT_GAP说明是记录锁如果 LOCK_MODE 为 X, GAP说明是间隙锁
为什么会退化为间隙锁
假设事务 执行了这条等值查询语句查询的记录是「不存在」于表中的.
begin;
SELECT * FROM employees WHERE id 20 FOR UPDATE;SELECT * FROM performance_schema.data_locks;
执行后获取的结果如下: 从上图可以看到共加了两个锁分别是
表锁X 类型的意向锁行锁X 类型的间隙锁 此时事务 在 id 20 记录的主键索引上加的是间隙锁锁住的范围是 (19, 21)
为什么我可以插入id19的数据?
接下来,如果有其他事务插入19,21这一些记录的话,这些插入的雨具都会阻塞(插入id19会被阻塞,但是插入id9不会被阻塞). 注意如果你用本地同一个事务插入是可以成功插入的,即使你在加了间隙锁后成功插入 id 19并不意味着这个锁没有生效。实际上在你插入记录时锁住的间隙并没有阻止插入操作因为没有其他事务在此位置竞争。间隙锁主要是防止其他事务插入新记录而不是阻止当前事务插入。
为什么锁住的范围是(19,21)
MySQL 根据索引的有序性来推断出加锁的区间间隙锁的目的是防止在当前查询的区间内插入新的记录。如果该查询所涉及的范围是 id 20但没有找到那么 间隙锁会锁住的是 20 的位置区间即锁住 (19, 21) 这个区间防止其他事务插入 id 20 或者更新 id 19 和 id 21 的位置。这并不意味着会锁住 19 或 21 本身而是锁住了一个范围阻止其他事务在该范围内插入记录。
唯一索引范围查询
