MySQL Forums
Forum List  »  MyISAM

Re: How to identify which connection has locked a particular table
Posted by: Veerabahu Subramanian
Date: November 25, 2011 05:09AM

Hi James,

Thanks for your response. I repeated the same steps mentioned in the bug i.e., in one connection do select from table and in other connection do alter table with auto commit set to OFF in both connections. After doing this, show processlist from 3rd connection shows the below output
mysql> show processlist;
+----+-----------------+-----------------+----------+---------+------+---------------------------------+--------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+----------+---------+------+---------------------------------+--------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 399 | Waiting for next activation | NULL |
| 2 | Admin | 127.0.0.1:57833 | tejnmsdb | Sleep | 8 | | NULL |
| 3 | root | localhost | tejnmsdb | Sleep | 392 | | NULL |
| 7 | root | localhost | tejnmsdb | Sleep | 51 | | NULL |
| 8 | root | localhost | tejnmsdb | Query | 86 | Waiting for table metadata lock | Alter table test1 ADD COLUMN (k INT) |
| 9 | root | localhost | tejnmsdb | Query | 0 | NULL | show processlist |
+----+-----------------+-----------------+----------+---------+------+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)

Here connection ID 7 is the actual culprit (i.e the client through which I did select). Doing commit on connection id 7 makes the alter to complete. In this context
1. The above algorithm doesn't point to the correct connection. Kindly point me if I am doing something wrong.
2. Can you also give some pointers on how to find the connections that are locked from show process list ( Find the connection(s) that are not "Locked")

TIA,
Veerabahu

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to identify which connection has locked a particular table
2294
November 25, 2011 05:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.