Heyho!
Tim Wood wrote:
That is what transactions are for, you need INNODB-Tables and "BEGIN"/"START TRANSACTION" and "COMMIT" (in combination with "SET AUTOCOMMIT=0"):Hi
I have a number of clients connecting to a DB in order to take jobs off a queue, mark them active, then run them. In pseudo code, each client executes the following sequence of queries:
a-- select test_id from tests where status=1 and priority < 11 order by priority
b-- update tests set status=2 where test_id = <result of query above>
< load,run test etc>
[ eg status==1 -> queued, status==2 -> active]
What happens in some cases is that tests can be marked active by more than one client. I'm guessing what is happening (if we have two clients C1, C2 ) is the following sequence of events in the DBMS:
C1a C2a C1b C2b
What's the best way to ensure that a single client executes both a and b atomically so that no other client can show up and execute an 'a' between another client's a and b ?
http://dev.mysql.com/doc/mysql/en/COMMIT.html
http://dev.mysql.com/doc/mysql/en/Transactional_Commands.html
http://dev.mysql.com/doc/mysql/en/InnoDB_transaction_model.html
This is one part of the ACID-Priniple: http://en.wikipedia.org/wiki/ACID
If your application does this in a multithreaded way beware of deadlocks (see the INNODB chapter above)!
Cheers Tim
HTH, Wolfram
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
