Optimistic database locking

From BOARD18 Project WIKI
Jump to navigation Jump to search


BOARD18 code has some limited previsions for preventing database damage.

Unfortunately these provisions only consist of optimistic database locking. Optimistic database locking involves checking if the record was updated by some other session after it was read and the update transaction was committed. This method works best when it is unlikely that a lot of users will be accessing and updating the record at the same time. EG: when BOARD18 is being used for play by email games.

The other option, pessimistic database locking, involves a session taking an exclusive lock on the table or on a specific record in the table to prevent any other session from modifying that record. A pessimistic lock should be used when a lot of sessions may be trying to access the same record at the same time. EG: when BOARD18 is being used for a "live" playing session.

Pessimistic locking provides better integrity in those situations. However it is slower. And management of the lock is harder. If the application fails to manage the lock properly, the session may encounter deadlocks. Deadlocks are very hard to debug.

Converting BOARD18 to pessimistic locking is not a task that will be attempted at this time!

The Problem With Multiple Windows

Players having 2 windows, or 2 tabs, open at the same time can cause problems for BOARD18 as it is currently coded.

BOARD18 uses a session variable called the SESS_UPDATE_COUNTER to support optimistic database locking. Session variables are stored on the server, but in order to know which session belongs to which user the server sends a cookie to the browser, which the server later uses to pick the right session. This cookie applies to the whole site, so the same session is used for all requests from the same browser. This is true even if they are not from the same tab/window.

Therefor players are advised to not have 2 browser windows or tabs open at the same time for the same game in the same client machine.