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/