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

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.

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.