MySQL Bugs: #97110: Record's Partition Update Takes Gap Lock

Bug #97110	Record's Partition Update Takes Gap Lock
Submitted:	5 Oct 2019 12:00	Modified:	10 Oct 2019 11:01
Reporter:	Digvijay Singh	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.33	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Consider the following table:

CREATE TABLE `attributes` (
  `id` varchar(24) NOT NULL,
  `dead` int(11) NOT NULL,
  PRIMARY KEY (`id`,`dead`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY LIST (dead)
(PARTITION pa VALUES IN (0) ENGINE = InnoDB,
 PARTITION pd VALUES IN (1) ENGINE = InnoDB) */

On updating a record's dead value, it moves across partitions. When updating the record using subset of the columns of the primary key (everything excluding the partitioned column) in the WHERE clause - a gap lock is being acquired.

As per this https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html in the INSERT section the insert does not take a gap lock.

How to repeat:
Create table using the command:
CREATE TABLE `attributes` (
  `id` varchar(24) NOT NULL,
  `dead` int(11) NOT NULL,
  PRIMARY KEY (`id`,`dead`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY LIST (dead)
(PARTITION pa VALUES IN (0) ENGINE = InnoDB,
 PARTITION pd VALUES IN (1) ENGINE = InnoDB) */

Create Seed Data:
INSERT INTO `attributes` (`id`, `dead`)
VALUES
	('1', 1),
	('2', 0),
	('3', 0),
	('4', 1);

In Session 1:
BEGIN;
UPDATE `attributes` SET `dead`=1 WHERE `id`='2';

In Session 2:
BEGIN;
UPDATE `attributes` SET `dead`=1 WHERE `id`='3';

Session 2 starts waiting for the first transaction to complete.

Suggested fix:
Expected behavior on the basis of the documentation is that no gap locks should be taken and both inserts into the new partition should be allowed to go through.

However in case there is some special handling in case of partitions then that needs to be added to the document.

Hi Mr. Singh,

Thank you for your bug report.

However, this is not a bug. When UPDATE moves a row from one partition to another, it effectively does an INSERT to the new partition, which has to  take a gap lock.

Not a bug.

Hi Sinisa Milivojevic,
Thanks for the quick response. In the usual case of inserting a row in a partition/table the documentation states:
"INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row."

And that is also the case in practice, where no gap locks are taken. Can you please throw some clarity on why the gap lock is needed in this case? If the gap lock is not needed to be taken then we can check behavior in latest versions/raise an enhancement request for the same. If it is needed then we can have the behavior documented for reference?

Can the status of the ticket be open till the above clarifications are brought?

Thanks,
Digvijay

Hi,

Please, do read carefully our Reference Manual. Among other gap locks, there is one named insert-intention lock.

Right gap locks are taken but as mentioned in the documentation the gap locks don't block concurrent transactions from inserting within the same gap:

"This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap"