• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

NOWAIT signal for a MysQL table

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
marrowyung
Asked:
marrowyung
  • 17
  • 6
  • 5
2 Solutions
 
Ahmed MerghaniCommented:
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
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
it seems that it expect no parameter as the input.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Ahmed MerghaniCommented:
What is yor MySQL version ?
0
 
marrowyungAuthor Commented:
5.5.28
0
 
Ahmed MerghaniCommented:
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
 
Ahmed MerghaniCommented:
0
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
Ahmed MerghaniCommented:
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
 
marrowyungAuthor Commented:
you mean can't do it in singal query ?
0
 
marrowyungAuthor Commented:
should we use Name_Locked instead of in_use ?
0
 
Ahmed MerghaniCommented:
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
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
jimyXCommented:
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
 
marrowyungAuthor Commented:
"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
 
jimyXCommented:
>   "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
 
marrowyungAuthor Commented:
"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
 
jimyXCommented:
> "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
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
again, so your example is, the table name is BlockAudit?
0
 
jimyXCommented:
> "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
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
any answer?
0
 
jimyXCommented:
> "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
 
marrowyungAuthor Commented:
"Yes, but you need to check, that might differ from version to another."


tks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 17
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now