List:General Discussion« Previous MessageNext Message »
From:Andrés Tello Date:May 14 2012 5:33pm
Subject:Re: Deadlock due lockwait. How can I tell mysql to wait longer?
View as plain text  
Claudio, would you please extend the example to the use of in?


On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni <claudio.nanni@stripped>wrote:

> In my experience if you have a poor designed code that run the same query
> for hundreds or thousands of times in a very short timespan (like some
> programmers do in for-loop instead of using a IN for example) you can put
> mysql on its knees, in some cases it may be the practical implementation of
> some lock mechanisms are particularly challenged by this  ultra high data
> 'locality' which bring to very high contention on a few hotspots at
> different levels (mutexes, indexes, pages).
>
>
> Just reflections :)
>
> Claudio
>
> 2012/5/14 Baron Schwartz <baron@stripped>
>
> > 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/
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql
> >
> >
>
>
> --
> 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