Solved

NOWAIT signal for a MysQL table

Posted on 2015-02-05
29
468 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
  • 17
  • 6
  • 5
29 Comments
 
LVL 8

Assisted Solution

by:Ahmed Merghani
Ahmed Merghani earned 50 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 450 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now