Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Let's say that I have two servers that simultaneously initiate a transaction on a MySQL 5.5 database using InnoDB. Both transactions do the same thing: Read a single row, then update the row based on its contents. I want to ensure that the two transactions run one after the other. That is, the second transaction should not be allowed to read the row until the first transaction is complete. Ideally, this would mean that the SELECT statement in the second transaction would wait until the first transaction has been committed, then return the updated values.

Is this possible? If not, is there a database that would be better suited to this task than MySQL?

If so, a related question: Suppose the first server crashed mid-transaction, so no COMMIT message was ever received. How would I go about setting a timeout after which the transaction would automatically roll back, so that the second transaction wouldn't be delayed forever? (Update: I've spun this off as a separate question.)

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

SELECT ... FOR UPDATE might satisfy your needs. See here.

share|improve this answer
    
Thanks, that got me on the right track, though I'd still like to see a more complete answer. –  Trevor Burnham Mar 1 '11 at 18:26
add comment

OK, I took Shane's suggestion and turned it into a test case. I've got a table called nsync with a row with id 1 and value foo. Then I run this transaction:

START TRANSACTION;
SELECT * FROM nsync WHERE id = '1' FOR UPDATE;
SELECT SLEEP(10);
UPDATE nsync SET stringy = 'bar' WHERE id = '1';
COMMIT;

and during the sleep interval I run

SELECT * FROM nsync WHERE id = '1' FOR UPDATE;

It works! The SELECT waits until the first transaction has completed, and returns the updated value bar.

Note that it's important that both SELECT statements use FOR UPDATE; if the first SELECT didn't use it, then the row wouldn't be locked; and if the second SELECT didn't use it, it wouldn't care about the lock.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.