Friday, May 7, 2010

How an administrator can unlock a table to overcome deadlock situation in MySQL

This is a common problem for a MYSQL DBA if the application has table lock bug. To unlock a table I use session kiil procedure. The steps are....

Step 1: login into mysql user root user.
Step 2: list all the sessions running in MySQL server.

show processlist;

The output will be looks like....

| Id | User | Host | db | Command | Time | State | Info | +-----+----------+-----------+------+---------+------+-------+------------------+
| 349 | his_user | localhost | NULL | Query | 0 | NULL | show processlist | +-----+----------+-----------+------+---------+------+-------+------------------

...if the MySQL server has no other running process. Otherwise the output will show other process id also. For locked processes the status will indicate the work lock.

Step 3: The last step is to kill the locked running process using process Id.

kill xxx;

'xxx' is the process Id value.

No comments:

Post a Comment