   Storing in Memory or Query on Need
  I'm left with a decision that I think should be decided early on in development.I know that frequently changed data such as x, y, velocity, etc.. should be kept in memory and every so often copied into the player's database (MySQL or file) to prevent dataloss.But whatabout the infrequent data such as money (which would only happen when a player picks-up/drops or buys/sells). Would it be best to refrain from loading it into memory and just read it from the database when needed and immediately save it to database when changed?   
  This has been covered before. Basically you want to set up atomic transactions with your database so that scenarios such as:Player 1 and Player 2 trade.Database removes items from Player 1's inventory// DB crash!// Database inserts items into player 2's inventory// Database removes items from Player 2's inventory// Database inserts items into Player 1's inventoryYou have to think of these like bank transactions. Don't make half a query that could result in a problem if not fully completed.If it was me for every item uses and trade I'd make a change to the database. Since you're probably not going for large scale it's not much of a problem. If for some reason you had problems with that scalability wise you might set up priorities. Using a potion isn't important to record and such whereas destroying an object to generate new items in the game world is a critical transaction.I'd go with the naive solution until it becomes a problem honestly. Database nowadays are so fast you will probably never even notice things even if you were assaulting the server with queries.   ;  So you're saying that although storing the data in memory may be beneficial to performance..... the real issue is whether the server will crash.Okay, I think you helped me decide.Thanks for the reply!   ;  If you just think about the database as being a save game file used by an auto-save system, then things seem a lot simpler. You keep the game in memory but do an auto-save after important events. Database transactions help to ensure that either the whole of the event is saved or none of it is, meaning no inconsistencies.   ;  Just a quick point...Keep your transactions as short as possible! i.e. a stored procedure would be besti.e. in MS SQLBEGIN TRANSUPDATE playerSET cash = cash - {amount}WHERE player = {player2}AND cash > {amount}IF @@GETROWS = 1 THEN INSERT INTO player_item (player_id, item_id) SELECT player_id, item_id FROM player_item WHERE player_id = {player1} AND item_id = {item_id} IF @@GETROWS = 1 THEN  UPDATE player  SET cash = cash + {amount}  WHERE player = {player1}  COMMIT TRANS ELSE  ROLLBACK TRANS ENDELSE ROLLBACK TRANSENDThis uses @@ROWCOUNT that contains the number of rows modified( http://doc.ddart.net/mssql/sql70/globals_22.htm )First it decrements the buying players cash only if he has it.Then it moves they sellers item only if he has itThen it increments the cash of the sellerIf they buyer doesn't have the cash or the seller doesn't have the item it Rolls back the whole transaction.   ;Quote:You keep the game in memory but do an auto-save after important events. The problem with this is that you can get de-sync without meaning to, unless you checkpoint the entire zone state for each checkpoint (which might cause visible glitching/lag while it happens).Here's an example of what happens when you don't checkpoint the entire state at the same time atomically:1) Player A and Player B trade some items2) Player A leaves the Zone, which checkpoints Player A.3) Zone crashesThis means that the items player A gave away are not anywhere to be found, and the items that B gave away are now shown as owned by both A and B.Because of this problem, and other similar problems, you will typically want to split your state into two kinds: transactional, which is updated straight to the database with physical commit (all trade transactions for multiple players would commit as one transaction), and real-time, which is kept in memory and mutated by the simulation server, and only occasionally checked back into storage. The important part here is to know which state is which, and keeping that in mind when designing game rules and implementation!;  Yeah, you can't just save characters individually, you have to save all the participants in the activity, preferably atomically.   ;  Extra fun when the entities coordinating the transaction are on different server processes (on different core/on different machine) where the confirmation handshaking  has to pass across.   Virtual boundries of zones have been the source of numerous 'soft' errors and subject to the manipulations of players  (versus hard errors.One side or the other is delegated to build/execute the transaction to a common DB.The DB itself may be split into active/inactive sets to minimize its size (toallow faster sync point copying/mirror splitting) where another 'master' DB contains all the data but the 'working' DB has only active players in it (and any worldstate which itself might be 'cached' for the active parts -- if youhave a large world where many areas arent occupied for long lengths of time)