Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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:

UPDATE nsync SET stringy = 'bar' WHERE id = '1';

and during the sleep interval I run


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

Your Answer


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.