?
Solved

NOWAIT signal for a MysQL table

Posted on 2015-02-05
29
Medium Priority
?
543 Views
Last Modified: 2015-03-04
Dear all,

Right now have a tought problme that we find out MySQL don't handle properly, we have AFTER INSERT trigger like this:

CREATE TRIGGER `xxx`.`tblFTCategory_AITRIGGER` AFTER INSERT ON xxx.tblFTCategory FOR EACH ROW
BEGIN
INSERT INTO xxx_DataAudit.`tblFTCategory` SET
TriggerAction_Audit="AFTER",
ActionDone_Audit="INSERT",
ActionTime_Audit=now(),
ActionByHost_Audit=USER() , `ID`=NEW.`ID`, `Description`=NEW.`Description`, `Active`=NEW.`Active`; END;

but if the table xxx_DataAudit.`tblFTCategory` has been locked for READ/WRITE, any insert/delete/update opetaion will wait until the lock released, is there anything to  instruct the trigger to not waiting for the locl release and just return pre defined exception message back to the control/application.

any programming techni like NOWAIT ilke Oracle ,or  check if the table is locked for INSERT/UPDATE/DELETE, then return error messager back too ?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 6
  • 5
29 Comments
 
LVL 8

Assisted Solution

by:Ahmed Merghani
Ahmed Merghani earned 200 total points
ID: 40593031
This will show you if the table is locked:
show open tables WHERE Table LIKE 'tblFTCategory'

Open in new window

if In_use > 0 then the table is locked. Other wise, it is open.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40593045
I think there are problems, when I do this, it said:


Lookup Error - MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Table LIKE 'tblFTCategory'' at line 1

Open in new window



can't see why
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40593050
it seems that it expect no parameter as the input.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40593051
What is yor MySQL version ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40593055
5.5.28
0
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40593100
Sorry forget to provide the database:
show open tables FROM xxx WHERE Table LIKE 'tblFTCategory'

Open in new window

Replace "xxx" with your database name.
0
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40593102
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40593126
still the same error:


MySQL Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Table LIKE 'tblFTCategory'' at line 1

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 40593135
I found that we can't just add a table name in the where condition, In_use>0 is ok.

show open tables where In_use=0;

I can't use Databse and Table in the where condition
0
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40593262
OK.

You can either
show open tables in xxx like 'tblFTCategory';

Open in new window

and then check the in_use.
OR
show open tables  in xxx where in_use > 0;

Open in new window

and then check the table name.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40593287
you mean can't do it in singal query ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40593292
should we use Name_Locked instead of in_use ?
0
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 40593354
Yes you can not do it in a single query as far as I know.
regarding the Name_Locked, you should not use it as the documentation said:
Whether the table name is locked. Name locking is used for operations such as dropping or renaming tables.
and it related to the table name for the operations "dropping or renaming".
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40593700
so you mean only if the table if dropping or renaming then it will show it as it should be.

but I am expecting it never be useful as the dropping and renaming operation is too fast to watch ?

so right now it seems that I can't integrate these 2 x statement into my trigger statement, even as a if and end condition ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40602614
in this sense it seems I can't put it in the code in IF .. ELSE loop.

this logic is now:
1) if the number of row return from "show open tables in WebHynet like 'tblinventory';" is  0 and
2)  if the number of row return from  "show open tables in WebHynet where in_use > 0;" then

we can insert record to the target table.

but it seems that I can't select count(*) from both
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40602648
any possible like:

if (show open tables in xxx like 'yyy';) is null and
 if (show open tables in xxx where in_use > 0;) is null then

write to target table?

else return error?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 40616129
As you have seen, the "Show Open Tables" is a query in itself and can not be in a SubQuery:
So you end up parsing the result in your application after executing:

show open tables in YourDB 
like 'YourTable'

/* Then test the field in_use */

Or:

show open tables in YourDB 
where In_use > 0;
/* Then look for your Target Table among the result set */ 

Open in new window


What you can do is use Named Lock:
GET_LOCK
IS_FREE_LOCK / IS_USED_LOCK
RELEASE_LOCK
When you want to Lock the Audit table issue a "Get_Lock" with a unique keyword.
And before inserting from the application check "IS_FREE_LOCK" for the Lock Keyword you issued earlier. If not used, meaning there is no Lock, proceed and insert your data, otherwise the Audit Table is Locked and hence you handle that with the predefined exception message that you wanted.
Do not forget, after you are done with the Audit Table, release the lock "RELEASE_LOCK".
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40627390
"When you want to Lock the Audit table issue a "Get_Lock" with a unique keyword.
 And before inserting from the application check "IS_FREE_LOCK" for the Lock Keyword you issued earlier."

Please explain in coding how to do it only for my case ? I don't quite understand what is printed in the link.

"Do not forget, after you are done with the Audit Table, release the lock "RELEASE_LOCK". "

or the table we are trying to write will be lock forever? just like the case if the data is not commited?

show open tables in YourDB 
like 'YourTable'

/* Then test the field in_use */

Open in new window


but it seems that I can't return the result as a value, like select count(*) from show open tables in YourDB
like 'YourTable'
then how can I check it using condition ?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 40627571
>   "Please explain in coding how to do it only for my case ? I don't quite understand what is printed in the link."

First let's agree that the keyword to be checked is going to be 'BlockAudit'.
So before inserting check whether the lock has been issued or not by using:
select IS_FREE_LOCK('BlockAudit');
0 = not free (locked by someone), 1= free and available to use.

Then execute and check the output of the following qurey:
SELECT GET_LOCK('BlockAudit', 5);    /* 5 seconds timeout  */

0 = timedout, 1 = locked successfully, NULL = Error occurred

So before insert/delete/update, check the keyword 'BlockAudit' if the result is 1 then proceed otherwise show an
exception message.

Get_Lock is paired with Release_Lock. Whoever issues the lock should release it when done, in order to be available for others to use, be calling:
SELECT RELEASE_LOCK('BlockAudit'); // or Do RELEASE_LOCK('BlockAudit');
 Hope you get it.

>  "or the table we are trying to write will be lock forever? just like the case if the data is not commited?"
Absolutely not.
First of all the lock is on a keyword and not on any table.
second the lock, on that keyword, is held as long as the user is connected. Once the connection is cut the locked keyword will be released automatically. So no worries here.

>   "then how can I check it using condition ? "
By iterating through the result-set in from your code and not in MySQL query.


What language do you use?
And how do you execute a query and test it's result in code?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40630193
"First let's agree that the keyword to be checked is going to be 'BlockAudit'.
 So before inserting check whether the lock has been issued or not by using:
 select IS_FREE_LOCK('BlockAudit');
 0 = not free (locked by someone), 1= free and available to use.
"

then how can I specify the talbe name I am going to check ? so your example is, the table name is BlockAudit?

when I run select IS_FREE_LOCK('BlockAudit'); in my side, it returns 0.

when I try this one: SELECT GET_LOCK('BlockAudit', 5); ., it returnes 1

"show an
 exception message."

what is the way to define error message?


">   "then how can I check it using condition ? "
 By iterating through the result-set in from your code and not in MySQL query.

What language do you use?
 And how do you execute a query and test it's result in code? "

you mean code in the application layer but not in MySQL layer?

I just wrote that trigger and nothing else.
0
 
LVL 24

Expert Comment

by:jimyX
ID: 40632615
> "then how can I specify the talbe name I am going to check ? so your example is, the table name is BlockAudit?"
> "what is the way to define error message?"
> "you mean code in the application layer but not in MySQL layer?"

Exactly, the application layer. triggers can not interact with users.
What you are going to do actually is utilize the application layer to control the access to the tables through the Named Lock technique.

> "I just wrote that trigger and nothing else. "
Eventually you will be having an application that manages the data, right?
There you should be doing the access control and validation.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40634838
"Exactly, the application layer. triggers can not interact with users.
 What you are going to do actually is utilize the application layer to control the access to the tables through the Named Lock technique."

so nothing can be done on MySQL side to check the table lock before writing ?

how about time out ? anything like like NOWAIT ilke Oracle  ?

"Eventually you will be having an application that manages the data, right?"

yes, that one is writen in PHP. but our developer want to check if there are something can be done on MySQL side.

at leaste we can return an error message to the applicatoin can say the table was locked and PHP will try to do something else.

Any from MySQL side can we do it ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40634839
again, so your example is, the table name is BlockAudit?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 40635207
> "so nothing can be done on MySQL side to check the table lock before writing ?"
You can not check and take action from MySQL side. You have to make your application the controller.

> "how about time out ? anything like like NOWAIT ilke Oracle  ?"
No. There is no "NOWAIT" in MySQL. If you use InnoDB, then there is Timeout.

> "but our developer want to check if there are something can be done on MySQL side."
> "Any from MySQL side can we do it ?"
Not much actually could be done from MySQL side, only to try and handle exceptions if arose.
But you better not allow exceptions happen, if you are able to check for potential exception generating actions, then that's an advantage to embrace.

> "so your example is, the table name is BlockAudit?"
No. It is not a table. It is a mutex.
Think of it as a flag that gets changed based on agreed actions started by first user. And everyone checks this flag to know whether they can go about that agreed action or someone else is processing and they should try later.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40639201
"If you use InnoDB, then there is Timeout."

yes, I am using InnoDB.  this is what we are having?  from that link, is that mean this is a MySQL startup option and no coding at all ?

"only to try and handle exceptions if arose.
But you better not allow exceptions happen, if you are able to check for potential exception generating actions, then that's an advantage to embrace."

please give example on how to do in this way is the table has been locked and we need to check it before writing.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40644039
any answer?
0
 
LVL 24

Accepted Solution

by:
jimyX earned 1800 total points
ID: 40644068
> "from that link, is that mean this is a MySQL startup option and no coding at all ?"

Yes, but you need to check, that might differ from version to another.

> "please give example on how to do in this way is the table has been locked and we need to check it before writing. "

Here is a pseudo code, should be done from PHP:
Before inserting in xxx.tblFTCategory, from PHP side, follow the following steps:
Step 1: Execute "select IS_FREE_LOCK('BlockAudit'); /* 0 = not free*/"
Step 2: Check the result if equals 0 then the table is locked. Show error message and halt, otherwise proceed.
Step 3: Execute "SELECT GET_LOCK('BlockAudit', 5);"
If result equals 0 means timedout, or NULL means Error occurred, then show error message "could not lock the table", and halt.
if result equals 1 means locked successfully, proceed.
Step 4: do your processing on the tables.
Step 5: (Finished working on the tables) Execute "SELECT RELEASE_LOCK('BlockAudit');".


This way is to avoid no wait, as you stated in your question:
"is there anything to  instruct the trigger to not waiting for the locl release and just return pre defined exception message back to the control/application."
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40644143
"Yes, but you need to check, that might differ from version to another."


tks.
0

Featured Post

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 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