Does this suffice, or do I have race conditions?

Question

I'm writing a strategy-kind of multi user game for the web. It has a playfield (X by Y squares) that I plan on serialize and store in a BLOB in a MySQL (innodb) database, one row for each ongoing game.

I now try to figure out a good way of keeping the database updated with any changes to the playfield, and at the same time finding a convenient solution to how to handle things that happen to the playfield in the time frame between loading the page and actually making a move.
I don't use AJAX.

There will be at most 20 players in each game, each player making between 1 and 10 moves in 24 hours, so it is a "slow" game.

My plan (so far) is to also store a kind of checksum for the playfield next to the blob and compare the databases state with the state loaded before trying to make changes to the playfield.

What I worry about is how to prevent race conditions.
Is it enough to:

1. Begin transaction.
2. load playfield from table
3. if checksum differs - rollback and update the users view
4. if checksum unchanged - update table and commit changes

Is the BEGIN TRANSACTION enough to block the race, or do I need to do something more in step 2 to show my intent to update the table?

Thankful for all advice.

Answer 1

No. You will need to issue a LOCK TABLES command for the tables you need to protect against conflicting updates. This would look something like...

LOCK TABLE my_table WRITE;

More details may be found here... http://dev.mysql.com/doc/refman/5.1/en/lock-tables.html

Don't forget to UNLOCK them afterwards!

Answer 2

If you use SELECT ... FOR UPDATE when you load the playfield from the database, it will block other selects until you commit or rollback the transaction.