MySQL Bugs: #43160: InnoDB uses X locks when S locks would work in DML subqueries

Bug #43160	InnoDB uses X locks when S locks would work in DML subqueries
Submitted:	24 Feb 2009 21:45	Modified:	27 Apr 2009 15:10
Reporter:	Harrison Fisk	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.0.72, 5.1.31	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	innodb, locking, subqueries

Description:
With a subquery in the form of:

DELETE FROM t1 WHERE c1 IN (SELECT c2 FROM t2);

The rows examined in t2 will have an exclusive lock put on them.  Really, they only need a shared lock put on them.

Also, innodb_locks_unsafe_for_binlog or read committed in 5.1 could not lock them all.

How to repeat:
create table ib1 (id int, primary key (id)) engine=innodb;
create table ib2 (id int, primary key (id)) engine=innodb;
INSERT INTO ib1 values (1), (2), (3), (4), (5);
INSERT INTO ib2 values (1), (2), (3), (4), (5);

On first connection:
BEGIN;
SELECT * FROM ib1 LOCK IN SHARE MODE;

On second connection:

BEGIN;
DELETE FROM ib2 WHERE id IN (SELECT id FROM ib1);

This delete will block since it is trying to set an exclusive lock on ib1 while a shared lock would be fine.

Suggested fix:
Multi-table DML statements seem to get this correct, so re-writing as a join should help reduce locking issues:

DELETE FROM ib2 USING ib1, ib2 WHERE ib1.id=ib2.id;

Otherwise, fix it to lock less.

Marko, could you have a look?

This could also fix Bug #39320.

I would say that this is a bug in the locking code of the MySQL server. get_lock_data() in lock.cc correctly locks table ib2 in TL_WRITE mode, but after that, it will incorrectly try to lock ib1 in TL_WRITE mode as well, even though a read lock would suffice. Storage engines should ever have any business of downgrading write locks to read locks. Hence, this bug must be fixed in the core of MySQL.

Possibly related to Bug#39843 ?

To clarify status of this bug:

#1 When tested, this bug is no longer present in 5.1.45 - the delete in connection 2 will no longer block.
#2 Based on such testing, I am assuming this is indeed a duplicate of bug #39320
#3 Queries can also be re-written:
   DELETE FROM ib2 WHERE id IN (SELECT id FROM ib1);
   to:
   DELETE FROM ib2 USING ib1, ib2 WHERE ib1.id=ib2.id;