-- 冠以解决select..for update 超时的问题,在测试表中有一条数据使用查询时超时,for update缓慢
-- 查询设置超时时间
show variables like 'innodb_lock_wait_timeout';
select ..... for update
-- 查询连接进程 如果有大量长时间连接可以kill
show processlist
-- 将相关进程关闭
kill 2032
-- 显示正在使用表或想要获取表的锁的相关表
show open tables where in_use>0;
对于参数的解释
Database
The database containing the table.
Table
The table name.
In_use
The number of table locks or lock requests there are for the table. For example, if one client acquires a lock for a table using LOCK TABLE t1 WRITE, In_use will be 1. If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client will block waiting for the lock, but the lock request causes In_use to be 2. If the count is zero, the table is open but not currently being used. In_use is also increased by the HANDLER ... OPEN statement and decreased by HANDLER ... CLOSE.
Name_locked
Whether the table name is locked. Name locking is used for operations such as dropping or renaming tables.
If you have no privileges for a table, it does not show up in the output from SHOW OPEN TABLES.
SELECT * FROM INNODB_LOCK_WAITS;
set global innodb_lock_wait_timeout=100;
set innodb_lock_wait_timeout=100;
select @@version;