3

I was wondering how to handle money in a SQL database to maintain consistency. I presume the tables would look something like below.

Consider the following scenario: if two players are playing then the money they wagered has been deducted from both of their balance_usd and added to pot_usd. But suppose the server goes down in the middle of the hand. Consistency of the database here presumably would mean that the money in pot_usd should be returned to the two players balance_usd effectively canceling the hand. It doesn't seem that transactions alone can solve this problem. How should the backend of a poker server be architected to handle this?

table user
    user_id
    balance_usd

table game
    game_id
    pot_usd
    ...
user782220
  • 10,677
  • 21
  • 72
  • 135

2 Answers2

0

To cope with that eventuality, you could have a table of CurrentGameStakes with fields for the playerID and the amount staked. Then when the hand is finished, you transfer the total of the stakes to the winner, and if a game doesn't complete for some reason, you return the staked amount to each player.

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • Does this mean on recovery a special sql script needs to be manually run to find the orphaned games and return the staked amount. That is, there is no avoiding having special recovery code. – user782220 Nov 14 '12 at 09:37
  • How often do you expect the games not to be completed? Fairly infrequently, one would hope. You could schedule the script to run on server startup? – podiluska Nov 14 '12 at 09:40
0

You'll need to keep track of balance_usd and pot_usd at the start of the game. If the game is cancelled then just restore these values. If it runs to completion then clear the records from the table you were using to track them.

Dave Richardson
  • 4,880
  • 7
  • 32
  • 47
  • 1
    This won't work if the player is playing multiple hands at multiple tables. If the hands go down then there will be multiple "starting balances" to choose from. – njr101 Nov 14 '12 at 09:35
  • I agree, though that's not a feature mentioned in the question. – Dave Richardson Nov 14 '12 at 09:36