Mysql 锁学习笔记

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
共享锁S	YES	NO
排它锁X	NO	NO

注：以上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锁。

表级锁类型兼容性

	`X`	`IX`	`S`	`IS`
`X`	Conflict	Conflict	Conflict	Conflict
`IX`	Conflict	Compatible	Conflict	Compatible
`S`	Conflict	Conflict	Compatible	Compatible
`IS`	Conflict	Compatible	Compatible	Compatible

行锁

记录锁总是锁定索引记录，即使表没有定义索引。对于这种情况，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   |
+---------------+-------------+