The genral log is the log that "logs" everything? humm... dunno if I can.. as always... stuuupid production server with no testing instance available...
And it happens very seldom, but force us to do a select (sum) from the movements table instead just a select balance from account... On Fri, May 11, 2012 at 1:53 PM, Claudio Nanni <claudio.na...@gmail.com>wrote: > Andrés, > > may be you can enable the general log, recreate the deadlock, > and attach the general log? > > If I had to reason as InnoDB, what I see is two updates statements that > arrive and want to update the same record, > I would be confused exactly as InnoDB is because I would not know which > update is the 'good' one, > I'd close my eyes and kill one. > This is a deadlock. > > Claudio > > 2012/5/11 Andrés Tello <mr.crip...@gmail.com> > >> Yup, but a far I understand... >> I made a >> >> select balance for update where accountid=3235296 lock in shared mode; >> >> over the same accountid , so the second transacion just would need to >> wait to the first transaccion to finish... >> >> That is why I'm confuse if I have a Deadlock o a wait lock... >> >> That is why I'm >> >> On Fri, May 11, 2012 at 1:36 PM, Claudio Nanni >> <claudio.na...@gmail.com>wrote: >> >>> 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 >>> >> >> > > > -- > Claudio >