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
>
