Ok... I have one of those pesky error, in an application not handling
deadlocks or lockwaits.
The database object can't be modified to support deadlock/lockwatis...
I can only change database parameteres
Database info: Server version: 5.5.22-log Source distribution
from show engine innodb status;
{abstract}
*** (1) TRANSACTION:
TRANSACTION 5F7EA9A, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 3112, 27 row lock(s)
update `account` set `balance`= 0.00 + '-6.07' where accountid='3235296'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7EA9A lock_mode X locks rec but not
gap waiting
*** (2) TRANSACTION:
TRANSACTION 5F7A398, ACTIVE 132 sec starting index read, thread declared
inside InnoDB 500
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3112, 27 row lock(s)
MySQL thread id 92442, OS thread handle 0x7f903b949710, query id 32378480
90.0.0.51 mario Updating
update `account` set `balance`= 0.00 + '-1.37' where accountid='3235296'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock mode S locks rec but not
gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5806 page no 69100 n bits 176 index `PRIMARY` of
table `f_database`.`account` trx id 5F7A398 lock_mode X locks rec but not
gap waiting
*** WE ROLL BACK TRANSACTION (1)
The issue is that I had a lock for over 132 seconds and the other was
waiting for 33 seconds, so I get a lockwait.
accountid is locked by a
select balance from account where accountid='3235296' lock in shared mode
How can I tell mysql to wait longer? I know the process which is doing the
deadlock, is a long balance process... I know that it takes time, sometives
over 15 minutes, but they always resolve...
How Can I tell mysql to wait for the lock as needed? like for over 12
minutes?
TIA
