Difference between revisions of "Optimistic database locking"

From BOARD18 Project WIKI
Jump to navigation Jump to search
(Initial page contents)
 
(Complete Initial page contents)
Line 1: Line 1:
== Overview ==
BOARD18 code has some limited previsions for preventing database damage.
BOARD18 code has some limited previsions for preventing database damage.


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


The other option, pessimistic database locking, is when a session takes an exclusive
The other option, pessimistic database locking, involves a session taking an exclusive
lock on the table or on a specific record so that no other session can
lock on the table or on a specific record in the table to prevent any other session from
start modifying that record. A pessimistic lock should be used when
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
a lot of sessions may be trying to access the same record at the same
time. EG: a "live" playing session. And, for example, players
time. EG: when BOARD18 is being used for a "live" playing session.  
having 2 windows open at the same time can make the problem worse.


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


Converting BOARD18 to pessimistic locking is not a task that I am
Converting BOARD18 to pessimistic locking is not a task that will be attempted at this time!
anxious to attempt 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. There are methods to avoid this problem. But until one of
them is implemented in BOARD18, the use of multiple tabs for the same session should be discouraged.

Revision as of 13:39, 16 May 2020

Overview

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. There are methods to avoid this problem. But until one of them is implemented in BOARD18, the use of multiple tabs for the same session should be discouraged.