Re: How to identify which connection has locked a particular table
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
Subject
Views
Written By
Posted
4924
November 21, 2011 07:50AM
2030
November 22, 2011 09:31AM
Re: How to identify which connection has locked a particular table
2294
November 25, 2011 05:09AM
2102
November 27, 2011 11:12PM
1602
November 28, 2011 12:20AM
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.