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 62.118.198.231 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-------------------------------------