LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 01-24-2011, 12:57 PM   #1
jacppe
LQ Newbie
 
Registered: Jan 2011
Location: Lima, Peru.
Distribution: CentOS
Posts: 9

Rep: Reputation: 0
Detect when Mysql has locked tables


Hi all, I'm running Mysql server. I need to know if is possible to generate an alert (email) when in the database are locked tables.
Is that possible?
Thanks a lot.
 
Old 01-24-2011, 02:06 PM   #2
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297
Hello,

Not sure if this is what you want but you could use the show open tables command on a database. If the output shows something like this:
Code:
mysql> show open tables from test;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | tab1  |      3 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)
then the output in column In_use tells you how many threads are using this table, meaning have the table locked or waiting.

You could use that in a script (connect to DB first, then the command) and mail you if the output is positive.

Kind regards,

Eric

Last edited by EricTRA; 01-25-2011 at 12:48 AM.
 
Old 01-24-2011, 02:16 PM   #3
jacppe
LQ Newbie
 
Registered: Jan 2011
Location: Lima, Peru.
Distribution: CentOS
Posts: 9

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by EricTRA View Post
Hello,

Not sure if this is what you want but you could use the show open tables command on a database. If the output shows something like this:
Code:
mysql> show open tables from test;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| mydb     | tab1  |      3 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)
then the output in column In_use tells you how many threads are using this table, meaning have the table locked or waiting.

You could use that in a script (connect to DB first, then the command) and mail you if the output is positive.

Kind regards,

Eric

Hi Eric, my idea is generate an alert when a table is locked state for more than xx seconds.
 
Old 01-25-2011, 12:57 AM   #4
EricTRA
LQ Guru
 
Registered: May 2009
Location: Gibraltar, Gibraltar
Distribution: Fedora 20 with Awesome WM
Posts: 6,805
Blog Entries: 1

Rep: Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297Reputation: 1297
Hello,

I don't know if there exists a function within MySQL that can give you that output but you might include some function for it in your script.

If a table is not locked then your script should do nothing, right?
Say that you detect a table as locked using the show open tables command. You could save the status in a variable and enter in a loop that checks like every ten seconds (or whatever interval you want).
When the counter (number of checks x ten seconds for example) reaches 30 (5 minutes) send a mail that the table has been locked for ten minutes.

Most likely other users with more MySQL experience can put in something more useful but this is how I would do it for starters meanwhile looking for another solution.

Kind regards,

Eric
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql reinstallation distorted by previous tables in /var/mysql mad4linux Linux - Software 0 10-04-2005 01:39 PM
MySQL tables from 4.0 to 3.0 michaelbhahn Linux - Software 0 08-31-2004 10:23 AM
Corrupted MySQL Tables cli_man Linux - Software 4 10-22-2003 06:49 AM
mysql tables with a - pk21 Linux - Software 7 05-23-2003 06:27 AM
mysql tables noord28 Linux - Newbie 0 02-20-2003 02:56 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 04:19 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration