Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Detecting locked tables (locked by LOCK TABLE)

Tags:

mysql

locking

Is there a way to detect locked tables in MySQL? I mean tables locked by the LOCK TABLE table WRITE/READ command.

(Note that readers interested in detecting named locks acquired with GET_LOCK should read Show all current locks from get_lock instead.)

like image 608
Marcin Avatar asked Mar 23 '10 12:03

Marcin


People also ask

How do you check if there is a lock on a table?

You can use the sys. dm_tran_locks view, which returns information about the currently active lock manager resources.

How do I find a locked table in MySQL?

In MySQL, locked tables are identified using the SHOW OPEN TABLES command. In its simplest form is displays all locked tables. All open tables in the table cache are listed, but the IN_USE column indicates of the table is locked. When the first lock is taken, the value increments to 1.

How do you break a table lock?

Hold the tension wrench twisted in the correct direction and insert the rake into the lock where the teeth of the keys would go. Push and pull the rake out of the lock, twisting it and working by feel. Twist the tension wrench in the correct direction, and the lock should spring open!


1 Answers

Use SHOW OPEN TABLES: http://dev.mysql.com/doc/refman/5.1/en/show-open-tables.html

You can do something like this

SHOW OPEN TABLES WHERE `Table` LIKE '%[TABLE_NAME]%' AND `Database` LIKE '[DBNAME]' AND In_use > 0; 

to check any locked tables in a database.

like image 105
Aruman Avatar answered Oct 10 '22 03:10

Aruman