查看mysql 锁表

SHOW ENGINE INNODB STATUS;

------------------------
LATEST DETECTED FAILURE
------------------------
...

TRANSACTIONS
------------
Trx id counter 12345678
Purge done for trx's n:o < 12345677 undo n:o < 0 state: running but idle
History list length 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 1234, OS thread id 1234567890
MySQL thread id 1, query id 1 localhost root
show engine innodb status

---TRANSACTION 12345678, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 2, query id 2 localhost root Sending data
select * from table_name where id = 1 for update
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index `PRIMARY` of table `database_name`.`table_name` trx id 12345678 lock_mode X waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
...

SHOW ENGINE INNODB STATUS; -- 查看死锁信息
KILL QUERY <thread_id>; -- 杀死导致死锁的查询线程

SET GLOBAL innodb_lock_wait_timeout = 50; -- 设置锁等待超时时间为50秒