Why Next-Key lock is called this way?

Question

As I understand (basing on official docs), Next-Key lock is an index record lock + the gap lock BEFORE that index record (preceding gap).

So I'm curious why is it called NEXT-Key lock? What 'key' is meant here and why 'next'?

Answer 1

In this context, key means one entry in an index. So one can say, "a key is locked" which means some session holds a lock on an entry in the index.

A next-key lock is acquired by index searches or scans.

UPDATE mytable WHERE id > 18;

Suppose there are actually values in the index 10, 11, 13, and 20 (like in the example in that manual). The UPDATE shown above would lock the entry for 20, and the gap before 20, because it's an index scan.

Then your session tries to insert into the gap:

INSERT INTO mytable (id) VALUES (19);

This conflicts with the gap-lock part of the next-key lock.

Think of it this way: you can't get the lock on 19, because some other session already has locked the gap that includes the value 19 you want to insert, as well as 20, which is the next key after 19 that really exists in the index.