Bug #38149 Lock wait timeout if REPLACE and UPDATE runs concurrently
Submitted: 15 Jul 2008 19:51 Modified: 13 May 2010 16:12
Reporter: Sveta Smirnova Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.0, 5.1, 6.0 bzr OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: Contribution
Triage: Triaged: D5 (Feature request)

[15 Jul 2008 19:51] Sveta Smirnova
Description:
REPLACE blocks UPDATE queries even if innodb-locks-unsafe-for-binlog=1

How to repeat:
$cat bug-master.opt
--innodb-locks-unsafe-for-binlog=1 --transaction-isolation=REPEATABLE-READ

$cat bug.test
--source include/have_innodb.inc

create table t1 (id int auto_increment primary key, f1 int) engine=innodb;
insert into t1 (f1) values (1), (2), (3);
create table t2 like t1;
insert into t2 select * from t1;
create table t3 like t1;
begin;
replace into t3 select t1.id, t1.f1 from t1 join t2 using (f1);

connect (addconroot, localhost, root,,);
connection addconroot;

begin;

--error 1205
update t1 set f1=4 where f1=2;
exit

Suggested fix:
Change function ha_innobase::store_lock() in ha_innodb.cc from:

if (srv_locks_unsafe_for_binlog &&
trx->isolation_level != TRX_ISO_SERIALIZABLE &&
(lock_type == TL_READ || lock_type == TL_READ_NO_INSERT) &&
(thd->lex->sql_command == SQLCOM_INSERT_SELECT ||
thd->lex->sql_command == SQLCOM_UPDATE ||
thd->lex->sql_command == SQLCOM_CREATE_TABLE)) {

to:

if (srv_locks_unsafe_for_binlog &&
trx->isolation_level != TRX_ISO_SERIALIZABLE &&
(lock_type == TL_READ || lock_type == TL_READ_NO_INSERT) &&
(thd->lex->sql_command == SQLCOM_INSERT_SELECT ||
thd->lex->sql_command == SQLCOM_REPLACE_SELECT ||
thd->lex->sql_command == SQLCOM_UPDATE ||
thd->lex->sql_command == SQLCOM_CREATE_TABLE)) {