Could there be a deadlock when using optimistic locking?

Question

As is known, there are two locking strategy: Optimistic vs. Pessimistic locking

Pessimistic Locking is when you lock the record for your exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.

Also knonw, that Optimistic Concurrency Control is not the same as Multi Version Concurrency Control (Oracle or MSSQL-Snapshot/MVCC-RC): Optimistic vs Multi Version Concurrency Control - Differences?

But can occur deadlock between two transactions if used OCC(Optimistic Concurrency Control) in both?

Can we say that the optimistic locking reduces the likelihood of deadlock by reducing the consistency? And only if each update is in a separate transaction, then the likelihood of deadlock is 0%, but with this the smallest consistency.

Answer 1

I am afraid that you have to be very precise in your definition of optimistic concurrency control. In the classical definition by Bernstein, Goodman and Hadzilacos, optimistic concurrency control allows threads to "virtually" acquire the locks, proceed with the updates, and then check for consistency violation when the transaction tries to commit. If a consistency violation occurs, the transaction is forced to abort and is resubmitted. Under this definition, it is not clear how a deadlock can occur, since threads are "never" blocked waiting for a lock. The classical definition of optimistic concurrency control is not easy to implement practically. However, recent work on hardware transactional memory is opening some possibilities and shedding some perspective on this old problem.

Answer 2

Sure.

A deadlock simply means that thread A holds a lock that thread B is waiting on while B holds a lock that A is waiting on. If your application is not designed to lock resources in the same order everywhere, it's easy enough to deadlock regardless of your locking strategy.

Imagine that threads A and B both want to update a particular row in a parent table and in a child table. Thread A updates the parent row first. Thread B updates the child row first. Now thread A tries to update the child row and finds itself blocked by B. Meanwhile, thread B tries to update the parent and finds itself blocked by A. You have a deadlock.

If you had a consistent order for locking resources (i.e. always lock the parent before the child) in Oracle you won't get deadlocks regardless of your locking strategy. You generally won't get deadlocks in SQL Server but the potential for row-level locks to get escalated in SQL Server makes that less than certain.