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
