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 >
