MySQL Bugs: #62021: Documentation incorrectly specifies that gap locking is used in READ COMMITTED

Bug #62021	Documentation incorrectly specifies that gap locking is used in READ COMMITTED
Submitted:	28 Jul 2011 19:55	Modified:	13 May 2013 21:23
Reporter:	Kevin Modzelewski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
The MySQL reference manual states that in READ COMMITTED:
"For range-type searches [for updates], InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range."
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed

It then immediately contradicts itself and says:
"[in READ COMMITTED] there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking"

You can test this yourself by doing something like "BEGIN; DELETE FROM test WHERE id>0" (no commit) and then "BEGIN; INSERT INTO TEST VALUES (10,10)"; in READ COMMITTED the INSERT proceeds immediately, and in REPEATABLE READ it blocks until the first transaction is committed or rolled back.  This means that the first statement is incorrect and the second is correct.

This behavior is also in agreement with other parts of the manual.  It seems like this part of the documentation was incorrectly copy-pasted from REPEATABLE READ.

How to repeat:
Look at http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed and read it.

To verify the incorrectness of the documentation, you can do something like the following:

Database set up:
Set global transaction level to READ COMMITTED
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `v` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

shell 1:
BEGIN
DELETE FROM test WHERE id > 0;

shell 2:
BEGIN
INSERT INTO test VALUES(10,10)
COMMIT

T2 proceeds to completion without blocking (thus, no gap locks).

Change transaction isolation mode to REPEATABLE READ, and repeat the experiment; now T2 blocks at the INSERT statement.

Suggested fix:
On this page of the manual:
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read-committed

Replace this paragraph:
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records. For UPDATE and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition (such as WHERE id = 100), or a range-type search condition (such as WHERE id > 100). For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For range-type searches, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range. This is necessary because “phantom rows” must be blocked for MySQL replication and recovery to work.

With something like the following:
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.

Upon further testing, I've noticed that an range-UPDATE query run in REPEATABLE READ mode will block inserts from READ COMMITTED or REPEATABLE READ, whereas a range-UPDATE query run in READ COMMITTED with not block inserts from READ COMMITTED or REPEATABLE READ.

I'm not sure how to reconcile this with either statement: that (in READ COMMITTED) gap locking is used during range-UPDATEs (seems untrue because a RC UPDATE doesn't block a RC INSERT), and that no gap locking occurs at all (seems untrue because a RR UPDATE *will* block a RC INSERT).

Manual actually says:

"In MySQL 5.1, if the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking."

So, it explains *when* "there is no gap locking" with READ COMMITTED isolation level (not *by default*). I see no clear contradiction.

Hmm, am I reading this wrong?  To me it says

(isolation is READ COMMITTED) OR (innodb_locks_unsafe_for_binlog==1) => no gap locking.

This would imply that when in READ COMMITTED, there is never gap locking.  I'm not sure how else to define "by default", so I don't quite understand how that could be construed to be noncontradictory.

Sorry, my fault. 

Indeed, that manual page needs correction.

Also READ UNCOMMITTED should not take gap locks.

I'm revisiting some doc bugs that got pushed down in priority due to 5.6 development work. This is a particularly tricky one because of all the back-and-forth discussion. Going back to the dev team for some final rulings.

Revised documentation to remove content that incorrectly states that gap
locking is used by Read Committed. Applied revision to 5.1, 5.5, 5.6, 5.7 reference manual.