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.
why are there two record lock rather than next key lock in this report?
73757072656d756dmean? I found this hex in lots of Stack Overflow question.
supremummean? the supremum of B+ leaf node?
What does these lines mean?