MySQL事务学习-2024-11-30

标签：11 IX 事务 -- lock 30 2024 article id

[学习记录]MySQL事务

锁的兼容情况总结-GPT

S锁和X锁的兼容性

在MySQL中，S锁（共享锁）和X锁（排他锁） 的兼容性如下：

锁类型	S锁	X锁
S锁	√ 兼容	× 不兼容
X锁	× 不兼容	× 不兼容

具体说明：

S锁（共享锁）：
- 多个事务可以同时对同一数据加S锁（即允许多个事务同时读取数据）。
- 如果一个事务已经持有S锁，其他事务无法对该数据加X锁。
X锁（排他锁）：
- 持有X锁的事务可以读写数据。
- 如果一个事务已经持有X锁，其他事务无法对该数据加S锁或X锁。

IS和IX的机制

IS锁（意向共享锁）和IX锁（意向排他锁） 是表级锁，用于协调行级锁的加锁意图。

目的：

避免表级锁与行级锁冲突时的重复检查。
提高事务处理的效率。

锁类型与兼容性

注意, 这里的内容,我去找了官网的说明, 以避免GPT结果不正确, 官网地址: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
以下是 MySQL8.0 官网上的表级锁类型兼容性总结
Compatible: 兼容的
Conflict: 冲突

从这里开始下面的还是GPT的总结

IS锁（意向共享锁）

特点：
- 表示事务想要在某些行上加S锁。
- 兼容其他事务的IS锁和IX锁。
- 与S锁和X锁的兼容性根据表锁情况判断。
场景：
当事务对表中某些行加S锁时，事务会先在表上申请IS锁。

IX锁（意向排他锁）

特点：
- 表示事务想要在某些行上加X锁。
- 兼容其他事务的IS锁和IX锁。
- 不兼容S锁或X锁。
场景：
- 当事务对表中某些行加X锁时，事务会先在表上申请IX锁。

S锁、X锁与IS、IX锁的关系

行级锁操作的前置步骤：
- 如果事务要对行加S锁，必须先在表级别加IS锁。
- 如果事务要对行加X锁，必须先在表级别加IX锁。
加锁顺序：
- 表级意向锁（IS或IX）与行级锁（S或X）配合使用。
- 例如，事务对表的一行记录加S锁时，会先对表加IS锁。

总结

S锁和X锁可用于行级的数据保护，控制数据的读写权限。
IS锁和IX锁是表级的意向锁，用于快速判断是否允许对某些行加行级锁。
锁兼容性：
- S锁适用于多事务同时读，但与X锁冲突。
- IX锁和IS锁之间兼容，但意向锁与表级S锁或X锁可能冲突。

官网中关于Intention Locks(意向)的说明

There are two types of intention locks:
有两种类型的意图锁：

An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
意图共享锁（IS）表示一个事务打算对表中的某些行设置共享锁。
An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.
意图排他锁（IX）表示一个事务打算对表中的某些行设置排他锁。

Intention locks do not block anything except full table requests (for example, LOCK TABLES … WRITE).

意图锁不会阻塞任何操作, 除非是对整个表的请求（例如，LOCK TABLES … WRITE）。

The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.
意图锁的主要目的是表明某个事务正在锁定某一行，或者即将锁定表中的某一行。

学习目标

补充遗漏的最后一种: 共享锁与共享锁兼容.
验证看看: IS锁与X锁的冲突, 是指明了X是表级锁的时候, 而不是行X锁.

SQL代码块记录

1. 共享锁与共享锁兼容

代码块

-- 1. 事务A针对id'3'上S锁
mysql> BEGIN;SELECT * FROM article WHERE id="3" LOCK IN SHARE MODE;
Query OK, 0 rows affected (0.00 sec)

+----+-------+-----------+--------------+
| id | name  | words_num | content_desc |
+----+-------+-----------+--------------+
| 3  | test2 |       999 | 1            |
+----+-------+-----------+--------------+
1 row in set (0.01 sec)

-- 2. 事务B针对id'3'上S锁
mysql> BEGIN;SELECT * FROM article WHERE id="3" LOCK IN SHARE MODE;
Query OK, 0 rows affected (0.00 sec)

+----+-------+-----------+--------------+
| id | name  | words_num | content_desc |
+----+-------+-----------+--------------+
| 3  | test2 |       999 | 1            |
+----+-------+-----------+--------------+
1 row in set (0.01 sec)

-- 3. 事务C针对id'3'上X锁
mysql> BEGIN;SELECT * FROM article WHERE id="3" for UPDATE;
Query OK, 0 rows affected (0.00 sec)
-- (会卡住)
-- 4. 在事务C超时前, 查看锁和事务的状态
mysql> select trx_id,trx_isolation_level from INNODB_TRX;
+-----------------+---------------------+
| trx_id          | trx_isolation_level |
+-----------------+---------------------+
|           15486 | REPEATABLE READ     |
| 284151884094088 | REPEATABLE READ     |
| 284151884093312 | REPEATABLE READ     |
+-----------------+---------------------+
3 rows in set (0.02 sec)

mysql> SELECT engine_lock_id, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks;
+-------------------------------------+-----------+---------------+-------------+-----------+
| engine_lock_id                      | lock_type | lock_mode     | lock_status | lock_data |
+-------------------------------------+-----------+---------------+-------------+-----------+
| 2676907384208:1073:2676913017368    | TABLE     | IX            | GRANTED     | NULL      |
| 2676907384208:11:4:15:2676896897048 | RECORD    | X,REC_NOT_GAP | WAITING     | '3'       |
| 2676907382656:1073:2676913015832    | TABLE     | IS            | GRANTED     | NULL      |
| 2676907382656:11:4:15:2676896890904 | RECORD    | S,REC_NOT_GAP | GRANTED     | '3'       |
| 2676907383432:1073:2676913016600    | TABLE     | IS            | GRANTED     | NULL      |
| 2676907383432:11:4:15:2676896893976 | RECORD    | S,REC_NOT_GAP | GRANTED     | '3'       |
+-------------------------------------+-----------+---------------+-------------+-----------+
6 rows in set (0.03 sec)
-- 5. 在事务C超时前, 提交事务A和事务B, 然后就能看到事务C查出了结果(没什么特别的) 最后再看看锁和事务的状态做个结尾
-- 提交A
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
-- 提交B
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
-- 看到C
+----+-------+-----------+--------------+
| id | name  | words_num | content_desc |
+----+-------+-----------+--------------+
| 3  | test2 |       999 | 1            |
+----+-------+-----------+--------------+
1 row in set (4.17 sec)
-- 看看锁
mysql> SELECT engine_lock_id, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks;
+-------------------------------------+-----------+---------------+-------------+-----------+
| engine_lock_id                      | lock_type | lock_mode     | lock_status | lock_data |
+-------------------------------------+-----------+---------------+-------------+-----------+
| 2676907384208:1073:2676913017368    | TABLE     | IX            | GRANTED     | NULL      |
| 2676907384208:11:4:15:2676896897048 | RECORD    | X,REC_NOT_GAP | GRANTED     | '3'       |
+-------------------------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.03 sec)

执行结果

很明显, 事务A的S锁,可以和事务B的S锁, 同时存在, 但是事务C的X锁, 需要等前面的S锁都释放了, 它才能拿到.
不要忘记了, 这些都是在隔离等级是REPEATABLE READ时, 使用主键id进行: 行锁的情况

2. IS锁与X锁的冲突, 是表级别的而不是行级别的. 但是IS锁与表级的S锁不冲突. IX锁与表级的S锁和X锁都冲突.

-- 重复过很多次了的就省略了. 下面仅作文字描述
-- 开启一个事务A,里面是select加S锁,锁定id='3'一行. 开启一个事务B, 里面是upadte, 改的是id='5'一行. 都操作的是表article
-- 查看当前锁的情况
mysql> SELECT engine_lock_id, lock_type, lock_mode, lock_status, lock_data FROM performance_schema.data_locks;
+-------------------------------------+-----------+---------------+-------------+-----------+
| engine_lock_id                      | lock_type | lock_mode     | lock_status | lock_data |
+-------------------------------------+-----------+---------------+-------------+-----------+
| 2676907382656:1073:2676913015832    | TABLE     | IX            | GRANTED     | NULL      |
| 2676907382656:11:4:14:2676896890904 | RECORD    | X,REC_NOT_GAP | GRANTED     | '5'       |
| 2676907383432:1073:2676913016600    | TABLE     | IS            | GRANTED     | NULL      |
| 2676907383432:11:4:15:2676896893976 | RECORD    | S,REC_NOT_GAP | GRANTED     | '3'       |
+-------------------------------------+-----------+---------------+-------------+-----------+
4 rows in set (0.03 sec)

-- 再开一个窗口, 尝试对article表,上S锁
mysql> Lock TABLES article READ;
-- (卡住了)

-- 换一个命令,看看表上的锁
mysql> SELECT * FROM performance_schema.metadata_locks where object_name='article';
+-------------+-----------------+-------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA   | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE        | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+-----------------+-------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | be_able_to_read | article     | NULL        |         2676862518896 | SHARED_READ      | TRANSACTION   | GRANTED     | sql_parse.cc:6140 |             226 |             65 |
| TABLE       | be_able_to_read | article     | NULL        |         2676862521056 | SHARED_WRITE     | TRANSACTION   | GRANTED     | sql_parse.cc:6140 |             230 |             16 |
| TABLE       | be_able_to_read | article     | NULL        |         2676867725872 | SHARED_READ_ONLY | TRANSACTION   | PENDING     | sql_parse.cc:6140 |             229 |              6 |
+-------------+-----------------+-------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
3 rows in set (0.04 sec)

-- 去提交事务B
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
-- (此时能立刻看到, 旁边执行Lock TABLES article READ;窗口中的pedding消失了)

-- 查看表上的锁
mysql> SELECT * FROM performance_schema.metadata_locks where object_name='article';
+-------------+-----------------+-------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA   | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE        | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+-----------------+-------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | be_able_to_read | article     | NULL        |         2676862518896 | SHARED_READ      | TRANSACTION   | GRANTED     | sql_parse.cc:6140 |             226 |             65 |
| TABLE       | be_able_to_read | article     | NULL        |         2676867725872 | SHARED_READ_ONLY | TRANSACTION   | GRANTED     | sql_parse.cc:6140 |             229 |              6 |
+-------------+-----------------+-------------+-------------+-----------------------+------------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.04 sec)

-- 解除article表的S锁
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
-- 查看表上的锁
mysql> SELECT * FROM performance_schema.metadata_locks where object_name='article';
+-------------+-----------------+-------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA   | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE   | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+-----------------+-------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | be_able_to_read | article     | NULL        |         2676862518896 | SHARED_READ | TRANSACTION   | GRANTED     | sql_parse.cc:6140 |             226 |             65 |
+-------------+-----------------+-------------+-------------+-----------------------+-------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.04 sec)

-- 对article表上X锁
mysql> Lock TABLES article WRITE;
-- (卡住了)

-- 去提交事务A
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
-- (此时能立刻看到, 旁边执行Lock TABLES article WRITE;窗口中的pedding消失了)

-- 再看看表上的锁
mysql> SELECT * FROM performance_schema.metadata_locks where object_name='article';
+-------------+-----------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA   | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+-----------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | be_able_to_read | article     | NULL        |         2676867727216 | SHARED_NO_READ_WRITE | TRANSACTION   | GRANTED     | sql_parse.cc:6140 |             229 |             11 |
+-------------+-----------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
1 row in set (0.04 sec)

现象

事务A给表上了IS锁, 事务B给表上了IX锁. 当IX锁存在时, 无法对表上READ的LOCK
释放了事务B的IX锁后, 事务A还占用IS锁时, 对表上READ的LOCK成功了
释放了事务B的IX锁后, 事务A还占用IS锁时, 对表上WRITE的LOCK会卡住
释放了事务A的IS锁后, 对表上WRITE的成功了
IS锁可以和行X锁共存, IX锁也可以和行S锁,行X锁共存.

标签：11,IX,事务,--,lock,30,2024,article,id
From： https://blog.csdn.net/qq_21410843/article/details/144160038