Three types of concurrency control:
Pessimistic concurrency control: a record is unavailable to users while somebody else is accessing it. i.e. from the time the record is fetched until it is updated in the database. The way this is implemented is, the moment someone fetches a record, a lock is acquired on that table and no other user is allowed to access the same table until the lock is released.
Optimistic concurrency control: a record is unavailable to other users only while the data is actually being updated. The way this is implemented is, the update examines the record in the database and determines whether any changes have been made. Attempting to update a changed record results in a concurrency violation and hence an exception.
Last in Wins: a record is never unavailable to the users. Every time the user updates the record is simply saved without checking for updates on the record. This can potentially overwrite any changes made by other users since you last refreshed the records. This is the easiest to implement and the most dangerous to use.
Implementation details:
Pessimistic concurrency control (PCC):
It can be implemented with or without using database locks. It‘s usually implemented on the application side. Database locks are available in very limited DBMS and hence it‘s a good idea not to use database locks.
Most of the applications today have a multi-layered architecture and have a separate layer for database access. PCC is mostly implemented in the data access layer or the service layer. We can maintain a static list of tables, which are currently been accessed. Before processing any request for a record, we check in this list. If the table is present in the list, then we throw an exception, else add it to the table and fetch the data. There is an alternative to the static table. One can use lock-bits at the table record level and maintain them in a session pool.
The static list or the lock-bits can be cleared when the user leaves the page under program control. This is done to free the locks as soon as possible, but there is no guarantee that this will occur.
As the user can leave the browser or the site at any moment, we have to clear the list or lock-bits stored are the session pool on Session End. And as a final countermeasure, we might have a demon running on the server cleaning old locks.
We have to keep in mind that an application that holds locks for long periods is not scalable.
Last in Wins
This concurrency control can be implemented without having to do anything. This is the default behavior. But this might be unacceptable in some circumstances if different users start to access the same records frequently.
Optimistic concurrency control (OCC)
In OCC, several users can access a screen for the same record, but only the first one to save it will succeed. The rest would get concurrent change exception.
OCC is implemented based on the record version. The record version could mean all the columns of the record or a unique state code. For the unique code purpose we can use a GUID string or a date-time value. I‘m not too sure which one to use. But these are some points:
The GUID is not portable if we want to port the code to other platforms.
The GUID uses 40 bytes of binary data instead of the 8 bytes used by the date.
The date-time tells us when the record was last updated.
Example:
User A fetches a record from the database along with its version, more on this later.
User B fetches the same record from the database, also with its version and writes the updated record back to the database, updating the version number.
User A “tries” to write the record to the database, but as the version on the database is different from the version hold by user A, the write fails, leaving user B‘s changes intact.
It‘s always favorable to use OCC. But there might be situations where we have a long transaction, which spans over several screens. The user might not want to get concurrent change exception at the end of the updation. In these cases, it‘s better to use PCC and let the user know upfront about any possible locks.
