Why does update of primary key lock on unique key in InnoDB?

Question

Could someone explain the following InnoDB locking behavior? It is running in READ_COMMITTED mode with the only table, which has non-overlapping primary key and unique key:

CREATE TABLE test3(
  p BIGINT NOT NULL,
  u BIGINT NOT NULL,
  PRIMARY KEY (p),
  UNIQUE KEY(u));

INSERT INTO test3 VALUES(10, 10);

There are two transactions running (T2 is started later).

T1

BEGIN;
  INSERT INTO test3 VALUES(20, 20);

T1 then blocks in application code for unrelated reasons.

T2

BEGIN;
  UPDATE test3 SET p=9 WHERE p=10;

At this point T2 blocks trying to acquire a lock help by T1.

Innodb lock info

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)

More info

I've enabled InnoDB lock monitor and here is what it shows:

---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         ;;

It looks like T1 does not take any gap locks and only locks the inserted record. This is expected under READ-COMMITTED isolation level. T2 on the other hand, takes three locks:

• X lock on a primary key record (expected)
• X lock on a secondary key record for u=10 (expected)
• S lock on a secondary key record following u=10, which is in this case u=20 (unexpected)

So the problem seems to be that MySql takes an extra shared lock on a next record in a unique index. Any ideas why?

Question

(The text below is not entirely accurate, see update above)

Why is T2 trying to obtain a shared lock on an index record 20 in u that T1 inserted? It doesn't seem like T2 is trying to touch that record at all. My understanding is that T1 takes an exclusive lock on 20 and a intension gap lock on (10, 20). T2 should only take an exclusive lock on 10.

This also does not happen if the index on u is not unique.

MySQL version is 5.5.35-0ubuntu0.12.04.2 but I observe the same behavior with 5.6.

Thanks!

Answer 1

The INSERT statement seems to effect an insertion intention gap lock to be set for [10, 20] instead of (10, 20]; and whereas other INSERT statements are still allowed to take place without locking, UPDATE statements are locked.

To illustrate this, before any transactions are started, insert another record:

INSERT INTO test3 VALUES (11, 11);

Now, when you play both transactions the UPDATE statement will not lock, because the gap lock is now set at [11, 20] whereas the next-key lock is at 10.

Similarly, when both transactions perform an INSERT statement, both transactions are executed without locking:

A: BEGIN;
A: INSERT INTO test3 VALUES (20, 20);
B: BEGIN;
B: INSERT INTO test3 VALUES (15, 15);

However

It seems that if the only unique constraint is the primary key, the above isn't an issue at all; the only reason that would make sense to me is that the UNIQUE constraint has a dependency on the primary key and this somehow makes MySQL trip over itself.