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
