List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:May 11 2012 6:53pm
Subject:Re: Deadlock due lockwait. How can I tell mysql to wait longer?
View as plain text  
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.criptos@stripped>

> 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.nanni@stripped>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.criptos@stripped>
>>
>>> 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 <baron@stripped>
> 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.criptos@stripped>
>>> > 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

Thread
Deadlock due lockwait. How can I tell mysql to wait longer?Andrés Tello11 May
  • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Baron Schwartz11 May
    • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Andrés Tello11 May
      • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Claudio Nanni11 May
        • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Andrés Tello11 May
          • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Claudio Nanni11 May
            • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Andrés Tello11 May
      • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Baron Schwartz11 May
        • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Andrés Tello11 May
        • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Johan De Meersman14 May
          • RE: Deadlock due lockwait. How can I tell mysql to wait longer?David Lerer14 May
Re: Deadlock due lockwait. How can I tell mysql to wait longer?Baron Schwartz14 May
  • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Claudio Nanni14 May
    • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Andrés Tello14 May
      • Re: Deadlock due lockwait. How can I tell mysql to wait longer?Claudio Nanni14 May
        • RE: Deadlock due lockwait. How can I tell mysql to wait longer?Rick James14 May