一、查询表级锁争用情况
mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Table_locks_immediate | 100 |
| Table_locks_waited | 11 |
+----------------------------+---------+
Table_locks_immediate:产生表级锁定的次数;
Table_locks_waited:出现表级锁定争用而发生等待的次数;
二、通过检查InnoDB_row_lock状态分析行锁的争夺情况:
mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+-------+
InnoDB_row_lock_current_waits:当前正在等待锁定的数量;
InnoDB_row_lock_time:从系统启动到现在锁定总时间长度;
InnoDB_row_lock_time_avg:每次等待所花平均时间;
InnoDB_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;
InnoDB_row_lock_waits:系统启动后到现在总共等待的次数;
三、查询是否锁表
mysql>show OPEN TABLES where In_use > 0;
四、.查询进程(如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程)
mysql>show processlist
id 为5的证明一直在等待资源。
3.杀死进程id(就是上面命令的id列)
kill id 5
五、查看下在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
杀死进程id(就是上面命令的trx_mysql_thread_id列)
kill 线程ID
例子:
查出死锁进程:SHOW PROCESSLIST
杀掉进程 KILL 420821;
六、查看mysql数据库是否需要手动提交
show VARIABLES like '%autocommit%'