How can I check if a MySQL Table is already locked?

I'm having an issue with locked tables and blocking I/O.

Is there a way to query the MySQL server first, asking if a particular table is locked, before trying to query the locked table?

I have a few TCL apps, that are time sensitive, and I would rather be able to set a timer callback to retry the SQL command, than to wait in blocking I/O while the table is locked.
The tables that I'm checking are, in the millions of rows per table, and so while backups run, the table can be locked for up to 6-7 minutes. But I can't have blocking I/O for that long, same as on a webpage, a user isn't going to wait that long for the page to load, but they would most likely be satisfied with a message that says the server is busy and to try again in a few moments.

I'm using MySQLtcl under TCL, but I would like a solution for PHP as well.

I've read a few cases where you can use a GET_LOCK(), but that isn't feasible in my case, as I don't need to create a lock, I just want to check to see if it's locked before querying.
mtchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

carlsiyCommented:
You can create a user defined lock with GET_LOCK(str,timeout), see http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html 

Do a get_lock with timeout 0, before your LOCK TABLE and release_lock after your UNLOCK TABLES.
Another thread running the get_lock will return a value 0 to indicate the locks is being held by another thread.

You have to be consistant to make this work, and it will not work if any thread LOCK TABLES without calling get_lock first.
0
mtchsAuthor Commented:
>I've read a few cases where you can use a GET_LOCK(), but that isn't feasible in my case, as I don't need to create a lock, I just want to check to see if it's locked before querying.

I've already read that.
I can't use GET_LOCK() because I would have to go back and edit a couple hundred queries, as well as figure out how to modify mysqldump to perform a GET_LOCK()... which doesn't work.

Is there any way to run a query, that either timesout, instead of blocking indefinitely?
Or, does MySQL show held locks, in another location that I could run as a privileged user?
0
carlsiyCommented:
you could try the in  "In_use"  syntax
If the count is zero, the table is open but not currently being used.  
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

mtchsAuthor Commented:
Is IN_USE() a mysql function?
- I can't seem to find anything related to this as a mysql function/variable/feature.

Or are you referring to the mysqltcl mysqlstate in_use(3) command?
- With this it doesn't seem to work as mysqldump is not called from tcl, it's running in a bash script.
0
mtchsAuthor Commented:
I might have to resort to a hacky way of, having a mem table that doesn't get locked by mysqldump, and just query that mem table before each query as to whether the dump is running or not.
And then before the mysqldump change the value to 1, and after change it back to 0.

I just was looking for a way to use a sql query, to ask the sql server if the table is locked, without having to deal with blocking I/O.
And I can't seem to find if the mysql db holds info regarding other table locks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carlsiyCommented:
basically this shows the open tables... as to locks it will very well depend on the process that is going on that table.
0
carlsiyCommented:
or this
http://dev.mysql.com/doc/refman/5.0/en/show-status.html
 
you could check the Table_locks_waited... but from what I recall.. it needs to wait for another lock process to increment otherwise it is still 0
0
mtchsAuthor Commented:
Excellent!
That was exactly what I was looking for!
0
carlsiyCommented:
Are you sure that was the answer you are looking for?... I still have another post after the one you accepted.. better check that out too.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.