MySQL Bugs: #85749: Manual does NOT explain gap locks set by SELECT ... FOR UPDATE

Bug #85749	Manual does NOT explain gap locks set by SELECT ... FOR UPDATE
Submitted:	31 Mar 2017 16:45	Modified:	15 May 2018 18:45
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	locks, missing manual, SELECT ... FOR UPDATE

Description:
Manual (https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html) says:

"For SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause)."

It seems to be NOT the case when non-unique secondary index is used, see recent test case and comments in Bug #85286. We execute:

select * from t where c = 5 for update;

and get:

mysql> show engine innodb status\G
...
------------
TRANSACTIONS
------------
Trx id counter 2328
Purge done for trx's n:o < 2324 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422168432626424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2327, ACTIVE 26 sec
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 3, OS thread handle 140693130499840, query id 36 localhost root
TABLE LOCK table `test`.`t` trx id 2327 lock mode IX
RECORD LOCKS space id 27 page no 4 n bits 80 index idx_c of table `test`.`t` trx id 2327 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `test`.`t` trx id 2327 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000000909; asc       ;;
 2: len 7; hex a90000011d011c; asc        ;;
 3: len 4; hex 80000005; asc     ;;

RECORD LOCKS space id 27 page no 4 n bits 80 index idx_c of table `test`.`t` trx id 2327 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 80000003; asc     ;;

So, we have a next-key lock on the secondary index record with value 5 (expected), primary key lock on record with value 2 (surely, we locked (2,5), not any gap). 

But why is this third gap lock on the secondary index record with value 3 (that is, row (3,10))? We checked next record while looking for 5 in the idx_c, we found it has value 10, so does not meet the criterion, and we released the lock (porbably), but NOT the gap lock before it.

As a result, we can not insert row with value, say, 8, in column c from the concurrent session.

How to repeat:
In one session:

set global innodb_status_output=ON;
set global innodb_status_output_locks=ON;

create table t (id int(11) primary key, c int(11), key idx_c (c));`
insert into t values (1, 1), (2, 5), (3, 10), (4, 20);
start transaction;
select * from t where c = 5 for update;

Then in another session try to explain the locks you see from show engine innodb status.

Try to run from the other session:

insert into t values(6.8);

and see it blocked because of the gap lock remains before the record for the value 10.

Suggested fix:
If everything works by design, please, clarify in the manual that gap locks are NOT released.

If manual quoted is right, fix InnoDB locking.

Hi!

I have run your test case and got the same results as you have.

Upon further analysis, I concluded that this is a bug.  A small bug , but a bug.

Verified.

Sorry, but I had to change synopsis and reclassify the bug as documentation request based on further debugging and code review.

No locks are released in this case, but we do request X lock on the gap before the next, non-matching record when non-unique secondary index is used. Check code starting from this line (https://github.com/mysql/mysql-server/blob/71f48ab393bce80a59e5a2e498cd1f46f6b43f9a/storag...):

			/* Try to place a gap lock on the next index record
			to prevent phantoms in ORDER BY ... DESC queries */
			const rec_t*	next_rec = page_rec_get_next_const(rec);

			offsets = rec_get_offsets(next_rec, index, offsets,
						  ULINT_UNDEFINED, &heap);
			err = sel_set_rec_lock(pcur,
					       next_rec, index, offsets,
					       prebuilt->select_lock_type,
LOCK_GAP, thr, &mtr);

in row_search_mvcc(). See the (potential) reason to set this gap lock in the comment above.

Maybe there is another reason for the behavior we see. Then it should be also documented.

Valerii,

Thank you for finding that comment.

We shall see that this is documented fully.

Posted by developer:
 
The reference documentation has been updated.
https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

The changes should appear online soon.

"For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and
DELETE statements, the locks that are taken depend on whether the
statement uses a unique index with a unique search condition, or a
range-type search condition. 

For a unique index with a unique search
condition, InnoDB locks only the index record found, not the gap before
it. 

For other search conditions, and for non-unique indexes, InnoDB locks
the index range scanned, using gap locks or next-key locks to block
insertions by other sessions into the gaps covered by the range."

Thank you for the bug report.