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

Bug #47845	Delete without overlaped keys prevent insertion
Submitted:	6 Oct 8:59	Modified:	9 Oct 2:11
Reporter:	Meiji KIMURA
Status:	Open
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.0.x, 5.1.x, 5.4.x	OS:	Any
Assigned to:	Marko Mäkelä	Target Version:
Triage:	Needs Triage: D4 (Minor)

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.