Tuesday, January 26, 2010

[Level 2] MySQL Table lock status

Sometimes when database has performance issue.
You may check if the root cause is session waiting to long to execute.
You can use the command "show global status like 'Table_locks%" to get the table lock status.

mysql> show global status like 'Table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 18    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.06 sec)

mysql>

There are two variables called "Table_locks_immediate" and "Table_locks_waited",
if the percentage of "immediate" is low and "waited" is high,
that shows you that the root cause "might be" the table lock.

Wish this helps.

regards,
Stanley Huang