MySQL transactions for browser based games - do you use them?

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[CygnusX]

I've always had to do 2 updates:

$Gold = 100;
mysql_query("Update Users
                  Set Gold = Gold - '$Gold' where UserID = '1',
                  Set Gold = Gold + '$Gold' where UserID = '2'");

If there is a different, more efficient method... I'd be very interested to know.

[chrisjenkinson]

I use Doctrine.

You should always use transactions if you are doing something which involves more than one SQL query. In some cases using a transaction will be faster than not using one as the database engine can optimise the commit.

Chris

[Zeggy]

I don't use mysql, but I do use transactions, yes.

[Sunchaser]

I use mysql with transaction, but only for critical updates (like your money transfer example)

[Marek]

Note that when using transactions, you have to use a database engine that supports them in the first place.

On MySQL, you have to use InnoDB tables to support transactions. I think MyISAM will just ignore you if you ask it to start a transaction. InnoDB is better than MyISAM is various ways, although it's sometimes claimed that MyISAM is faster for some types of tables. It's a debate that keeps coming back.

[Mutant]

Every request to my server is in a transaction. That way, in the "unlikely" event that an error occurs, the DB isn't left in some weird state, which could cause further problems. (There's an argument for using sub-transactions in some places as well, but I haven't really got that sophisticated yet).

Note, transactions aren't the same as serialisation (though the two are related). If you're looking for protection from race conditions (e.g. a character getting attacked simultaneously from 2 different opponents, both killing him... but which one gets the xp from the kill?) then you need to do some research on serialisation. But that usually comes at a fairly expensive performance cost.

[gnoh]

I think you mean synchronization, serializing is something entirely different.

A transaction with a lock and some sort of versioning would protect you from race conditions on the database.

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[Chris]

It's all not important, because in practice you need "IF gold>=price THEN gold=gold-price ELSE give_me_error" in one query (no clue if this is even doable). The delay when the gold disappear from one player and appear on another is not an issue. The only issue is when the gold is spent twice.

[Mutant]

I think you mean synchronization, serializing is something entirely different.

A transaction with a lock and some sort of versioning would protect you from race conditions on the database.

No, I was talking about serialisation through transaction isolation levels, e.g. http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html

"Serialisation" may have been the wrong term, but I'm not talking about doing locking (at least within the application).

Edit: not that I'd necessarily recommend that approach. Although if you're in a transaction, you at least have 'repeatable reads' generally, so nothing else will read the things you're writing until you commit. That doesn't protect you against everything, e.g. say you have an item in a shop, and two players buy the them at the same time. If their transactions start close together, the first may be in the process of updating the item row so that they own it, and the second won't see that until the commit so will think it's still available, which could result in a dupe, or the first player losing the item. You can increase the isolation level, but that can be problematic and slow... plus it's still not bullet proof for some edge cases.

Anyway, my recommendation would be to use transactions at repeatable read level, and code around any other race conditions.