有人能解释以下InnoDB锁定行为吗?它在READ_COMMITTED模式下运行,只有一个表,该表具有不重叠的主键和唯一密钥:
CREATE TABLE test3(
p BIGINT NOT NULL,
u BIGINT NOT NULL,
PRIMARY KEY (p),
UNIQUE KEY(u));
INSERT INTO test3 VALUES(10, 10);
有两个事务正在运行(T2稍后启动)。
T1
BEGIN;
INSERT INTO test3 VALUES(20, 20);
CCD_ 1然后由于不相关的原因而阻塞应用程序代码。
T2
BEGIN;
UPDATE test3 SET p=9 WHERE p=10;
此时,CCD_ 2阻止CCD_ 3尝试获取锁定帮助。
Innodb锁定信息
mysql> SELECT * FROM information_schema.innodb_trx G;
*************************** 1. row ***************************
trx_id: 158ABD
trx_state: LOCK WAIT
trx_started: 2014-04-23 03:26:43
trx_requested_lock_id: 158ABD:0:312:3
trx_wait_started: 2014-04-23 03:26:43
trx_weight: 6
trx_mysql_thread_id: 6749
trx_query: update test3 set p=9 where p=10
trx_operation_state: updating or deleting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 4
trx_lock_memory_bytes: 1248
trx_rows_locked: 3
trx_rows_modified: 2
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: 158AB8
trx_state: RUNNING
trx_started: 2014-04-23 03:25:28
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 6773
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> SELECT * FROM information_schema.innodb_locks;
+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
| 158ABD:0:312:3 | 158ABD | S | RECORD | `test`.`test3` | `u` | 0 | 312 | 3 | 20 |
| 158AB8:0:312:3 | 158AB8 | X | RECORD | `test`.`test3` | `u` | 0 | 312 | 3 | 20 |
+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 158ABD | 158ABD:0:312:3 | 158AB8 | 158AB8:0:312:3 |
+-------------------+-------------------+-----------------+------------------+
1 row in set (0.00 sec)
更多信息
我已经启用了InnoDB锁监视器,下面是它显示的内容:
---TRANSACTION 158BD1, ACTIVE 3 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 6817, OS thread handle 0x7f6cfd8ac700, query id 3255951 localhost 127.0.0.1 root Updating
update test3 set p=9 where p=10
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1672 n bits 72 index `u` of table `test`.`test3` trx id 158BD1 lock mode S locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000014; asc ;;
1: len 8; hex 8000000000000014; asc ;;
------------------
TABLE LOCK table `test`.`test3` trx id 158BD1 lock mode IX
RECORD LOCKS space id 0 page no 1670 n bits 72 index `PRIMARY` of table `test`.`test3` trx id 158BD1 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 8; hex 800000000000000a; asc ;;
1: len 6; hex 000000158bd1; asc ;;
2: len 7; hex 13000005bf27ab; asc ' ;;
3: len 8; hex 800000000000000a; asc ;;
RECORD LOCKS space id 0 page no 1672 n bits 72 index `u` of table `test`.`test3` trx id 158BD1 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 8; hex 800000000000000a; asc ;;
1: len 8; hex 800000000000000a; asc ;;
RECORD LOCKS space id 0 page no 1672 n bits 72 index `u` of table `test`.`test3` trx id 158BD1 lock mode S locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000014; asc ;;
1: len 8; hex 8000000000000014; asc ;;
---TRANSACTION 158BCE, ACTIVE 148 sec
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 6810, OS thread handle 0x7f6cfd82a700, query id 3255952 localhost 127.0.0.1 root
show engine innodb status
TABLE LOCK table `test`.`test3` trx id 158BCE lock mode IX
RECORD LOCKS space id 0 page no 1672 n bits 72 index `u` of table `test`.`test3` trx id 158BCE lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000014; asc ;;
1: len 8; hex 8000000000000014; asc ;;
看起来T1不采取任何间隙锁定,并且只锁定插入的记录。这是在READ-COMMITTED隔离级别下预期的。另一方面,T2有三个锁:
- 主键记录上的X锁(预期)
- u=10时的X锁定辅助密钥记录(预期)
- 在u=10之后的第二密钥记录上锁定S,在这种情况下,u=20(意外)
因此,问题似乎是MySql对唯一索引中的下一条记录使用了额外的共享锁。有什么想法吗?
问题
(下面的文本并不完全准确,请参阅上面的更新)
为什么T2试图获得T1插入的u
中的索引记录20的共享锁?T2似乎根本没有试图打破这一纪录。我的理解是,T1在20上取独占锁定,在(10,20)上取强度间隙锁定。T2应该只对10执行独占锁定。
如果u
上的索引不是唯一的,也不会发生这种情况。
MySQL版本是5.5.35-0ubuntu0.12.04.2
,但我在5.6中观察到了相同的行为。
谢谢!
INSERT语句似乎实现了为[10,20]而不是(10,20]设置的插入意图间隙锁定;而其他INSERT语句仍然允许在没有锁定的情况下进行,UPDATE语句被锁定。
为了说明这一点,在开始任何事务之前,插入另一条记录:
INSERT INTO test3 VALUES (11, 11);
现在,当您播放两个事务时,UPDATE语句将不会锁定,因为间隙锁定现在设置为[11,20],而下一个密钥锁定为10。
类似地,当两个事务都执行INSERT语句时,两个事务在不锁定的情况下执行:
A: BEGIN;
A: INSERT INTO test3 VALUES (20, 20);
B: BEGIN;
B: INSERT INTO test3 VALUES (15, 15);
然而
看来,如果唯一唯一的约束是主键,那么上述问题根本不是问题;对我来说唯一有意义的原因是UNIQUE约束对主键有依赖性,这在某种程度上使MySQL失控。