Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1046
  • Last Modified:

Mutex table howto

Hi all,

How do i create a mutex table in a database and ensure consistency using standard SQL.

Scenario:

Multiple clients must be able to perform the following sequence:

set mutex
read value from table
process value
update value in table
release mutex

Unique client id's are not available.

Preferable only the statements select, update, insert and delete is used. The solution must work with at least oracle and mySQL.

Regards
/S?ren




0
svanggaard
Asked:
svanggaard
1 Solution
 
Brendt HessSenior DBACommented:
Try using the FOR UPDATE option on your SELECT statement, e.g.:

SELECT * From MyTable WHERE ID = 1 FOR UPDATE

This locks the rows, disallowing updates from other processes until they are released.

Both MySQL and Oracle support this syntax.
0
 
svanggaardAuthor Commented:
The SELECT FOR UPDATE is not supported by mySQL.

mySQL only supports locking an entire table.

Regards

/S?ren
0
 
Brendt HessSenior DBACommented:
Hmmm... according to the documentation at MySQL.Com, this is supported...

"If you are using FOR UPDATE on a table handler with page/row locks, the examined rows will be write locked. "

Researching more, this means that you will need the InnoDB version of MySQL.  If you cannot use this for some reason, then you are right - you will need an external Mutex handler of some sort.

See:  http://www.mysql.com/doc/I/n/InnoDB.html
for more info on InnoDB tables and their use.

0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
JgouldCommented:
Question has been closed as per recommendation

JGould-EE Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now