MySQL Bugs: #36988: Documentation on Next-Key Locking is confusing and misleading

Bug #36988	Documentation on Next-Key Locking is confusing and misleading
Submitted:	26 May 2008 18:34	Modified:	1 Dec 2008 20:42
Reporter:	Gordon Shannon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.24-rc-community-log	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	gap locking, next-key locks, phantom reads

Description:
The concept of "next-key locking" and "gap locking" are confusing. The term "next-key locking" is poorly defined in the manual, and is poorly named since the name implies it applies only to the "next key" after a locked record.  In my opinion all that is needed is "gap locking".

I think the problems can be summarized by examining the 2nd paragraph from the online documentation section 13.5.10.6 (5.1).

<quote>
The next-key locks that InnoDB sets on index records also affect the “gap” before that index record. If a user has a shared or exclusive lock on record R in an index, another user cannot insert a new index record immediately before R in the index order. (A gap lock refers to a lock that only locks a gap before some index record.)
</quote>

ISSUE 1. I can start a transaction in one session, cause a share lock on id 3, insert a row with id 2 in another session without waiting for txn 1 to commit; Thus, sentence #2 is wrong. (see "how to repeat")

ISSUE 2. The use of the phrase "immediately before" is wrong.  Rather, it effects the entire gap referred to in the where clause, not just the next record.  (see "how to repeat")

ISSUE 3. The last sentence says it locks a gap before some index record.  It can also lock a gap AFTER an index record, as in "where id > 100"

How to repeat:
ISSUE #1:

-- Session A:
set session tx_isolation='REPEATABLE-READ';
drop table if exists gs;
create table gs (id int not null primary key auto_increment, val int);
insert into gs(id,val) values (1,5),(5,8),(6,154);
start transaction;
select * from gs where id=5 lock in share mode;

-- Session B:

set session tx_isolation='REPEATABLE-READ';
-- 4 is immediately before 5, yet this does not block.
insert into gs values (4,1500);  
Query OK, 1 row affected (0.05 sec)

-- Session A:
rollback;

ISSUE #2:

-- Session A:
set session tx_isolation='REPEATABLE-READ';
drop table if exists gs;
create table gs (id int not null primary key auto_increment, val int);
insert into gs(id,val) values (1,5),(5,8),(6,154);
start transaction;
select * from gs where id < 100 lock in share mode;
+----+------+
| id | val  |
+----+------+
|  1 |    5 | 
|  5 |    8 | 
|  6 |  154 | 
+----+------+

-- Session B:

set session tx_isolation='REPEATABLE-READ';

-- 3 is *not* immediately before 5, but this still blocks...
insert into gs values (3,1500);  

-- Session A:
rollback;

session B:
Query OK, 1 row affected (12.13 sec)

Suggested fix:
It should state at the beginning that next-key locking is only applied when the query includes a range in the where clause. As is, it implies that next-key locking is always applicable, and the text about phantom reads appears to justify why the mechanism exists.  It should state clearly that none of this applies when the query does not use a range.

I think I understand gap locking.  Even after all this I still cannot define next-key locking as differentiated from gap locking.  They seem to be the same thing.

Thank you for the report.

I agree "gap" part is not clear from the manual.

According to "select * from gs where id < 100 lock in share mode;" part, there is example in the manual which starts from "select * from gs where id < 100 lock in share mode;"

So report is partially verified as documenatation bug.

Your table definition does not include ENGINE = InnoDB, so there will certainly be no blocking if the table is creating using the default engine (MyISAM). Was your environment such that InnoDB was the default table type?

Innodb is our default engine type.

Re: Issue #2:

"
-- Session B:

set session tx_isolation='REPEATABLE-READ';

-- 3 is *not* immediately before 5, but this still blocks...
insert into gs values (3,1500);
"

3 as a value does not immediately precede the value 5, but it's in the gap that immediately precedes 5 (i.e., the gap from 2 through 4). You should find similar blocking where you to insert 2 or 4.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Gap locks and next-key locks are not the same. A next-key lock is an index-record lock plus a gap lock before the index record. Please see:

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

(This is a new section that did not exist when you submitted this bug report.)

Issue 1: The behavior you see occurs because gap locking is used for locking ranges, not unique records.

Issue 2: re: "3 is *not* immediately before 5, but this still blocks": True, 3 is not immediately before 5, but the gap that includes 3 is. (The gap between 1 and 5 includes the values 2, 3, 4.)

Issue 3: The section quoted does actually say that the gap following the last index record can be locked. It's just not in the part you quoted. However, I've revised the section to make this more obvious. (At least, I hope it's more obvious. Please take a look and see.)