What is a real world example where you would choose to use pessimistic locking?

Question

I have read many articles on when to use Optimistic vs. Pessimistic locking and my basic understanding is :

• Optimistic locking is more scalable so probably use this if possible
• Use pessimistic locking when it does not ever make sense to allow concurrent updates

I haven't been able to find any useful examples of when it would be smart to choose pessimistic locking (it seems that optimistic is usually preferred).

Would be very helpful if someone could answer for a specific example: say we have a Credit Card/account table and we want to authorize a transaction. Integrity is very important and I don't see why it would ever be useful to allow concurrent updates. Is this an example where we would use pessimistic locking?

Answer 1

When Read Committed Snapshot Isolation level is set to OFF it uses shared lock. Shared lock is placed on rows which are being read by current transaction and it prevent other transactions from updating the rows which are being read until current transaction reads the rows. Lock on the row is released immediately once the row is read. Similarly this shared lock is placed on the rows which are being updated by current transaction and it prevents other transactions from reading the rows until it updates the rows and commit/rollback the transaction.

Other than row level lock, shared lock uses page level lock and table level lock. Page level lock is released once the next page is read and table level lock is released once the statement is finished.

Shared lock is also known as pessimistic read since it doesn't allow other transactions to update the rows which are being read. Shared lock is also known as pessimistic write since it doesn't allow other transactions to read the rows which are being updated.

When Read Committed Snapshot Isolation level is set to ON it uses snapshot of last committed data to the underlying table before the statement in the transaction starts. It doesn't use any locking mechanism here. Its optimistic read since it allows transactions to update the records which are being read by current transaction. Its pessimistic write since it exclusively locks the records on the snapshot it took prior to start of update statement and other transactions read last committed data on the underlying table.

I've created salary table and inserted 1 million rows in it. Set Read Committed Snapshot OFF and one transaction is reading all the rows from it and almost at the same time another transaction will update all the rows of the table.It will take time for second transaction to update all the rows (Pessimistic Read) whereas if you execute both the transactions when read committed snapshot is ON then the second transaction will take only the update query execution time without any waiting time of first transaction(Optimistic Read).

T1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

SELECT * FROM SALARY

COMMIT TRANSACTION

T2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

UPDATE SALARY SET AMOUNT = 7500

COMMIT TRANSACTION

Set Read Committed Snapshot OFF and run below two transactions. first T1 and then T2 almost at the same time.

T1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

SELECT * FROM SALARY WHERE ID IN (1,2,3,4,5)

WAITFOR DELAY '00:00:10'

SELECT * FROM SALARY WHERE ID IN (1,2,3,4,5)

COMMIT TRANSACTION

T2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRANSACTION

UPDATE SALARY SET AMOUNT = 6500 WHERE ID IN (1,2,3,4,5)

WAITFOR DELAY '00:00:30'

COMMIT TRANSACTION

You'll see that second select statement of T1 transaction will wait until T2 commits the data. Its because T2 acquired shared lock prior to execution of update query.once it will commit the data T1 select statement will execute. Update statement here is an example of pessimistic write.

pessimistic write is there when you set SNAPSHOT ON on the above two transactions but since second select statement of T1 can take snapshot of last committed data it will not wait for T2 to complete and update statement of T2 also takes the snapshot of last committed data and apply exclusive lock to update the records.

pessimistic write is there when you set SNAPSHOT ON on the above two transactions but since second select statement of T1 can take snapshot of last committed data it will not wait for T2 to complete and update statement of T2 also takes the snapshot of last committed data and apply exclusive lock to update the records.

For optimistic write you can refer snapshot isolation level.

Answer 2

Pessimistic locking prevents lost update or write skew. So, use pessimistic locking:

• If lost update or write skew occurs.
• Or if there are some problems if lost update or write skew occurs.

Optimistic locking doesn't prevent lost update or write skew. So, use optimistic locking:

• If lost update or write skew doesn't occur.
• Or, if there are no problems even if lost update or write skew occurs.

In MySQL and PostgreSQL, you can use SELECT FOR UPDATE as pessimistic locking.

You can check my answer of the lost update and write skew examples with optimistic locking(without SELECT FOR UPDATE) and pessimistic locking(with SELECT FOR UPDATE) in MySQL.

Answer 3

This is almost a duplicate of this past question: Optimistic vs. Pessimistic locking

Pessimistic locking is good for cases when you want to guarantee you lock multiple resources atomically. This helps to avoid deadlocks.

Optimistic locking relies on non-atomic locking, in the sense that you may need to lock multiple resources during your transaction, and if you acquire the locks optimistically, you do that one at a time, so there's a race condition with respect to other concurrent transactions.

Pessimistic locking has some risks too. You might lock some of the resources needlessly, if it turns out you didn't need to lock them after all. It could depend on the logic of your transaction, but you had to lock them as part of the atomic lock request just in case you did need them.