MySQL Bugs: #77009: SELECT FOR UPDATE write locks on gaps are not exclusive

Bug #77009	SELECT FOR UPDATE write locks on gaps are not exclusive
Submitted:	12 May 2015 3:20	Modified:	10 May 2018 13:51
Reporter:	Donald Ball	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	locking gaps index

Description:
When SELECT FOR UPDATE obtains write locks on index values containing rows, concurrent transactions are blocking from obtaining their own write locks on the same index values. When SELECT FOR UPDATE obtains write locks on index values that do not contains rows (gaps), concurrent transactions are not blocked.

This leads to deadlocks in this common transactional scenario:

BEGIN;
SELECT * FROM users WHERE external_id = ? FOR UPDATE;
INSERT INTO users (external_id) VALUES (?);
COMMIT;

Concurrent transactions that attempt to insert values within the same gap, e.g. beyond the range of the extant index, will deadlock.

How to repeat:
CREATE TABLE users (id int NOT NULL UNIQUE KEY);
INSERT INTO users (id) VALUES (1), (10);

T1:
BEGIN;
SELECT * FROM users WHERE id = 4 FOR UPDATE;

T2:
BEGIN;
SELECT * FROM users WHERE id = 5 FOR UPDATE;

T1:
INSERT INTO users (id) VALUES (4); this will block waiting for T2 to release its gap lock

T2:
INSERT INTO users (id) VALUES (5); this will trigger the deadlock

Suggested fix:
I fail to see why write locks on index values that do not contain rows should be treated differently than write locks on index values that do contain rows. It seems to me they both should be exclusive, and block other transactions from acquiring them until released.

Looks like duplicate of: https://bugs.mysql.com/bug.php?id=72439.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".