What is the difference between optimistic and pessimistic concurrency control

Next Page ». Describe optimistic and pessimistic concurrency. Optimistic concurrency: It assumes that resource conflicts between multiple users are very unlikely to occur and thus allows transactions to execute without any locking mechanisms on the resources. It is only while changing the data that a check is made on resources if any conflicts have occurred.

Pessimistic concurrency: Assumes that resource conflicts between multiple users are very likely to occur and hence locks resources as they are used by transactions for the duration of the transaction. A transaction is assured of successful completion unless a Deadlock ocurrs. Posted on March 24, by vladmihalcea. At the Networking course in college, I learned that there are two ways of dealing with conflicts or collisions:.

If the cost of retrying is high, we could try to avoid the conflict altogether via locking, which is the principle behind how pessimistic locking works. In the diagram above, both Alice and Bob will acquire a read shared lock on the account table row upon reading it. Because both Alice and Bob hold the read shared lock on the account record with the identifier value of 1 , neither of them can change it until one releases the read lock they acquired.

This is because a write operation requires a write exclusive lock acquisition, and read shared locks prevent write exclusive locks. MySQL also uses pessimistic locking by default when using the Serializable isolation level and optimistic locking for the other less-strict isolation levels. Optimistic Locking allows a conflict to occur, but it needs to detect it at write time. This can be done using either a physical or a logical clock. However, since logical clocks are superior to physical clocks when it comes to implementing a concurrency control mechanism, we are going to use a version column to capture the read-time row snapshot information.

So, when reading the account record, both users read its current version. However, when Bob changes the account balance, he also changes the version from 1 to 2. 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.

Attention reader! 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.

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. In an optimistic approach, you will not find any cascading rollback because it uses only the local copy of data and not database.