0

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

Community
  • 1
Trevor Burnham
  • 364
  • 2
  • 3
  • 15

2 Answers2

5

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.

Trevor Burnham
  • 364
  • 2
  • 3
  • 15
2

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

Shane Madden
  • 112,982
  • 12
  • 174
  • 248