Hello Andrés did you notice that both transactions are trying to update records with same *accountid='3235296' * and that they lock the same index page? *space id 5806 page no 69100 n bits 176 index*
Cheers Claudio 2012/5/11 Andrés Tello <mr.crip...@gmail.com> > Ok, so I had a deadlock... > > But then, why a deadlock doesn't rollback all the transaccion? > > > On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz <ba...@xaprb.com> wrote: > > > 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 > > > > > -- Claudio