so, i'm currently writing match-making code for my game, i'm using apache, php, and a mysql database. the game is not real-time with opponents, so the database is used for maintaining a persistent game, essentially a client well ask for the game's current state, and then the user well play if the opponent has taken their turn.

essentially what i'm doing for match-making is a request comes into my server, and i ask the database for any games that don't currently have an opponent, i select the first one, add myself into the game, and then start the game(this is completly automated, their is no choosing a game from the client). if no game is available, a new game is created, and waits for an opponent to add themselve's into the game.

my problem is that i can foresee problems like this:

client A asks for list of games available, client B then asks for a list of games(which is the same list client A has), client A add's itself to the first game avaialble, client B add's itself to the first game avaiable, now client B is actually in the game, but client A thinks it's in the game.

any ideas on an probable approach that would be possible?

transaction eh?, i'll start looking that up, if i understand correctly, does it essentially cause blocking for mysql commands? what happens if another client is in the process of updating a diffrent row in the same table(such as updating it's own game data, does this command become blocked as well?)

i guess some research is in order, thanks for the info=-)

ok, so after a bit of research, and messing around, it doesn't appear transactions are what i'm after.

while transactions are nice for working on data with the ability to rollback in an instance, it doesn't appear that it causes blocking, so client B needs some method of knowing that a transaction is in place.

however, i think i came up with a solution:

"update games set bID=".myid." where id=".gid." and and bID is null;" if(affected_rows<=0){ //already been written to. }else done=true;

i tried this by instancing about 3 users, and setting a target time for each one to actually write their value into the update, 2 users go 0 for affected rows, and one got 1, so it seems to work(although i don't know if it'll scale well, but i guess we'll find out).

You absolutely want to use a transaction of some sort for this.

The problem you're having is that you're involving the client in decisions the client has no authority over.

The easiest solution is to change your logic to a single request:

"Place me in an open game, and return the game instance and whether I'm the first or the second player in that game."

The server can then, within a transaction, do all the logic of figuring out what game to put the player in, and whether to create a new game.

Note that you will only ever have 0 or 1 games waiting for a new player, if the algorithm is what you describe in your initial post.

There *are* ways to do distributed transactions like you initially suggest, using things like version numbers, or delta change requests, but in that case, you always have to be prepared for a transaction to fail on the client. Basically, the client being told "I told you that game X was free, but it's no longer free -- try something else." The problem with that is that, in high contention, you'll end up with a lot of churn and very unpredictable service times. A server-side atomic request is much simpler.