mysql查看锁表方法详解

mysql查看锁表方法详解

 

１、show OPEN TABLES where In_use > 0;

查看哪些表在使用中，In_use列表示有多少线程正在使用某张表，Name_locked表示表名是否被锁，这一般发生在Drop或Rename命令操作这张表时。所以这条命令不能帮助解答我们常见的问题：当前某张表是否有死锁，谁拥有表上的这个锁等。

 

 

２、show status like '%lock%', show status like 'Table%'查看服务器状态

 

 

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：
mysql> show status like 'Table%';
+----------------------------+----------+
| Variable_name       | Value |
+----------------------------+----------+
| Table_locks_immediate   | 105       |
| Table_locks_waited  | 3     |
+----------------------------+----------+
2 rows in set (0.00 sec)

 

 

 锁状态

 

mysql的锁有表锁和行锁，myisam最小锁为表锁，innodb最小锁为行锁，可以通过以下命令获取锁定次数、锁定造成其他线程等待次数，以及锁定等待时间信息。

 

mysql> show status like '%lock%';

+------------------------------------------+---------+

| Variable_name                            | Value   |

+------------------------------------------+---------+

| Com_lock_tables                          | 0       |

| Com_unlock_tables                        | 0       |

| 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       |

| Key_blocks_not_flushed                   | 0       |

| Key_blocks_unused                        | 13396   |

| Key_blocks_used                          | 19      |

| Performance_schema_locker_lost           | 0       |

| Performance_schema_rwlock_classes_lost   | 0       |

| Performance_schema_rwlock_instances_lost | 0       |

| Qcache_free_blocks                       | 0       |

| Qcache_total_blocks                      | 0       |

| Table_locks_immediate                    | 1570736 |

| Table_locks_waited                       | 7294    |

+------------------------------------------+---------+

 

 

如当Table_locks_waited与Table_locks_immediate的比值较大，则说明我们的表锁造成的阻塞比较严重，可能需要调整Query语句，或者更改存储引擎，亦或者需要调整业务逻辑。当然，具体改善方式必须根据实际场景来判断。而Innodb_row_lock_waits较大，则说明Innodb的行锁也比较严重，且影响了其他线程的正常处理。同样需要查找出原因并解决。造成Innodb行锁严重的原因可能是Query语句所利用的索引不够合理（Innodb行锁是基于索引来锁定的），造成间隙锁过大。也可能是系统本身处理能力有限，则需要从其他方面来考虑解决。

 

 

３、show processlist;

查看进程状态

 

mysql> show processlist;

查看进程状态，通过此命令可以查看哪些sql在等待锁

 

4、进入information_schema 数据库，查询表PROCESSLIST,查看各SQL执行状态

 

mysql> select * from PROCESSLIST  where DB='database_name' limit 10;

+----------+---------+---------------------+------------------------------------------+---------+------+-

| ID       | USER    | HOST          | DB          | COMMAND | TIME | STATE | INFO |

+----------+---------+---------------------+------------------------------------------+---------+------+-

| 33076340 | db_user| 192.168.13.1:50355 | database_name | Sleep   |   10 |       | NULL | 

| 33076170 | db_user| 192.168.13.1:44206 | database_name | Sleep   |   97 |       | NULL | 

| 33075794 | db_user| 192.168.13.1:44156 | database_name | Sleep   | 1063 |       | NULL | 

+----------+---------+---------------------+------------------------------------------+---------+------+-

此方式和上面第三种方式是一样的效果