Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

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 ?
SOLUTION
Avatar of Ahmed Merghani
Ahmed Merghani
Flag of Sudan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marrowyung
marrowyung

ASKER

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
it seems that it expect no parameter as the input.
What is yor MySQL version ?
5.5.28
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.
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

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
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.
you mean can't do it in singal query ?
should we use Name_Locked instead of in_use ?
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".
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 ?
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
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?
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".
"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 ?
>   "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?
"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.
> "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.
"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 ?
again, so your example is, the table name is BlockAudit?
> "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.
"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.
any answer?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"Yes, but you need to check, that might differ from version to another."


tks.