Download - All IT eBooks

this tool further in “SHOW ENGINE INNODB STATUS and InnoDB Monitors” on page 96 and in detail in Chapter 6:
mysql> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
110802 2:03:45 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 41 seconds
...
-----------TRANSACTIONS
-----------Trx id counter 0 26243828
Purge done for trx's n:o < 0 26243827 undo n:o < 0 0
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 101514240
MySQL thread id 4483, query id 25022097 localhost root
show engine innodb status
---TRANSACTION 0 26243827, ACTIVE 9 sec, OS thread id 101403136 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 4481, query id 25022095 localhost root Updating
update t set a=26 where a=6
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 349 page no 3 n bits 88 index `PRIMARY` of table `test`.`t`
trx id 0 26243827 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 00000006; asc
;; 1: len 6; hex 0000019072e3; asc
r ;; 2:
len 7; hex 800000002d0110; asc
- ;;
--------------------TRANSACTION 0 26243821, ACTIVE 125 sec, OS thread id 101238272,
thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 4480, query id 25022091 localhost root User sleep
update t set a=sleep(200) where a=6
The important part we need to pay attention to is:
---TRANSACTION 0 26243827, ACTIVE 9 sec, OS thread id 101403136 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320, 1 row lock(s)
MySQL thread id 4481, query id 25022095 localhost root Updating
update t set a=26 where a=6
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
This shows that a query is waiting for a lock.
Locks | 61