... 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.na...@gmail.com] > 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.crip...@gmail.com> > > > > > Claudio, would you please extend the example to the use of in? > > > > > > On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni > <claudio.na...@gmail.com>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 <ba...@xaprb.com> > >> > >> > 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 <ba...@xaprb.com> > >> wrote: > >> > > Johan, > >> > > > >> > > On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman < > >> vegiv...@tuxera.be> > >> > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
