Difference between revisions of "Optimistic database locking"
(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 | 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 | 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, | The other option, pessimistic database locking, involves a session taking an exclusive | ||
lock on the table or on a specific record | 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 | a lot of sessions may be trying to access the same record at the same | ||
time. EG: a "live" playing session | time. EG: when BOARD18 is being used for a "live" playing session. | ||
Pessimistic locking provides better integrity | Pessimistic locking provides better integrity in those situations. However it is slower. And | ||
management of the lock is harder | 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 | 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. |
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.