MySQL表锁情况 获取锁等待情况可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺: mysql> show status like 'Table%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | Table_locks_immediate | 61437800 | | Table_locks_waited | 233 | +----------------------------+----------+ 2 rows in set (0.00 sec) Table_locks_immediate表示立即释放MySQL表锁数,Table_locks_waited表示需要等待的MySQL表锁
数,如果Table_locks_immediate / Table_locks_waited >
5000,最好采用InnoDB引擎,因为InnoDB是行锁而MyISAM是MySQL表锁,对于高并发写入的应用InnoDB效果会好些。示例中的服
务器Table_locks_immediate / Table_locks_waited = 235,MyISAM就足够了。
可以通过检查Innodb_row_lock状态变量来分析系统上的行锁的争夺情况: mysql> show status like 'innodb_row_lock%'; +----------------------------------------+----------+ | Variable_name | Value | +----------------------------------------+----------+ | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 2001 | | Innodb_row_lock_time_avg | 667 | | Innodb_row_lock_time_max | 845 | | Innodb_row_lock_waits | 3 | +----------------------------------------+----------+ 5 rows in set (0.00 sec) |
|