MySQL Bugs: #47845: Delete without overlaped keys prevent insertion

Bug #47845	Delete without overlaped keys prevent insertion
Submitted:	6 Oct 2009 6:59	Modified:	24 Nov 2016 14:41
Reporter:	Meiji KIMURA	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.x, 5.1.x, 5.4.x	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have an InnoDB table have 140000-row.

I have two transaction for that table.

Transaction A deletes 10000-row and not commit for a while within one transaction.
Transaction B deletes 100-row and insert 100-row with same keys in autocommit mode. These key are not overlapped with keys of Transaction B.

A's keys and B's keys are not overlapped, so they can work well without locking.
But unser some conditions, Transaction A's row-lock expanded additional 2 row-lock(10002->10004). It prevent B's insertion.

How to repeat:
(1) Prepare a table with primary key, and insert 140000-row.
(2) Transaction A try to delete 10000-row. It makes 10002-row locking.
(3) Transaction B deletes 100-row and insert 100-row with same keys in autocommit mode. If I repeate this procedure, A's row-locking expanded from 10002 to 10004.
It prevent Transaction B from inserting.

Suggested fix:
[Workaround] 
(1) Commit often in Transaction A.
(2) innodb-locks-unsafe-for-binlog=1

[Suggested fix]
It should not be blocked.

this looks like a feature related to gap locks, still assigning for a closer look.

I do not know the exact setting in this bug report. Does EXPLAIN tell that MySQL is actually using the assumed access path to the table?

It is quite normal for InnoDB record and gap locks to spill over the range defined in the WHERE clause. MySQL typically specifies the range to the engine like this:

"fetch all records in an index where index_value >= 12345"

It does NOT specify the last record in the range. When InnoDB fetches the first record that is NOT in the range, InnoDB has to lock that record and the gap before it.

Also, only physical records in an index cvan carry lock information. If we have records 1 and 100, and the user wants to lock all records in the range [1, 50), then InnoDB locks the whole 'gap' between 1 and 100.

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".