InnoDB lock wait timeout

Following code cause an undetectable deadlock when run simultaneously. (Lock wait timeout exceeded;)

START TRANSACTION;SELECT id into tmp from mf_banners where id=192 FOR UPDATE;UPDATE mf_banners set is_active=is_active where id=192;COMMIT;

And I can’t understand why.
Transaction 1 takes X lock for record 192, then Transaction 2 try to take the same lock and can’t, so T2 waits for X lock. Then T1 tries to update record (It holds X lock for that record) and it can’t! Why???

I have serialized access with GET_LOCK and RELEASE_LOCK and that helped to avoid lock wait timeouts, but i’am not sure that they are replication safe. Are they replication safe, especially when used inside stored procedures?
Thank you for help.

Below is
show innodb status

------------TRANSACTIONS------------Trx id counter 0 12717377Purge done for trx’s n:o < 0 12682208 undo n:o < 0 0History list length 17Total number of lock structs in row lock hash table 2LIST OF TRANSACTIONS FOR EACH SESSION:—TRANSACTION 0 0, not started, OS thread id 564647936MySQL thread id 3697, query id 4767431 localhost remoteshow innodb status—TRANSACTION 0 0, not started, OS thread id 488327168MySQL thread id 3681, query id 4485076 remote—TRANSACTION 0 12679021, not started, OS thread id 564648448MySQL thread id 3656, query id 4062663 localhost remote—TRANSACTION 0 12664021, not started, OS thread id 563878400MySQL thread id 3655, query id 3852613 localhost remote—TRANSACTION 0 12717376, ACTIVE 11 sec, OS thread id 568848896 starting indexreadmysql tables in use 16, locked 16LOCK WAIT 2 lock struct(s), heap size 320MySQL thread id 3690, query id 4767429 localhost remote statisticsSELECT id into tmp from mf_banners where id=192 FOR UPDATE------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 720926 n bits 120 index PRIMARY of table banners2/mf_banners trx id 0 12717376 lock_mode X locks rec but not gap waitingRecord lock, heap no 19 PHYSICAL RECORD: n_fields 60; compact format;---------------------TRANSACTION 0 12717375, ACTIVE 11 sec, OS thread id 563876864mysql tables in use 16, locked 162 lock struct(s), heap size 320MySQL thread id 3691, query id 4767430 localhost remote Table lockupdate mf_banners set is_active=is_active where id=192--------FILE I/O--------I/O thread 0 state: waiting for i/o request (insert buffer thread)I/O thread 1 state: waiting for i/o request (log thread)I/O thread 2 state: waiting for i/o request (read thread)I/O thread 3 state: waiting for i/o request (write thread)Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0Pending flushes (fsync) log: 0; buffer pool: 02313 OS file reads, 152526 OS file writes, 14925 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.30 writes/s, 0.30 fsyncs/s-------------------------------------

Hmm, strange. Can you please also show us SHOW CREATE TABLE, and let us know MySQL version?

This problem was in MySQL 5.0.21 and disappeared when we installed 5.0.56.

CREATE TABLE mf_banners (
id int(11) NOT NULL auto_increment,
partner_id int(11) NOT NULL,
title varchar(127) NOT NULL default ‘??? ???’,
text varchar(255) character set utf8 NOT NULL,
href varchar(255) character set utf8 NOT NULL,
banner_type tinyint(1) NOT NULL COMMENT ‘0 - clicks 1 - shows’,
amount_per_day double NOT NULL default ‘0’,
amount_used_this_day double NOT NULL default ‘0’,
amount_last_update date NOT NULL,
total_used_amount double NOT NULL default ‘0’,
total_amount_limit double NOT NULL default ‘0’,
total_shows int(11) NOT NULL default ‘0’,
total_image_shows int(11) NOT NULL default ‘0’,
total_clicks int(11) NOT NULL default ‘0’,
koef float NOT NULL default ‘1’,
start_day date NOT NULL,
end_day date NOT NULL,
is_active tinyint(1) NOT NULL default ‘1’,
start_hour smallint(2) NOT NULL default ‘0’,
end_hour smallint(2) NOT NULL default ‘24’,
spread_daily_amount tinyint(1) NOT NULL default ‘0’,
phone_brand char(3) character set utf8 NOT NULL,
price double(5,5) NOT NULL default ‘0.00000’,
server tinyint(2) NOT NULL default ‘0’,
approved tinyint(1) NOT NULL default ‘1’,
approved_time datetime NOT NULL,
disapproved_comment text,
shows_block int(11) NOT NULL default ‘0’,
clicks_block int(11) NOT NULL default ‘0’,
speed_last_update datetime NOT NULL default ‘0000-00-00 00:00:00’,
speed_last_shows int(11) NOT NULL default ‘0’,
speed_value float NOT NULL default ‘0’,
tags varchar(100) NOT NULL,
dontblock tinyint(1) NOT NULL default ‘0’,
fresh_flag timestamp NOT NULL default CURRENT_TIMESTAMP,
delete_flag tinyint(1) NOT NULL default ‘0’,
non_adult tinyint(1) NOT NULL default ‘0’,
is_graphic tinyint(1) NOT NULL default ‘0’,
is_autoeconomy tinyint(1) NOT NULL default ‘0’,
max_clicks int(11) NOT NULL default ‘0’,
rnd_group_id int(11) NOT NULL default ‘0’,
date_activated date NOT NULL,
clickamount_per_day int(11) NOT NULL default ‘0’,
clickamount_used_this_day int(11) NOT NULL default ‘0’,
clickamount_last_update date NOT NULL default ‘0000-00-00’,
unique_shows int(11) NOT NULL default ‘0’,
unique_shows_with_cookies int(11) NOT NULL default ‘0’,
unique_clicks int(11) NOT NULL default ‘0’,
wap_card_id int(11) NOT NULL,
last_update datetime NOT NULL,
black_domains tinytext,
credit tinyint(1) NOT NULL default ‘0’,
system_flag tinyint(1) NOT NULL default ‘0’,
minute_div_flag tinyint(1) NOT NULL default ‘1’,
possible_shows int(11) NOT NULL default ‘0’,
very_possible_shows int(11) NOT NULL default ‘0’,
avg_koef_sum double NOT NULL default ‘0’,
avg_koef double NOT NULL default ‘0’,
KEY partner_id (partner_id),
KEY server (server),
KEY price (price),
KEY is_active (is_active)