Unlocking tables if thread is lost

Question

http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html

The following is the extract from the documentation.

mysql> LOCK TABLES real_table WRITE, temp_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM temp_table;
mysql> DELETE FROM temp_table;
mysql> UNLOCK TABLES;

My question is that if I use this logic in the shell script code, and if the thread is lost (not killed) before it reaches "unlock tables" statement, how will I know that some tables are locked and how do I unlock tables manually?

Have you considered using a transaction instead? http://stackoverflow.com/questions/4226766/mysql-transactions-vs-locking-tables — GordonM, Mar 20 '12 at 08:44

score 164 · Answer 1 · answered Mar 19 '13 at 19:37

164

Here's what i do to FORCE UNLOCK FOR some locked tables in MySQL

1) Enter MySQL

mysql -u your_user -p

2) Let's see the list of locked tables

mysql> show open tables where in_use>0;

3) Let's see the list of the current processes, one of them is locking your table(s)

mysql> show processlist;

4) Let's kill one of these processes

mysql> kill put_process_id_here;

answered Mar 19 '13 at 19:37

Kostyantyn

5,041
3
34
30

4

Somehow I've managed to get a table locked, without anything being shown in the processlist (besides sleeping connections)... – Kevin Jul 11 '13 at 00:43
1

If your process is missing entirely but you are getting complaints about locked tables: just restart mysqld. For me, it was "service mysqld restart" – Emery King Oct 27 '15 at 15:31
1

After I killed a specific pid, for some reason, processlist becomes empty – hello_harry May 12 '16 at 18:41
2

Works perfectly! – Oluwaseye Aug 09 '18 at 13:19
1

This is the perfect solution, I'd locked a table thru an crash of my HeidiSQL Client. I'd expected to need root/SUPER privilege, which I don't have, but this worked without any raised privilege. – aSystemOverload Dec 10 '18 at 15:44
for example: open 2 connection, in first connection, connection id is 1, another is 2; in connection 1 we execute sql: lock tables test write; and in connection 2, we can see a table is be locked: show open tables where in_use > 0 ; can know a table be locked; but I can not know which proces ID locked it if in diff connection session id; so what can i kill it? – 君主不是你 Nov 29 '19 at 07:24
1

this is useless without the way how to tell the process id that holds the lock. – FaceBro Jun 24 '20 at 18:52
I found that the `Info` column in the processlist would show the query that ended up leaving the table open. YMMV. – Charles Wood Jun 14 '23 at 01:26

score 33 · Accepted Answer · answered Mar 20 '12 at 09:26

33

how will I know that some tables are locked?

You can use SHOW OPEN TABLES command to view locked tables.

how do I unlock tables manually?

If you know the session ID that locked tables - 'SELECT CONNECTION_ID()', then you can run KILL command to terminate session and unlock tables.

answered Mar 20 '12 at 09:26

Devart

119,203
23
166
186

5

if I do not know the session ID and the terminal that locked table is not open, then? – shantanuo Mar 20 '12 at 09:43
2

Try to set WAIT_TIMEOUT period before the locking tables (e.g. SET WAIT_TIMEOUT = 120;). It will help to close that connection automatically after the specified period. – Devart Mar 20 '12 at 13:40
1

Not so versatile approach. – Márton Tamás Feb 05 '19 at 18:25

score 3 · Answer 3 · answered Sep 16 '15 at 19:51

3

With Sequel Pro:

Restarting the app unlocked my tables. It resets the session connection.

NOTE: I was doing this for a site on my local machine.

answered Sep 16 '15 at 19:51

kevnk

18,733
3
28
30

Unlocking tables if thread is lost

3 Answers3

Linked