Deadlocks and lock wait timeouts are independent of one another. A
deadlock happens when there is a cycle in the waits-for graph. Your
transactions are *active* for 132 and 33 seconds, but the deadlock
happens at the instant the conflict is detected, not after waiting. A
deadlock cannot be resolved by waiting, by definition. Hence the name,
deadlock. The only way to resolve it is to choose a victim.
On Fri, May 11, 2012 at 3:06 AM, Andrés Tello <mr.crip...@gmail.com> wrote:
> 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
--
Baron Schwartz
Author, High Performance MySQL
http://www.xaprb.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
