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
• Status: Verified
• 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

[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)) {