Pessimistic locking and Optimistic locking

Given problem
Solution of locking in RDBMS
Pessimistic locking
Optimistic locking
How to choose Pessimistic locking or Optimistic locking
Wrapping up

Given problem

In single tier architecture, we have all things in one computer. It will include user interface, backend business logic, and database. With this architecture, normally we will only have one user to use this application. So, our database only takes care about containing data.

But from two tier architecture, n-tier architecture, we have multiple users in our system. It means that at the same time, we will have multiple requests to interact with our database. There are many problems will raise in this context, especially resolving conflicts of read/write operations between multiple requests at the same table or rows in database.

Belows are some problems that we have to deal with.

Lost update

Assuming that we have Person 1 and Person 2 that interact with our database such as Employee table.

Time	Person 1	Person 2
T1	Read row with id = 1
T2		Read row with id = 1
T3	Write this changed row to DB
T4		Write this changed row to DB

We can find that Person 1’s update of the database has been lost by some changes of Person 2. So, we can called it as the lost update syndrome.

If it happens in the blocks of database, we can take the negative consequence of it.

Uncommitted dependency problem

This problem is related to the Commit/Rollback mechanism of the transaction management.

For example, assuming that we have two transaction T1 and T2.

T1	T2
SELECT * FROM customer;
	UPDATE customer SET name = ‘Johnson’; Rollback;
SELECT * FROM customer;

Based on the above example, we can find that the result of the second query of T1 transaction is different than the first query’s result of T1 transaction.

Because T1 transaction can see the uncommitted data from T2 transaction.

Inconsistent analysis problem

Solution of locking in RDBMS

To overcome this problem, we need to protect our data from multiple requests that interact the same table or row. So we need locking.

Belows are some level lockings that we need to know:

Database level locking

This level locking does not use frequently in reality because it impacts to our system when encountering multiple session at the same time.

But it is useful in some specific cases:
- Our DBMS want to upgrade to a new version.
Table level locking

The table level locking will be used in cases:
- When we have some modifications in all table.
  
  For example: Use in some DDL operations such as ALTER, CREATE, DROP.
In MySQL 8.0, InnoDB engine uses intention locks, are table-level locks that indicate that which type of lock (shared or exclusive) a transaction requires later for a row in a table.
- An intention shared lock (IS) indicates that a transaction intends to set a shared lock on individual rows in a table.
- An intention exclusive lock (IX) indicates that a transaction intends to set an exclusive lock on individual rows in a table.
Intention locks do not block anything except full table requests (for example, LOCK TABLES … WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.
Page or Block level locking
Row level locking

There are two types of row-level lockings in RDBMS:
- Shared lock permits a transaction that hold a lock to read a row.
- Exclusive lock permits a transaction that hold a lock to update or delete a row.
Column level locking

In this article, we only need to consider the strategies of row-level locking:

Pessimistic locking
Optimistic locking

Pessimistic locking

Introduction to Pessimistic locking

The idea of pessimistic locking is that it assumes that the concurrency conflicts usually happen in our database.

Pessimistic locking is a locking strategy that it will lock a record at the first accessing time. It ensures that only one user can do something with this record at any given time.

Pessimistic locking exists until the transaction commits or rolls back.
Benefits and Drawbacks
- Benefits
  - Processes know immediately when a locking violation occurs, rather than after the transaction is complete.
- Drawbacks
  - It is not fully supported by all databases.
  - It consumes extra database resources because we need to remain the connection between the application and the database.
  - It can be caused deadlocks.
  - It decreases the concurrency of connection pooling when using the server session, which affects the overall scalability of your application.
Some implementations of Pessimistic locking
- Two phase locking
Pessimistic locking with the other RDBMS
- Oracle TopLink, an ORM framework, implements pessimistic locking by using database row-level locks, such that attempts to read a locked row either fail or are blocked until the row is unlocked, depending on the database.
- MySQL utilizes the auto commit mode by default. It means that after we completed an update operation, it will commit data. So if we need to use Pessimistic locking, we must change this auto commit mode.
  
  Beside above statements, MySQL cluster uses pessimistic record locking.
When to use
- when the chance of conflict is high.
- when the cost of locking is less than the cost of rolling back transaction.

Optimistic locking

Introduction to Optimistic locking

The idea of optimistic locking is that it assumes that the concurrency conflicts rarely happen in our database, so we don’t need to lock it immediately after a user enters a record.

Optimistic locking is a strategy locking that will lock record when a user commited or saved, not at the first time - accessing it. It is a way that a database checks whether the original record and the updated record can be conflicted or not by using signals.

These signals can be calculated by some approaches:
- Use an additional column with integer type or timestamp type to save the version of a record.
  
  It means that the database will check the version column of the original record and the updated record. If it is equal together, this updated operation will be processed successfully. Otherwise, it will be aborted.
- Use the checksum or hash function to get a value based on the original record.
  
  In this approach, a database will need a checksum or hash function to calculate from the current record. It takes too much time or not depends on the algorithm that we choose.
So, belows are some operations of Optimistic locking:
- No locks when data is read
- After the data is saved, our database will check for conflicts.
  
  If the data was changed by another transaction since it was read earlier, it usually does this by checking some version numbers on the data or by checking the individual fields.
  
  If there was a change, the transaction won’t complete, then it will be rolled back.
- This locking will notify the user, show them the updated values from the database and give them a chance to overwrite those values with their own changes.
Benefits and Drawbacks
- Benefits
  - It doesn’t require us to lock up the database resource.
  - It improves concurrency because a record is not actually locked when at first time, it is accessed by a transaction. So, the multiple applications can read a record.
  - Without locking database when reading, writing a record, it prevents deadlocks.
- Drawbacks
  - With this locking, some applications need to take time to calculate the version of each column.
Some implementations of optimistic locking
- The timestamp-based concurrecy control
  
  The timestamp-based algorithm assigns a single (more correctly one for each kind of operation, read and write) timestamp to each object, denoting the last transaction that accessed it. So each transaction checks during the operation, if it conflicts with the last transaction that accessed the object.
- The multi-version concurrecy control
  
  The multi-version approach maintains multiple versions of each object, each one corresponding to a transaction. As a result, the multi-version approach manages to have fewers aborts than the first approach, since a potentially conflicting transaction can write a new version, instead of aborting in some cases. However, this is achieved at the cost of more storage required for all the versions.
Optimistic locking with the other RDBMS
- In Oracle 10g or above, it uses the hidden ORA_ROWSCN based on the internal Oracle System Clock (SCN) that is as same as checksum, but it does not take the calculation cost when updated. In order to use this functionality, our table will need to setup in ROWDEPENDENCIES mode.
- In MS SQL server, it uses READ_COMMITTED isolation level by default, Optimistic locking will allow readers and writers threads to work concurrently, but writers will block write until the blocking thread commits.
When to use
- when the chance of conflict is low
- when the cost of rolling back is lower than the cost of locking data

How to choose Pessimistic locking or Optimistic locking

Pessimistic locking
- Use this locking in the stateful or the long connection between the server and the database.
  
  –> So, we need to increase the connection pool of the system, and affect to the system’s performance when there are the large amount of connection.
  
  –> Increase software and hardware to adapt it.
Optimistic locking
- Take the high storage of database because we must utilize an additional version column or using checksum function –> affect to the performance of the system.
  
  With a table that contains fields such as LONG, RAW, BLOB, the checksum function isn’t useful.