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

[11 Jun 2008 23:28] Gordon Shannon
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.
[12 Jun 2008 5:24] Valeriy Kravchuk
Thank you for a problem report. Please, send the resultd of:

show variables like 'innodb_locks%';

from your server.
[12 Jun 2008 14:04] Gordon Shannon
show variables like 'innodb_locks%';

+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   | 
+--------------------------------+-------+
1 row in set (0.01 sec)
[13 Jun 2008 15:21] Susanne Ebrecht
Verified as described by using MySQL 5.1 bzr tree.

This is InnoDB related because NDB is working correct as expected.
[17 Jun 2008 17:23] Heikki Tuuri
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.
[12 Aug 2008 17:08] Inaam Rana
Duplicate of http://bugs.mysql.com/bug.php?id=37232
[12 Aug 2008 17:29] MySQL Verification Team
Marking as duplicate according last comment from Inaam Rana.