MySQL Bugs: #37357: REPLACE ... SELECT puts share and gap locks on selected rows, INSERT does not

Bug #37357	REPLACE ... SELECT puts share and gap locks on selected rows, INSERT does not
Submitted:	11 Jun 2008 23:28	Modified:	12 Aug 2008 17:29
Reporter:	Gordon Shannon	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.1.24-rc-community-log	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	replace insert read-committed lock

Description:
When running in READ-COMMITTED isolation mode, doing a REPLACE INTO .. SELECT is putting the same gap locks and S locks on the rows selected as it does in REPEATABLE-READ.  INSERT INTO .. SELECT does not put require these locks, and I expected REPLACE to behave the same way.  

The document page 13.10.5.8, http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html, states that "REPLACE is done like an INSERT if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row that has to be updated."  Note it says nothing about the selected rows.

If you repeat the test case I provide and change "replace" to "insert", the statements in session 2 will not block.  This is the behavior I expect and need with "replace" as well.

How to repeat:
-- SESSION 1:

create table gs01 ( id int not null, val int, primary key(id)) engine=innodb;

insert into gs01 values (1,10),(3,30);

create table gs02 like gs01;

set session transaction isolation level read committed;

begin;
replace into gs02 select * from gs01 where id between 1 and 3;

-- SESSION 2

-- either of these 2 statements will block, waiting on locks from session 1:
delete from gs01 where id=1;
insert into gs01 values (2,20);

Suggested fix:
Change "replace" to behave like "insert" with respect to locks on the selected rows in a REPLACE INTO ... SELECT statement, when isolation level is set to READ-COMMITTED.  In other words, it should request no gap locks or S locks on the selected rows.

Thank you for a problem report. Please, send the resultd of:

show variables like 'innodb_locks%';

from your server.

show variables like 'innodb_locks%';

+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   | 
+--------------------------------+-------+
1 row in set (0.01 sec)

Verified as described by using MySQL 5.1 bzr tree.

This is InnoDB related because NDB is working correct as expected.

This was brought up also by another customer a few weeks ago. I agree that it would be logical to drop the locking from the SELECT table in this case. It could be just like INSERT ... SELECT ...

Assigning this feature request to Inaam.

Duplicate of http://bugs.mysql.com/bug.php?id=37232

Marking as duplicate according last comment from Inaam Rana.