Detect when MySQL tables are in readlock state (from PHP)

Question

Hello,

I need to get a PHP app to detect when it's MySQL database is in readlock, and throw an error message. Easy, but is there any MySQL command we can run to check current lock state, rather than just having the system get stuck, as the query is queued for the unlock?

Cheers,

Johnathon

Edit: The database is being put in readlock by the DBA, but our client wants the application to automatically handle this suituation... I've not found anything whilst going through the docs, hence asking the experts :-)

Answer 1

Since your PHP script is waiting for a response from the MySQL server, it may be difficult to determine what state the query it has committed is in as it executes. If you're having problems with read locks in queries, you're probably better off going about problem solving and investigating why your queries are getting locked to the point where it is causing your scripts to hang, rather than just botching together some sketchy handlers for it, because locked queries can indicate a serious underlying problem.

Consult the MySQL documentation and the mysql performance blog for pointers on how to tweak startup variables for your MySQL server to help improve performance. Also make sure you're carefully using PHP sessions in a managed way - if you have lots of asynchronous requests at the same time to your server from a single user, PHP can sometimes hang because the session file is locked from multiple threads, making database queries hang with it. This is a serious problem that we encountered.

Locked queries can be pretty tricky to troubleshoot, but the best thing for you to do is to investigate the root cause of why they're getting locked and resolve it. Executing SHOW FULL PROCESSLIST; will give you a list of all your open connections to your database and can give you information to help you identify slow running queries that are locking up subsequent queries.

Hope that helps, good luck.

Answer 2

After checking the docs there doesn't appear to be a way of doing this through PHP's mysql C API

You can review detected deadlocks by running "SHOW ENGINE [EngineName] STATUS" replacing engine name with INNDOB, MyISAM or whatever engine the locked table is running.

You can also get lock information by running "mysqladmin debug" from a command line

People have requested a SHOW LOCKS command but it doesnt exist as yet.

This is all I can establish from the docs. I'm hoping someone else has a solution for this, as it could be very useful to me.

Answer 3

If your app is the only thing accessing and potentially locking said database, you could use the GET_LOCK and RELEASE_LOCK mysql functions, before trying to access the database.

If you don't have control over how it's being locked, then I'll have to defer to the wiser folks here than myself.