Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-11-16
10
Medium Priority
?
5,475 Views
Last Modified: 2013-11-22
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.
0
Comment
Question by:mtchs
  • 6
  • 4
10 Comments
 
LVL 6

Expert Comment

by:carlsiy
ID: 22971971
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
 

Author Comment

by:mtchs
ID: 22971983
>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
 
LVL 6

Expert Comment

by:carlsiy
ID: 22972013
you could try the in  "In_use"  syntax
If the count is zero, the table is open but not currently being used.  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mtchs
ID: 22972029
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
 

Author Comment

by:mtchs
ID: 22972036
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
 
LVL 6

Accepted Solution

by:
carlsiy earned 1000 total points
ID: 22972042
0
 
LVL 6

Expert Comment

by:carlsiy
ID: 22972047
basically this shows the open tables... as to locks it will very well depend on the process that is going on that table.
0
 
LVL 6

Expert Comment

by:carlsiy
ID: 22972053
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
 

Author Closing Comment

by:mtchs
ID: 31517287
Excellent!
That was exactly what I was looking for!
0
 
LVL 6

Expert Comment

by:carlsiy
ID: 22972056
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question