mysql主键更新被锁_mysql – 为什么更新主键锁定InnoDB中的唯一键？

有人可以解释以下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);

然后T1出于无关原因阻止应用程序代码.

T2

BEGIN;

UPDATE test3 SET p=9 WHERE p=10;

此时T2阻止尝试获取T1的锁定帮助.

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锁定主键记录(预期)

> X锁定辅助密钥记录,u = 10(预期)

>在u = 10之后锁定辅助密钥记录,在这种情况下u = 20(意外)

所以问题似乎是MySql在唯一索引中的下一条记录上获取额外的共享锁.有什么想法吗？

题

(以下文字并不完全准确,请参阅上面的更新)

为什么T2试图获取T1插入的索引记录20上的共享锁？似乎T2根本没有尝试触及该记录.我的理解是T1在20上进行了一次独占锁定,并且内部间隙锁定在(10,20)上. T2应该只对10进行独占锁定.

如果u上的索引不是唯一的,也不会发生这种情况.

MySQL版本是5.5.35-0ubuntu0.12.04.2,但我观察到与5.6相同的行为.

谢谢！