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