Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

Difference Between Pessimistic Approach and Optimistic Approach in DBMS

  • Last Updated : 29 Jun, 2021

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. 

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

 

In the execution of pessimistic approach, the validate operation is performed first and  if there’s a validation consistent with tcompatibility 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 userIt 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 approachThe 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 transactionThe 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 costIt has a relatively lower storage cost as compared to pessimistic approach
It has a lower degree of concurrencyIt has a high degree of concurrency
This approach is found to use where there are more transaction conflictsThis 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 conflictIt allows the conflict to happen
It is suitable for a small database or a table which has less recordsIt is suitable for a large database or has more records

 

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!