For a game, I am trying to implement a scores table. According to game logic the scores usually change when one user completes one round which occurs every few minutes; but there is no guarantee two users will finish same time.
In order to not to recalculate scores every time that a user checks scores, I plan to hold score data in a persistent table which will be updated every time a round is completed.
I plan to implement this by
TRUNCATE TABLE scores; INSERT INTO SCORES SELECT ....
but my concern is what happens when two users finish at the same time. I pass the whole query in one connection (from ASP.NET, if that matters), but are two queries together considered as atomic? Or do you suggest that I must implement a manual transaction logic?
If you suggest transaction, putting is it enough to enclose the query with "BEGIN TRAN...END TRAN" and do you suggest MyISAM or InnoDB for that 'scores' table?
My MySQL version is 5.0.92-log if that matters.
Two queries are atomic only if they're performed inside a transaction. And even then they'll only be atomic for the rest of the world. Inside the transaction it's still two completely separate operations.
Transactions won't protect you from two+ users doing operations on the data set. You'll still have to establish locks on the relevant tables to prevent concurrent access.