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