List:General Discussion« Previous MessageNext Message »
From:Rick James Date:May 14 2012 9:49pm
Subject:RE: Deadlock due lockwait. How can I tell mysql to wait longer?
View as plain text  
... WHERE id IN (...) -- This will (I think) sort the IN list.  Therefore, if two queries
have the same (or overlapping) IN values, there cannot be a deadlock.  (I am assuming
nothing else being touched.)

If, on the other hand, you try to get a list of rows by other means, and the order of the
rows is not as predictable, then you can get deadlocks.  Example:
BEGIN
... WHERE id = 123
... WHERE id = 456
... WHERE id = 5
...
COMMIT
In one case, I could shuffle the statements into numerical order to eliminate a deadlock
that was happening about 1% of the time.

> -----Original Message-----
> From: Claudio Nanni [mailto:claudio.nanni@stripped]
> Sent: Monday, May 14, 2012 12:29 PM
> To: Andrés Tello
> Cc: Baron Schwartz; MySql
> Subject: Re: Deadlock due lockwait. How can I tell mysql to wait
> longer?
> 
> Andrés,
> 
> with pleasure.
> 
> Imagine a website that is used to search, just for example, hotel rooms
> for booking.
> 
> It is possible that a programmer would:
> 
> 1) issue a select that returns the IDs the rooms matching the criteria
> 2) do a loop in the code scanning each ID of the resultset and for each
> ID issue the SELECT to get the details of the Hotel and/or Room
> (probably and,
> using a join)   e.g. SELECT.....WHERE roomid=123
> 
> What happens is that if your search criteria return, for example, 200
> rooms, you will issue 200 selects to get the details, those selects are
> 'twin' selects, that is they are identical with different parameters,
> this in my experience is one main cause of contention, keep in mind
> that a while loop in php (for example) is extremely fast.
> 
> A better approach, always in my experience, is to:
> 
> 1) issue a select that returns the IDs the rooms matching the criteria
> 2) issue 1 select to get all the results by using something like:
> SELECT ,,,,,,,WHERE roomid in (1,2,6,123,239,599,...)
> 3) loop in the resultset and get the details of hotel/room
> 
> May be at first look it just look a choice of style, but just imagine
> this:
> 
> you have an application which search part (aforementioned) has 1000
> hits per second, with the first approach (which I call auto inflicted
> Denial of Service :) ) you will have 1 + 1000x(200-1000) = ~200k-1M
> queries with the second you will have 1+ 1000 queries, no more need to
> scale out :)
> 
> I hope I was enough clear, if not do not hesitate to ask, and please
> anyone correct me if I am wrong.
> 
> Claudio
> 
> 
> 
> 
> 
> 
> 2012/5/14 Andrés Tello <mr.criptos@stripped>
> 
> >
> > 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
> >>
> >
> >
> 
> 
> --
> 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