sql-serveroraclelockingoptimistic-lockingpessimistic-locking

Could there be a deadlock when using optimistic locking?


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.


Solution

  • 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.