Mysql 锁学习笔记

embedded/2024/9/23 3:16:17/

目录

Innodb锁

共享锁与排它锁

锁兼容级别

意向锁 - 表级锁

代码示例

表级锁类型兼容性

行锁

代码示例

间隙锁

代码示例

临键锁 - 行锁加间隙锁

插入意向锁

自增锁

SELECT的加锁规则 (RR)

查看锁状态命令

3.0 前置条件

3.1 主键检索

3.2 唯一索引检索

3.3 普通索引检索

3.4 索引的范围检索

3.5 普通检索（无索引）

3.6 总结

参考文章

Innodb锁

SQL 行锁类型说明
INSERT ...排他锁自动加锁
UPDATE ...排他锁自动加锁
DELETE ...排他锁 自动加锁
SELECT （正常）不加任何锁
SELECT ... LOCK IN SHARE MODE共享锁在 SELECT 之后加 LOCK IN SHARE MODE
SELECT ... FOR UPDATE排他锁在 SELECT 之后加 FOR UPDATE

共享锁与排它锁

以下是官网给出的定义，其实共享锁也称为读锁，排他锁可称写锁。

  • A shared (S) lock permits the transaction that holds the lock to read a row.

  • An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.

翻译：

  • 共享锁是允许事务获取锁去读一行数据。
  • 排他锁是允许事务获取锁去更新或者删除一行数据。

锁兼容级别

共享锁S排它锁X
共享锁SYESNO
排它锁XNONO

注：以上YES代表可以共存，NO代表不能共存。

意向锁 - 表级锁

以下是官方给出的两种意向锁解释

  • An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.

  • An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.

翻译：

  • 意图共享锁(IS)表明事务打算对表中的单个行设置共享锁。
  • 意图排他锁(IX)表示事务打算对表中的单个行设置排他锁。

代码示例

SELECT…FOR SHARE设置IS锁，SELECT…FOR UPDATE设置一个IX锁。

The intention locking protocol is as follows:

  • Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.

  • Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

意图锁定协议如下:

  • 在事务获得表中某一行的共享锁之前，它必须首先获得表上的IS锁或更强的锁。
  • 在事务获得表中某一行的排他锁之前，它必须首先获得该表上的IX锁。

表级锁类型兼容性

XIXSIS
XConflictConflictConflictConflict
IXConflictCompatibleConflictCompatible
SConflictConflictCompatibleCompatible
ISConflictCompatibleCompatibleCompatible

行锁

记录锁总是锁定索引记录，即使表没有定义索引。对于这种情况，InnoDB创建一个隐藏的聚集索引，并使用该索引进行记录锁定。

隐藏的聚集索引：MySQL :: MySQL 8.3 Reference Manual :: 17.6.2.1 Clustered and Secondary Indexes

  • 当你在一个表上定义一个PRIMARY KEY时，InnoDB使用它作为聚集索引。应该为每个表定义一个主键。如果没有逻辑上唯一且非空的列或列集可以使用主键，则添加一个自动增量列。自动递增列值是唯一的，并在插入新行时自动添加。
  • 如果你没有为一个表定义PRIMARY KEY, InnoDB使用第一个UNIQUE索引，并且所有的KEY列都定义为not NULL作为集群索引。
  • 如果一个表没有PRIMARY KEY或合适的UNIQUE索引，InnoDB会在包含行ID值的合成列上生成一个隐藏的集群索引GEN_CLUST_INDEX。这些行按照InnoDB分配的行ID排序。行ID是一个6字节的字段，随着新行插入而单调增加。因此，按行ID排序的行在物理上是按插入的顺序排列的。

代码示例

-- 事务A获取记录锁（共享锁）
START TRANSACTION;
SELECT * FROM table_name WHERE condition FOR SHARE;
-- 事务B尝试获取记录锁（排他锁）
START TRANSACTION;
SELECT * FROM table_name WHERE condition FOR UPDATE;

间隙锁

间隙锁是在索引记录之间的间隙上的锁，或者在第一个索引记录之前或最后一个索引记录之后的间隙上的锁。

如果id没有被索引，或者有一个非唯一的索引，语句将锁定前面的空白。间隙锁之间不冲突。

如果将事务隔离级别更改为READ COMMITTED，间隙锁失效。

代码示例

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 For UPDATE;
//防止其他事务将值15插入到列t.c1中，无论列中是否已经存在这样的值，
因为范围中所有现有值之间的间隙被锁定。

临键锁 - 行锁加间隙锁

如果一个会话对索引中的记录R具有共享锁或排他锁，则另一个会话不能在索引顺序R之前的空白中插入新的索引记录。

左开右闭，即不锁住左边界，但会锁住右边界。临键锁的主要设计目的是为了解决所谓的“幻读”问题。
(-infinity, 1]
(1, 7]
(7, +infinity)

-- 使用临键锁查询数据

SELECT * FROM users WHERE id > 1 FOR UPDATE;

插入意向锁

插入意图锁是insert操作在行插入之前设置的一种间隙锁。这个锁以这样一种方式表示插入的意图，即插入到相同索引间隙中的多个事务如果不在间隙内的相同位置插入，则不需要彼此等待。

假设存在值为4和7的索引记录。分别尝试插入值为5和6的事务，在获得插入行上的排他锁之前，每个事务都用插入意图锁锁住4和7之间的间隙，但不会相互阻塞，因为行不冲突。

自增锁

AUTO-INC锁是一种特殊的表级锁，用于在具有AUTO_INCREMENT列的表中插入事务。在最简单的情况下，如果一个事务正在向表中插入值，那么任何其他事务都必须等待对该表进行自己的插入，以便第一个事务插入的行接收连续的主键值。

innodb_autoinc_lock_mode变量控制用于自动增量锁定的算法。它允许您选择如何在可预测的自动递增值序列和插入操作的最大并发性之间进行权衡。

SELECT的加锁规则 (RR)

查看锁状态命令

查看当前事务等待锁sql
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
使用sys.innodb_lock_waits系统视图（仅适用于MySQL 8.0及以上版本）
SELECT * FROM sys.innodb_lock_waits;
查看死锁信息（TRANSACTIONS）
SHOW ENGINE INNODB STATUS
查询所有进程
show processlist;
在使用的表
show open tables where in_use > 0;
查看当前出现的所有锁​​​​​​​
SELECT * FROM information_schema.INNODB_LOCKs;

3.0 前置条件

# 表结构(姓名、公司、工号)
userinfo (Id PK, username, company, usercode);# 表中包含四条记录
5, Gates, Microsoft, 24
7, Bezos, Amazon,35
11, Jobs, Apple,37
14, Elison, Oracle,38

3.1 主键检索

1. 记录存在的情况

# 5是存在的记录，行锁
mysql> select * from userinfo where id=5 for update;mysql> update userinfo set username = "Brand" where id = 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction# X 排他锁
# RECORD 记录锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X             | RECORD   |
+---------------+-------------+

2. 记录不存在的情况

# 6是不存在的记录，间隙锁，锁住的区间为（5,7）,对应上面的前置条件
mysql> select * from userinfo where id = 6 for update;mysql>  insert into user values(6, 'Brand', 'Ali',100);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactio# X 排他锁 + Gap 间隙锁
# RECORD 记录锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X,GAP         | RECORD   |
+---------------+-------------+

3.2 唯一索引检索

与主键检索结果一致，因为这两种都是可以唯一确定索引值和区间范围的。

3.3 普通索引检索

1. 记录存在的情况 左开右闭

# 24是存在的记录，更新行锁，插入间隙锁。24要算在内，锁住的区间为 usercode的(-oo,35]
,对应上面的前置条件
mysql> select * from userinfo where usercode = 24 for update;mysql> insert into user values(6, 'Brand', 'Ali',25);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction# X 排他锁
# RECORD 记录锁  + Gap 间隙锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X,GAP         | RECORD   |
+---------------+-------------+

2. 记录不存在的情况 左开右闭

# 25是不存在的记录，间隙锁，锁住的区间为 usercode的(24,35],对应上面的前置条件
mysql> select * from userinfo where id = 25 for update;mysql>  insert into user values(6, 'Brand', 'Ali',26);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactio# X 排他锁 + Gap 间隙锁
# RECORD 记录锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X,GAP         | RECORD   |
+---------------+-------------+

3.4 索引的范围检索

索引包括主键(默认)、唯一索引和其他普通索引

mysql> select * from userinfo where id > 4 for update;mysql> insert into user values(66, 'Brand', 'Ali',25);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction# X 排他锁 + Gap 间隙锁
# RECORD 记录锁
mysql> select * from performance_schema.data_lock_waits;
+---------------+-------------+
| lock_mode     | lock_type|
+---------------+-------------+
| X,GAP         | RECORD   |
+---------------+-------------+

3.5 普通检索（无索引）

表锁，因为需要扫描整张表。扫描期间所有的操作都不能被获取或变更。

3.6 总结

  • 事务隔离级别为可重复读(Repeated Read, RR)
  • 以主键或唯一索引作为查询条件，有存在值（记录）时是行锁，不存在值时触发间隙锁。
  • 普通索引作为查询条件，恒定间隙锁。
  • 索引作为查询条件，并以范围取值时，产生间隙锁。
  • 无索引时的普通检索，产生表锁。

参考文章

MySQL :: MySQL 8.3 Reference Manual :: 17.7.1 InnoDB Locking

https://www.cnblogs.com/wzh2010/p/18030866

https://www.cnblogs.com/qdhxhz/p/15598873.html


http://www.ppmy.cn/embedded/13111.html

相关文章

去雾笔记01-SRKTDN: Applying Super Resolution Method to Dehazing Task

去雾笔记01-SRKTDN: Applying Super Resolution Method to Dehazing Task

文章目录 Abstract1. Introduction2. Related Work3. Method3.1. Network Architecture Abstract 们提出了一种结合超分辨方法和知识转移方法的模型。我们的模型由一个教师网络、一个去雾网络和一个超分辨率网络组成。 1. Introduction ECNU KT团队提出了一个知识蒸馏[20]模…
阅读更多...
Midjourney如何实现人物角色的一致性？

Midjourney如何实现人物角色的一致性？

在数字艺术和AI生成媒体的发展中，保持人物一致性是一个巨大的挑战。Midjourney作为一个先进的图像生成平台，它如何确保在连续的图像生成过程中，同一人物能保持一致的外观和特征呢？本文将深入探讨Midjourney如何通过技术手段实现这…
阅读更多...
计算机网络的基础知识

计算机网络的基础知识

tcp叫段，udp叫报，ip叫包，链路层叫帧。 我们在网线中跑的就是帧。 tcp段和udp报没有长度限制，只有在ip层和链路层才有包和帧的最大限制。 一个tcp段，在tcp缓存区中被封装，然后传到ip层进行拆分和封装&#x…
阅读更多...
行业早报4.24

行业早报4.24

1.哪吒汽车母公司合众汽车获至少 50 亿元投资，将尽快实现 IPO 上市； 2.消息称抖音生活服务一季度销售额超 1000 亿，但未缩小与美团差距； 3.华晨宝马 2023 年国内销量约 71 万辆，同比增长 6.8%，3 系成主力车…
阅读更多...
x264 编码器亚像素处理流程

x264 编码器亚像素处理流程

H264编码标准中为什么需要亚像素数据 在H.264中，为了提高运动估计的精度，除了在整像素级别上进行搜索外，还会在亚像素级别（如1/2像素和1/4像素）上进行搜索，以找到最佳匹配块。在进行亚像素估计时，需要对非整数像素点进行插值计算。H.264标准中定义了多种插值方法，如六抽…
阅读更多...
第七届蓝桥杯 c/c++ B组

第七届蓝桥杯 c/c++ B组

标题：第几天 2000年的1月1日，是那一年的第1天。 那么，2000年的5月4日，是那一年的第几天？ 注意：需要提交的是一个整数，不要填写任何多余内容。 125 直接查看电脑日历去数或者excal拉即可 标题…
阅读更多...
【1431】java学习网站系统Myeclipse开发mysql数据库web结构java编程计算机网页项目

【1431】java学习网站系统Myeclipse开发mysql数据库web结构java编程计算机网页项目

一、源码特点 java 学习网站系统是一套完善的java web信息管理系统，对理解JSP java编程开发语言有帮助，系统具有完整的源代码和数据库，系统主要采用B/S模式开发。开发环境为TOMCAT7.0,Myeclipse8.5开发，数据库为Mysql5.0&…
阅读更多...
多轴机械臂/正逆解/轨迹规划/机器人运动学/Matlab/DH法 学习记录03——机械臂运动学逆解

多轴机械臂/正逆解/轨迹规划/机器人运动学/Matlab/DH法 学习记录03——机械臂运动学逆解

系列文章目录 本科毕设正在做多轴机械臂相关的内容，这里是一个学习机械臂运动学课程的相关记录。 如有任何问题，可发邮件至layraliufoxmail.com问询。 1. 数学基础 2. 机械臂几何法与DH表示法 3. 机械臂运动学逆解 文章目录 系列文章目录一、引言1.手臂…
阅读更多...
最新文章

Copyright @ 2022~2023