MySQL is adding more tools to monitor its internals with every new release, but one thing it still lacks is a way to find out who is locking what, and therefore which transactions block which other ones. This is such a vital feature that I’m considering writing my own patch to the source! Still, it is possible, to a limited extent, to find out who’s locking resources. In this article I’ll explain how you can do that.
This article is the second in a series on how to use the innotop MySQL and InnoDB monitor.
Introduction
Here’s the situation: you are trying to update a table and every time you issue the query, it hangs until it times out and tells you the lock wait timeout was exceeded. Someone has locked the table you’re trying to update, but you have no idea who. This can be incredibly frustrating, because this could go on indefinitely. I’ve sometimes had to put work off till another day, because the table is locked all day long.
I’ve found only a very limited set of circumstances in which MySQL will say what’s happening with locks. These are all printed out in the text of SHOW ENGINE INNODB STATUS.
When there was a deadlock
The first way to see locks is when there’s been a deadlock. The status text will show transaction information on the transactions that deadlocked, which locks they held, and which they were waiting for. Here is a sample. Look at the sections titled “WAITING FOR THIS LOCK TO BE GRANTED” and “HOLDS THE LOCKS.”
------------------------LATEST DETECTED DEADLOCK------------------------060731 20:19:58*** (1) TRANSACTION:TRANSACTION 0 93698, ACTIVE 2 sec, process no 12767, OS thread id 1141946720 starting index readmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1216MySQL thread id 3, query id 19 localhost root Updatingupdate test.innodb_deadlock_maker set a = 0 where a 0*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 131120 n bits 72 index `GEN_CLUST_INDEX` of table `test/innodb_deadlock_maker` trx id 0 93698 lock_mode X waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000019000; asc ;; 1: len 6; hex 000000016e01; asc n ;; 2: len 7; hex 80000000320110; asc 2 ;; 3: len 4; hex 80000000; asc ;;*** (2) TRANSACTION:TRANSACTION 0 93699, ACTIVE 2 sec, process no 12767, OS thread id 1142212960 starting index read, thread declared inside InnoDB 500mysql tables in use 1, locked 14 lock struct(s), heap size 1216MySQL thread id 4, query id 20 localhost root Updatingupdate test.innodb_deadlock_maker set a = 1 where a 1*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 0 page no 131120 n bits 72 index `GEN_CLUST_INDEX` of table `test/innodb_deadlock_maker` trx id 0 93699 lock mode SRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000019000; asc ;; 1: len 6; hex 000000016e01; asc n ;; 2: len 7; hex 80000000320110; asc 2 ;; 3: len 4; hex 80000000; asc ;;Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000019001; asc ;; 1: len 6; hex 000000016e01; asc n ;; 2: len 7; hex 8000000032011f; asc 2 ;; 3: len 4; hex 80000001; asc ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 131120 n bits 72 index `GEN_CLUST_INDEX` of table <strong style="color:transparent">本文来源gaodai#ma#com搞@@代~&码网^</strong>`test/innodb_deadlock_maker` trx id 0 93699 lock_mode X waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 000000019000; asc ;; 1: len 6; hex 000000016e01; asc n ;; 2: len 7; hex 80000000320110; asc 2 ;; 3: len 4; hex 80000000; asc ;;*** WE ROLL BACK TRANSACTION (2)
转载请注明原文链接：How to find out who is locking a table in MySQL_MySQL
