首页 > 数据库 >MySQL事务-2024-11-29

MySQL事务-2024-11-29

时间：2024-11-29 23:34:23浏览次数：5  
标签：11 RECORD lock GRANTED 29 2024 trx id

[学习记录]MySQL事务

学习目标

  • 在REPEATABLE READ隔离级别下, 事务A执行select, 事务B执行insert, 看看锁到底是什么情况. 同时重点看看,id是varchar但是写sql时候写的是number, 会有什么危害

SQL代码块记录

1. 事务A执行select, 加S锁. 且 仅通过主键, 过滤出一条结果. 事务B修改同一行数据. 但是id列本是varchar类型, 但是写sql时,写的是数字

代码块
-- 1. 先执行事务A的查询(注意没提交)
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 |       233 | 1            |
+----+-------+-----------+--------------+
1 row in set (0.01 sec)
-- 2. 查看当前锁的情况和事务的情况
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     | IS        | GRANTED     | NULL                   |
| 2676907382656:11:4:1:2676896890904  | RECORD    | S         | GRANTED     | supremum pseudo-record |
| 2676907382656:11:4:2:2676896890904  | RECORD    | S         | GRANTED     | '1'                    |
| 2676907382656:11:4:3:2676896890904  | RECORD    | S         | GRANTED     | '110801'               |
| 2676907382656:11:4:4:2676896890904  | RECORD    | S         | GRANTED     | '2'                    |
| 2676907382656:11:4:13:2676896890904 | RECORD    | S         | GRANTED     | '4'                    |
| 2676907382656:11:4:14:2676896890904 | RECORD    | S         | GRANTED     | '5'                    |
| 2676907382656:11:4:15:2676896890904 | RECORD    | S         | GRANTED     | '3'                    |
+-------------------------------------+-----------+-----------+-------------+------------------------+
8 rows in set (0.04 sec)

mysql> 
select trx_id,trx_isolation_level,trx_state,trx_requested_lock_id  from INNODB_TRX;
+-----------------+---------------------+-----------+-----------------------+
| trx_id          | trx_isolation_level | trx_state | trx_requested_lock_id |
+-----------------+---------------------+-----------+-----------------------+
| 284151884093312 | REPEATABLE READ     | RUNNING   | NULL                  |
+-----------------+---------------------+-----------+-----------------------+
1 row in set (0.03 sec)
-- 3. 事务B中进行更新
mysql> BEGIN;UPDATE article SET words_num = 999 WHERE id = 3;
Query OK, 0 rows affected (0.00 sec)
-- (卡住)
-- 4. 查看当前锁的情况和事务的情况
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              |
+-------------------------------------+-----------+-----------+-------------+------------------------+
| 2676907383432:1073:2676913016600    | TABLE     | IX        | GRANTED     | NULL                   |
| 2676907383432:11:4:2:2676896893976  | RECORD    | X         | WAITING     | '1'                    |
| 2676907382656:1073:2676913015832    | TABLE     | IS        | GRANTED     | NULL                   |
| 2676907382656:11:4:1:2676896890904  | RECORD    | S         | GRANTED     | supremum pseudo-record |
| 2676907382656:11:4:2:2676896890904  | RECORD    | S         | GRANTED     | '1'                    |
| 2676907382656:11:4:3:2676896890904  | RECORD    | S         | GRANTED     | '110801'               |
| 2676907382656:11:4:4:2676896890904  | RECORD    | S         | GRANTED     | '2'                    |
| 2676907382656:11:4:13:2676896890904 | RECORD    | S         | GRANTED     | '4'                    |
| 2676907382656:11:4:14:2676896890904 | RECORD    | S         | GRANTED     | '5'                    |
| 2676907382656:11:4:15:2676896890904 | RECORD    | S         | GRANTED     | '3'                    |
+-------------------------------------+-----------+-----------+-------------+------------------------+
10 rows in set (0.04 sec)

mysql> 
select trx_id,trx_isolation_level,trx_state,trx_requested_lock_id  from INNODB_TRX;
+-----------------+---------------------+-----------+------------------------------------+
| trx_id          | trx_isolation_level | trx_state | trx_requested_lock_id              |
+-----------------+---------------------+-----------+------------------------------------+
|           15430 | REPEATABLE READ     | LOCK WAIT | 2676907383432:11:4:2:2676896893976 |
| 284151884093312 | REPEATABLE READ     | RUNNING   | NULL                               |
+-----------------+---------------------+-----------+------------------------------------+
2 rows in set (0.04 sec)
--5. 提交事务A
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

-- 提交后,事务B中马上能看到
Query OK, 1 row affected (10.60 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--6. 查看当前锁的情况和事务的情况
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              |
+-------------------------------------+-----------+-----------+-------------+------------------------+
| 2676907383432:1073:2676913016600    | TABLE     | IX        | GRANTED     | NULL                   |
| 2676907383432:11:4:1:2676896893976  | RECORD    | X         | GRANTED     | supremum pseudo-record |
| 2676907383432:11:4:2:2676896893976  | RECORD    | X         | GRANTED     | '1'                    |
| 2676907383432:11:4:3:2676896893976  | RECORD    | X         | GRANTED     | '110801'               |
| 2676907383432:11:4:4:2676896893976  | RECORD    | X         | GRANTED     | '2'                    |
| 2676907383432:11:4:13:2676896893976 | RECORD    | X         | GRANTED     | '4'                    |
| 2676907383432:11:4:14:2676896893976 | RECORD    | X         | GRANTED     | '5'                    |
| 2676907383432:11:4:15:2676896893976 | RECORD    | X         | GRANTED     | '3'                    |
+-------------------------------------+-----------+-----------+-------------+------------------------+
8 rows in set (0.04 sec)

mysql> 
select trx_id,trx_isolation_level,trx_state,trx_requested_lock_id  from INNODB_TRX;
+--------+---------------------+-----------+-----------------------+
| trx_id | trx_isolation_level | trx_state | trx_requested_lock_id |
+--------+---------------------+-----------+-----------------------+
|  15430 | REPEATABLE READ     | RUNNING   | NULL                  |
+--------+---------------------+-----------+-----------------------+
1 row in set (0.04 sec)

--8. 提交事务B, 然后看看这个表的全部id
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from article;
+--------+
| id     |
+--------+
| 1      |
| 110801 |
| 2      |
| 3      |
| 4      |
| 5      |
+--------+
6 rows in set (0.02 sec)

-- EXPLAIN一下语句看看
mysql> EXPLAIN SELECT * FROM article WHERE id="3" LOCK IN SHARE MODE;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | article | NULL       | const | PRIMARY       | PRIMARY | 258     | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.02 sec)

mysql> EXPLAIN SELECT * FROM article WHERE id=3 LOCK IN SHARE MODE;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)

mysql> EXPLAIN SELECT * FROM article WHERE id=3;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.03 sec)
现象
  1. 事务A和事务B与之前学习的时候一样的操作, 但是只是写的数字, 可以看到 锁覆盖了整个表的id, 而且还出现了一个没见过的supremum pseudo-record
  2. 在事务A查询没有提交的时候, 事务B在执行update的时候 , 只出现了等一个锁, 而且看起来是按顺序来等着要锁的
GPT总结

现象揭示了MySQL InnoDB引擎在处理SQL语句和锁机制时一些细致而特殊的行为。这些现象可以从数据类型的转换、记录锁的工作原理，以及InnoDB的实现细节三个层面进行解析。

1. VARCHAR主键与WHERE条件中的隐式类型转换

  • 现象：
    在表中，id是VARCHAR类型，SQL语句中使用WHERE id = number会锁定整个表的多个记录，而WHERE id = "string"只锁定匹配的行。
  • 原因：
    • InnoDB的锁是基于索引的，而索引操作依赖于SQL条件的精确匹配。
    • 当id为VARCHAR类型时：
      • 使用WHERE id = "string"会正确匹配索引项，并仅对匹配的行加锁。
      • 使用WHERE id = number时，MySQL会隐式地将number转换为VARCHAR，但这种隐式转换可能导致索引扫描行为异常。
    • 隐式转换的影响：
      • MySQL可能认为number与多个记录潜在匹配，导致锁范围扩展（即锁住更多行或整个索引范围）。
      • 具体锁表现：
        • LOCK IN SHARE MODE：会为可能匹配的行加共享锁，包括主键上的索引范围。
        • 这就是为什么整个表看起来被锁住了。

2. supremum pseudo-record 的含义

  • 现象：
    performance_schema.data_locks 中出现了 supremum pseudo-record。
  • 原因：
    • supremum pseudo-record 是 InnoDB 索引页的逻辑结束标记：
      • 每个索引页（B+树的一个节点）都会有一个额外的伪记录，表示当前页中所有记录的上界。
      • 它本身并不对应任何真实数据，但会参与锁竞争。
      • 当你对表进行操作（例如LOCK IN SHARE MODE 或 FOR UPDATE），MySQL可能会在锁定索引记录时附带锁定supremum pseudo-record。
    • 锁定伪记录的目的：
      • 保护索引页的完整性，防止其他事务插入新的记录到同一个索引范围内。
      • 这是InnoDB的实现细节，确保B+树的插入、删除、更新操作的安全性。
    • 建议：
      • 如果频繁出现伪记录锁，可以检查是否存在大量的范围扫描操作。

3. 事务B等待id='1'的X锁，而非目标行id='3'

  • 现象：
    在事务A未提交时，事务B的UPDATE操作锁住了id='1'的记录，而非目标行id='3'。
  • 原因：
    • 行级锁与索引范围锁：
      • InnoDB 的行锁（record lock）依赖于索引。
      • 如果查询条件未能直接精确匹配主键，InnoDB可能会进行范围扫描，锁定整个索引范围。
      • 在WHERE id = number的情况下，由于隐式类型转换，可能导致索引扫描从id='1'开始，逐行尝试加锁。
    • 锁顺序：
      • InnoDB按照索引顺序加锁。
      • 在尝试获取目标行（id='3'）的锁之前，事务B会按照索引顺序等待之前记录的锁释放，例如id='1'。
      • 这也解释了事务B在等待id='1'的锁，而不是直接等待id='3'。

改进与建议

  1. 避免隐式类型转换：

    • 在SQL语句中，始终确保主键值类型与列定义匹配。
    • 例如，如果主键是VARCHAR，则使用WHERE id = "string"，而不是WHERE id = number。

  2. 优化锁定范围：

    • 使用主键查询，确保InnoDB只对目标行加锁，而非范围锁。
    • 避免在事务中长时间持有共享锁或排他锁，减少锁竞争。

  3. 监控和调试锁行为：

    • 使用performance_schema.data_locks和INNODB_TRX检查锁状态和事务等待。
    • 通过分析锁的顺序和范围，优化SQL语句和索引设计。

  4. 关于supremum pseudo-record：

    • 不需要额外处理，这种锁是InnoDB的正常行为。
    • 如果频繁出现伪记录锁，可以检查是否存在大量的范围扫描操作。
总结

你的现象揭示了InnoDB在类型转换、索引操作和锁管理上的细节特性。这些细节不仅影响性能，还可能导致意外的锁范围扩展。通过明确SQL语句的类型匹配、优化查询条件和索引设计，可以有效避免这些问题，提高事务的并发性能和可靠性。

2. 执行和上步骤一样的内容, 但是事务B替换成INSERT看看效果

代码块
-- 1. 开启事务A, id用number
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.03 sec)

-- 2. 开启事务B
mysql> BEGIN;INSERT into article (id, `name`, words_num) VALUES ("6", "test6", 1);
Query OK, 0 rows affected (0.00 sec)
-- (卡住了)

-- 3.查看锁和事务
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              |
+-------------------------------------+-----------+--------------------+-------------+------------------------+
| 2676907383432:1073:2676913016600    | TABLE     | IX                 | GRANTED     | NULL                   |
| 2676907383432:11:4:1:2676896893976  | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |
| 2676907382656:1073:2676913015832    | TABLE     | IS                 | GRANTED     | NULL                   |
| 2676907382656:11:4:1:2676896890904  | RECORD    | S                  | GRANTED     | supremum pseudo-record |
| 2676907382656:11:4:2:2676896890904  | RECORD    | S                  | GRANTED     | '1'                    |
| 2676907382656:11:4:3:2676896890904  | RECORD    | S                  | GRANTED     | '110801'               |
| 2676907382656:11:4:4:2676896890904  | RECORD    | S                  | GRANTED     | '2'                    |
| 2676907382656:11:4:13:2676896890904 | RECORD    | S                  | GRANTED     | '4'                    |
| 2676907382656:11:4:14:2676896890904 | RECORD    | S                  | GRANTED     | '5'                    |
| 2676907382656:11:4:15:2676896890904 | RECORD    | S                  | GRANTED     | '3'                    |
+-------------------------------------+-----------+--------------------+-------------+------------------------+
10 rows in set (0.04 sec)

mysql> 
select trx_id,trx_isolation_level,trx_state,trx_requested_lock_id  from INNODB_TRX;
+-----------------+---------------------+-----------+------------------------------------+
| trx_id          | trx_isolation_level | trx_state | trx_requested_lock_id              |
+-----------------+---------------------+-----------+------------------------------------+
|           15432 | REPEATABLE READ     | LOCK WAIT | 2676907383432:11:4:1:2676896893976 |
| 284151884093312 | REPEATABLE READ     | RUNNING   | NULL                               |
+-----------------+---------------------+-----------+------------------------------------+
2 rows in set (0.04 sec)

-- 4. 提交事务A
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
-- 同时能看到事务B 
Query OK, 1 row affected (27.19 sec)

-- 5. 查看锁和事务
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              |
+------------------------------------+-----------+--------------------+-------------+------------------------+
| 2676907383432:1073:2676913016600   | TABLE     | IX                 | GRANTED     | NULL                   |
| 2676907383432:11:4:1:2676896893976 | RECORD    | X,INSERT_INTENTION | GRANTED     | supremum pseudo-record |
+------------------------------------+-----------+--------------------+-------------+------------------------+
2 rows in set (0.04 sec)

mysql> 
select trx_id,trx_isolation_level,trx_state,trx_requested_lock_id  from INNODB_TRX;
+--------+---------------------+-----------+-----------------------+
| trx_id | trx_isolation_level | trx_state | trx_requested_lock_id |
+--------+---------------------+-----------+-----------------------+
|  15432 | REPEATABLE READ     | RUNNING   | NULL                  |
+--------+---------------------+-----------+-----------------------+
1 row in set (0.04 sec)

-- 6. 在事务A窗口, 再开一个事务A1
 BEGIN;SELECT * FROM article WHERE id=3 LOCK IN SHARE MODE;
 -- (卡住了)
-- 6至7之间, 事后再次测试中, 增加一个状态的查询. 因为是**另外补充的**, 所以id不一样,不用管
mysql> 
select trx_id,trx_isolation_level,trx_state,trx_requested_lock_id  from INNODB_TRX;
+-----------------+---------------------+-----------+-------------------------------------+
| trx_id          | trx_isolation_level | trx_state | trx_requested_lock_id               |
+-----------------+---------------------+-----------+-------------------------------------+
|           15442 | REPEATABLE READ     | RUNNING   | NULL                                |
| 284151884093312 | REPEATABLE READ     | LOCK WAIT | 2676907382656:11:4:16:2676896891248 |
+-----------------+---------------------+-----------+-------------------------------------+
2 rows in set (0.04 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              |
+-------------------------------------+-----------+--------------------+-------------+------------------------+
| 2676907383432:1073:2676913016600    | TABLE     | IX                 | GRANTED     | NULL                   |
| 2676907383432:11:4:1:2676896893976  | RECORD    | X,INSERT_INTENTION | GRANTED     | supremum pseudo-record |
| 2676907383432:11:4:16:2676896894320 | RECORD    | X,REC_NOT_GAP      | GRANTED     | '6'                    |
| 2676907382656:1073:2676913015832    | TABLE     | IS                 | GRANTED     | NULL                   |
| 2676907382656:11:4:2:2676896890904  | RECORD    | S                  | GRANTED     | '1'                    |
| 2676907382656:11:4:3:2676896890904  | RECORD    | S                  | GRANTED     | '110801'               |
| 2676907382656:11:4:4:2676896890904  | RECORD    | S                  | GRANTED     | '2'                    |
| 2676907382656:11:4:13:2676896890904 | RECORD    | S                  | GRANTED     | '4'                    |
| 2676907382656:11:4:14:2676896890904 | RECORD    | S                  | GRANTED     | '5'                    |
| 2676907382656:11:4:15:2676896890904 | RECORD    | S                  | GRANTED     | '3'                    |
| 2676907382656:11:4:16:2676896891248 | RECORD    | S                  | WAITING     | '6'                    |
+-------------------------------------+-----------+--------------------+-------------+------------------------+
11 rows in set (0.04 sec)

 -- 7. 等待事务A1超时后, 看一下锁和事务的状态
 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     | IS                 | GRANTED     | NULL                   |
| 2676907382656:11:4:2:2676896890904  | RECORD    | S                  | GRANTED     | '1'                    |
| 2676907382656:11:4:3:2676896890904  | RECORD    | S                  | GRANTED     | '110801'               |
| 2676907382656:11:4:4:2676896890904  | RECORD    | S                  | GRANTED     | '2'                    |
| 2676907382656:11:4:13:2676896890904 | RECORD    | S                  | GRANTED     | '4'                    |
| 2676907382656:11:4:14:2676896890904 | RECORD    | S                  | GRANTED     | '5'                    |
| 2676907382656:11:4:15:2676896890904 | RECORD    | S                  | GRANTED     | '3'                    |
| 2676907383432:1073:2676913016600    | TABLE     | IX                 | GRANTED     | NULL                   |
| 2676907383432:11:4:1:2676896893976  | RECORD    | X,INSERT_INTENTION | GRANTED     | supremum pseudo-record |
| 2676907383432:11:4:16:2676896894320 | RECORD    | X,REC_NOT_GAP      | GRANTED     | '6'                    |
+-------------------------------------+-----------+--------------------+-------------+------------------------+
10 rows in set (0.04 sec)

mysql> 
select trx_id,trx_isolation_level,trx_state,trx_requested_lock_id  from INNODB_TRX;
+--------+---------------------+-----------+-----------------------+
| trx_id | trx_isolation_level | trx_state | trx_requested_lock_id |
+--------+---------------------+-----------+-----------------------+
|  15437 | REPEATABLE READ     | RUNNING   | NULL                  |
|  15432 | REPEATABLE READ     | RUNNING   | NULL                  |
+--------+---------------------+-----------+-----------------------+
2 rows in set (0.04 sec)

-- 8. 提交事务A1, 重新使用'3'再开启一个事务A2
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.02 sec)
-- 9. 此时锁和事务的状态
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              |
+-------------------------------------+-----------+--------------------+-------------+------------------------+
| 2676907383432:1073:2676913016600    | TABLE     | IX                 | GRANTED     | NULL                   |
| 2676907383432:11:4:1:2676896893976  | RECORD    | X,INSERT_INTENTION | GRANTED     | supremum pseudo-record |
| 2676907383432:11:4:16:2676896894320 | RECORD    | X,REC_NOT_GAP      | GRANTED     | '6'                    |
| 2676907382656:1073:2676913015832    | TABLE     | IS                 | GRANTED     | NULL                   |
| 2676907382656:11:4:15:2676896890904 | RECORD    | S,REC_NOT_GAP      | GRANTED     | '3'                    |
+-------------------------------------+-----------+--------------------+-------------+------------------------+
5 rows in set (0.04 sec)

mysql> 
select trx_id,trx_isolation_level,trx_state,trx_requested_lock_id  from INNODB_TRX;
+-----------------+---------------------+-----------+-----------------------+
| trx_id          | trx_isolation_level | trx_state | trx_requested_lock_id |
+-----------------+---------------------+-----------+-----------------------+
|           15432 | REPEATABLE READ     | RUNNING   | NULL                  |
| 284151884093312 | REPEATABLE READ     | RUNNING   | NULL                  |
+-----------------+---------------------+-----------+-----------------------+
2 rows in set (0.04 sec)

-- 10. 提交事务B, 然后再看看锁和事务状态
mysql> COMMIT;
Query OK, 0 rows affected (0.00 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 |
+-------------------------------------+-----------+---------------+-------------+-----------+
| 2676907382656:1073:2676913015832    | TABLE     | IS            | GRANTED     | NULL      |
| 2676907382656:11:4:15:2676896890904 | RECORD    | S,REC_NOT_GAP | GRANTED     | '3'       |
+-------------------------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.04 sec)

mysql> 
select trx_id,trx_isolation_level,trx_state,trx_requested_lock_id  from INNODB_TRX;
+-----------------+---------------------+-----------+-----------------------+
| trx_id          | trx_isolation_level | trx_state | trx_requested_lock_id |
+-----------------+---------------------+-----------+-----------------------+
| 284151884093312 | REPEATABLE READ     | RUNNING   | NULL                  |
+-----------------+---------------------+-----------+-----------------------+
1 row in set (0.04 sec)

--  再重新试一下, 事务B的insert. 在此之前, 所有事务都提交了,然后数据库中id是'6'的那一行数据已经删除了
 -- 1. 开事务B
 mysql> BEGIN;INSERT into article (id, `name`, words_num) VALUES ("6", "test6", 1);
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
 -- 2. 用事务C查询
 mysql>  BEGIN;SELECT * FROM article WHERE id="6" LOCK IN SHARE MODE;
 -- (会卡住)
 -- 3. 提交了事务C后, 再开启一个事务C1
 mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;SELECT * FROM article WHERE id="6";
Query OK, 0 rows affected (0.00 sec)

Empty set
现象
  1. 事务A 在id是varchar但是使用number写where时, 锁上了很多内容, 这会导致事务B如果要进行insert, 会卡住, 事务B在等supremum pseudo-record的锁
  2. 当事务B不提交时, 事务A1 错误使用where id= number的写法时,会多生成了一个id’6’的X锁(这个X锁是事务B的), 且事务A1至少先在等待的是id’6’的S锁.
  3. 事务A1虽然因为拿不到id’6’的S锁卡住, 并且最后超时了, 但是超时后, 其他S锁拿到了,而且不会丢掉.
  4. 当事务B不提交时, 事务C中针对id’6’加S锁查询时, 会卡住.
  5. 当事务B不提交时, 事务C1中针对id’6’不加锁查询, 不会卡住, 当然也查不到结果.
  6. 当事务B不提交时, 事务A2中针对id’3’'加S锁查询时,可以正常执行.
GPT总结
事务A1 等待 id=6 的共享锁（S锁），超时后仍能保留其他 S 锁

这是因为 MySQL 的锁机制允许事务部分获取资源。如果某些锁无法获取（如 id=6 的 S 锁），但其他资源已经成功加锁，这些锁会被保留。

  • 事务A1 使用了共享锁来查询 id=3，但由于锁冲突，未能成功获取 id=6 的 S 锁。
  • 超时后，事务会放弃等待，但已经获取的锁并不会被回滚。
INSERT操作默认行为
  • 在MySQL的InnoDB引擎中，当事务B执行INSERT语句时，会给新插入的记录（比如id=6）创建隐式锁。这种锁是短期的，通常在事务提交后释放。
  • 重点：事务B在插入记录时，并不会主动生成其他显式锁（如共享锁或排他锁），除非后续的操作要求。
锁机制与等待现象
  • 隐式锁：事务B插入id=6时生成的隐式锁是轻量级的，主要用于事务期间的并发控制。
  • 显式锁：其他事务如果尝试显式锁定id=6，MySQL需要确保事务B的修改（插入）已经完成并提交，才会让其他事务获得锁。

因此，当其他事务尝试显式锁定时，会出现“等待”事务B释放隐式锁的现象。

锁类型的转换
  • 当事务B插入记录后，没有立即生成显式锁。
  • 当其他事务需要对id=6进行排他锁（X锁）时，MySQL会：
    1. 先等待事务B的隐式锁释放（事务B提交或回滚）。
    2. 等到事务B的锁后, 生成事务A的排他锁（X锁）。

这个过程会显得像是“其他事务触发了锁”，但实际上是其他事务的操作使得锁冲突显现。

总结
  • 事务B的隐式锁不会主动变成显式锁，除非其他事务的操作引发锁需求。
  • 当其他事务尝试对id=6显式加锁时，它需要等待事务B释放对这条记录的隐式锁。
  • 这个等待与锁的生成是MySQL的正常行为，用于保证事务隔离级别（比如RR或SERIALIZABLE）。

标签：11,RECORD,lock,GRANTED,29,2024,trx,id
From： https://blog.csdn.net/qq_21410843/article/details/144147812

相关文章

  • NOIp 2024 考试策略
    无论简不简单，都要在前30min浏览所有题面，思考哪题可做、哪题不可做，思考能打哪些部分分，9:00再开始写T1。题目简单时9:00开写后，30min以内切完T1。9:30开T2，思考大概30min~45min后，如果一点进展都没有，那么选择冲暴力。如果还有思路，就再想想。在10:30时，必须开始写T2的......
  • 联想电脑更新Windows11系统后无法调节亮度？已解决！！！
    电脑型号：ThinkPadE490系列1.右键单击“此电脑”，打开设备管理器，可以看到显卡驱动有异常。（此方只能解决显卡驱动问题导致的无法调节亮度）2.打开联想服务官网（地址：http://support.lenovo.com.cn），选择“驱动和软件下载”3.输入主机编号后点击搜索。主机编号的获取方式有两种：其一......
  • 20222409 2024-2025-1 《网络与系统攻防技术》实验五实验报告
    1.实验内容1.1本周学习内容本周学习了信息收集与渗透测试相关技术，主要包括Metasploit、nmap和Nessus的使用。Metasploit是渗透测试框架，能提供多种攻击模块，如远程代码执行、提权和后渗透功能（如键盘记录、文件操作等），可用于模拟攻击和漏洞利用；nmap被用于扫描目标主机的端口和服......
  • [杂题]2024.9~2024.11 杂题总结
    [杂题]2024.9~2024.11杂题总结题目做多了，不总结，和没做是一样的。ARC061B挺好的一道题。观察到三个不好做，我们想能否搞成一个牌堆去取。发现显然是可以的，我们只需要知道一个确定的取出来牌的编号序列，必然可以确定三者的牌堆分别是什么。所以，问题转换成了：有多少个序列，当\(A\)......
  • NOIP2024游记——随风而去吧
    来不及来不及你曾笑着哭泣来不及来不及你颤抖的手臂来不及来不及无人将你打捞起来不及来不及你明明讨厌窒息没有想到一个月过的这么快，四个星期后又坐在机房里，而这一次——可能是真正意义上的最后一次踏入机房了，明天考完NOIP2024就是真正意义上的退役了。细细数来，学习......
  • springboot庀材二手房交易平台--41611（免费领源码）可做计算机毕业设计JAVA、PHP、爬虫、
    摘要在互联网飞速发展的当下，开发庀材二手房交易平台是为了利用互联网和科学技术来满足二手房交易市场日益增长的需求，提供一个方便快捷的平台供用户进行二手房交易。通过开发此系统，可以解决传统二手房交易中信息不透明、中介费高昂等问题，提供更便捷、透明、安全的交易环境。......
  • 【纯干货分享】计算机毕业设计必看必学36113+PHP 高校成绩管理系统原创的定制程序,单
     高校成绩管理系统的设计与实现摘 要随着互联网趋势的到来，各行各业都在考虑利用互联网将自己推广出去，最好方式就是建立自己的互联网系统，并对其进行维护和管理。在现实运用中，应用软件的工作规则和开发步骤，采用PHP技术建设高校成绩管理系统。本设计主要实现集人性化、高效......
  • 20222427 2024-2025-1 《网络与系统攻防技术》实验七实验报告
    1.实验内容1.1本周学习内容本周学习了有关Web安全的相关知识，复习了一些有关于Web的基础知识，比如：前、后端的定义，以及在前后端各自使用的语言，如：html、css、JS（前端）；C/C++、Python、Java、Go、Php（后端）等。学习了有关于数据库攻击的一些基本操作，如：SQL注入。1.2实验要求及内......
  • 统架构设计师2022年11月考点精讲（31-40题）
    统架构设计师2022年11月考点精讲（31-40题） ......
  • STM32单片机学习记录（11.29）
    一、STM32    6.4-PWM驱动程序        1.PWM基本结构：运行控制——时基单元——输出比较单元——GPIO。        2.PWM程序编写步骤：        （1）RCC开启时钟，将要用的TIM外设和GPIO外设的时钟打开；      ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99