List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:May 14 2012 2:47pm
Subject:Re: Deadlock due lockwait. How can I tell mysql to wait longer?
View as plain text  
Argh. I meant to send this to the list but it doesn't have the
reply-to set as I expect... <the usual gripe>

On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz <baron@stripped> wrote:
> Johan,
>
> On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman <vegivamp@stripped> wrote:
>> What I fail to understand, Baron, is how there can be a deadlock here - both
> transactions seem to be hanging on a single-table, single-row update statement. Shouldn't
> the oldest transaction already have acquired the lock by the time the youngest came
> around; and shouldn't the youngest simply wait until the eldest finished it's update?
>
> Take a look at the output again:
>
> ======================== 8< ===============================
>
> *** (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)
>
>
> ======================== 8< ===============================
>
> Here is how to interpret that: Transaction 1 has locked 27 rows (not
> just a single row!) and is waiting for an exclusive lock on some row.
> Transaction 2 holds a shared lock on that same row and is trying to
> upgraded its shared lock to an exclusive lock.
>
> Both transactions have locked 27 rows, so this is not a single-row,
> single-table problem. It may be the case that it is a single-statement
> problem, but in that case the statement needs to be optimized somehow
> so that it does not access too many rows.  But there is not enough
> information to really diagnose what is going on.



-- 
Baron Schwartz
Author, High Performance MySQL
http://www.xaprb.com/
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