Difference Between Pessimistic Approach and Optimistic Approach in DBMS

1. Pessimistic Approach :

A Pessimistic approach is an approach of concurrency control algorithms in which the transaction is delayed if there is a conflict with each other at some point of time in the future. It locks the database’s record for update access and other users can only access record as read-only or have to wait for a record to be ‘unlocked’. Programming an app with a pessimistic concurrency approach can be more complicated and complex in managing because of deadlocks’ risk. 

In the execution of pessimistic approach, the validate operation is performed first and  if there’s a validation consistent with the compatibility of the lock then only read, compute, and write operations are performed i.e., 

Validate -> Read -> Compute -> Write

In the pessimistic approach we use two common locking protocols:

1. Two-phase locking protocol
2. Timestamp ordering protocol

2. Optimistic Approach :

An Optimistic approach is an approach of concurrency control algorithms that are based on assumption that conflicts of operations on a database are rare. It is advisable to run these transactions to completion and to check for conflicts only before they commit also here there’s no checking to be done while the execution of transactions. This approach does not need any locking or time-stamping method. In an optimistic approach, a transaction is executed without any problems of restriction until transaction is committed. The optimistic approach allows the transactions to proceed in an unsynchronized way and also allows conflict checking at the end. This approach is also known as validation or certification approach.

During optimistic execution, we do only read and compute operations without validation and validate the transaction just before the right operation.

 Read -> Compute -> Validate ->  Write

Advantages of optimistic approach :

• In an optimistic approach transaction, rollback becomes very easy when the contacts are there.
• In an optimistic approach, you will not find any cascading rollback because it uses only the local copy of data and not database.

Disadvantages of optimistic approach :

• Using an optimistic approach for concurrency control can be very expensive as it needs to be rolled back.
• If there is a conflict between large and small transactions in this method, then only large transactions are rolled back repeatedly as they consist of more conflicts.

Difference Between Pessimistic Approach and Optimistic Approach :

Pessimistic Approach	Optimistic Approach
It locks records so that selected record for update will not be changed meantime by another user	It doesn’t lock the records as it ensures record wasn’t changed in time between SELECT & SUBMIT operations.
The conflicts between transactions are very large in this approach	The conflicts between transactions are less as compared to pessimistic approach.
The synchronization of transactions is conducted in start phase of life cycle of execution of a transaction	The synchronization of transactions is conducted in later phase or gets delayed in execution of a transaction
It is simple in designing and in programming.	It is more complex in designing and managing deadlocks’ risk
It has a higher storage cost	It has a relatively lower storage cost as compared to pessimistic approach
It has a lower degree of concurrency	It has a high degree of concurrency
This approach is found to use where there are more transaction conflicts	This approach is found to use where there are fewer transaction conflicts or very rare
The flow of transaction phases: Validate -> Read -> Compute -> Write	The flow of transaction phases: Read -> Compute -> Validate ->  Write
It helps in protecting the system from the concurrency conflict	It allows the conflict to happen
It is suitable for a small database or a table which has less records	It is suitable for a large database or has more records