Menu
Is there any way via a MySQL query to get the locked tables? I have a C# threading application running and there are bunch of tables getting locked in the app.
I need to see the locked tables and analyze the code that could be locking it.
794
SELECTs do not normally do any locking that you care about on InnoDB tables. The default transaction isolation level means that selects don't lock stuff.
SHOW OPEN TABLES WHERE `Table` LIKE '%[TABLE_NAME]%' AND `Database` LIKE '[DBNAME]' AND In_use > 0; to check any locked tables in a database. Show activity on this post. You can use SHOW OPEN TABLES to show each table's lock status.
MySQL Locks: Read LocksIf the session holds the READ lock on a table, they cannot perform a write operation on it. It is because the READ lock can only read data from the table. All other sessions that do not acquire a READ lock are not able to write data into the table without releasing the READ lock.
A locked table remains locked until you either commit your transaction or roll it back, either entirely or to a savepoint before you locked the table. A lock never prevents other users from querying the table. A query never places a lock on a table.
Use:
SHOW OPEN TABLES An check whether the column In_use is greater than 0. In that case, the table is locked.
List of locked tables:
show open tables WHERE In_use > 0
Check whether the table tb_employees is locked or not:
show open tables WHERE Table LIKE 'tb_employees' AND In_use > 0
From the official documentation:
In_useThe number of table locks or lock requests there are for the table. For example, if one client acquires a lock for a table using LOCK TABLE t1 WRITE, In_use will be 1. If another client issues LOCK TABLE t1 WRITE while the table remains locked, the client will block waiting for the lock, but the lock request causes In_use to be 2. If the count is zero, the table is open but not currently being used. In_use is also increased by the HANDLER ... OPEN statement and decreased by HANDLER ... CLOSE.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
