Next-Key Locks from MySQL documentation

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. A next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

Show InnoDB engine status

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

MySQL documentation also provide an example to explain what does Show engine InnoDB status output looks likes.

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

The strange thing is there is no next key lock appears in this output, instead, two record locks.

My Questions

  1. why are there two record lock rather than next key lock in this report?

    enter image description here

  2. What does 73757072656d756d mean? I found this hex in lots of Stack Overflow question.

  3. What does supremum mean? the supremum of B+ leaf node?

  4. What does these lines mean?

enter image description here

  • Can we see the queries and SHOW CREATE TABLE, so we can provide some context? – Rick James Apr 23 at 3:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.