Saturday, May 7, 2016

Run execute MySQL command in shell under console

Run execute MySQL command in shell under console

To debug InnoDB lock waits. Show how many rows are locked (look under transaction):

# mysql -u root -p -e 'SHOW ENGINE INNODB STATUS\G'


------------
TRANSACTIONS
------------
Trx id counter 381
Purge done for trx's n:o < 37F undo n:o < 0
History list length 39
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 84, OS thread handle 0x7f5b66aff700, query id 975 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 50, OS thread handle 0x7f5b66c23700, query id 838 192.168.6.112 jun
---TRANSACTION 32E, not started
MySQL thread id 33, OS thread handle 0x7f5b8c04a700, query id 693 192.168.6.112 jun
---TRANSACTION 35F, not started
MySQL thread id 31, OS thread handle 0x7f5b66c6c700, query id 518 192.168.6.112 jun
---TRANSACTION 340, not started
MySQL thread id 28, OS thread handle 0x7f5b66bda700, query id 381 192.168.6.112 jun
---TRANSACTION 380, ACTIVE 1 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 83, OS thread handle 0x7f5b66b91700, query id 973 127.0.0.1 go_erp statistics
SELECT   SO.idOrder , SOLine.changed , SOLine.orderQty FROM SO INNER JOIN SOLine ON SO.idOrder = SOLine.idOrder WHERE SO.idOrder = 1 ORDER BY SO.idOrder FOR UPDATE
------- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 323 n bits 72 index `PRIMARY` of table `go_erp`.`SO` trx id 380 lock_mode X locks rec but not gap waiting
------------------
---TRANSACTION 37F, ACTIVE 2 sec
5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 82, OS thread handle 0x7f5b66b48700, query id 969 127.0.0.1 go_erp
----------------------------

# mysql -u root -p -e 'test.sql'

Install innotop:

# yum install innotop

Reference:

http://www.xaprb.com/blog/2007/09/18/how-to-debug-innodb-lock-waits/

No comments: