Could there be a deadlock when using optimistic locking?

W

4

7

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.

Wager answered 14/8, 2016 at 21:32 Comment(0)

G

7

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.

Gravely answered 14/8, 2016 at 21:50 Comment(6)

Thank you! Therefore Oracle Database never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. Does this mean that Deadlock is another difference Optimistic Concurrency from the Multi-Versioning Concurrency? But at the moment, when Optimistic Concurrency at finish - read-check-modify the row, do we use the lock? Or it can be only one lock per transaction at one moment, so cann't be deadlock. – Wager 14/8, 2016 at 22:5

@Wager - I'm not sure that I understand the followups. In order to update a row, you have to lock it. The difference between optimistic and pessimistic locking is whether you pessimistically lock the row just in case you might update it or whether you optimistically wait until you know you want to update it to obtain the lock. You could write an application that did every update as a separate transaction. That would reduce deadlocks but it would be horrible for data consistency. – Gravely 14/8, 2016 at 22:21

Yes, thank you, that's what I wanted to know. Can we say that the optimistic locking reduces the likelihood of deadlock by reducing the consistency? And only if every update in a separate transaction, then the likelihood of deadlock is 0%, but with this the smallest consistency. Using a certain number of optimistic approach, we can achieve the necessary trade-off between deadlock and consistency. – Wager 14/8, 2016 at 22:46

@Wager - In Oracle, deadlocks are not a trade-off. Whether you are using optimistic or pessimistic locking, if you write your code correctly, you should never get a deadlock. In SQL Server, barring cases where locks get escalated which should be very rare in an OLTP system, you should never get a deadlock. If your application gets deadlocks, it is written poorly. – Gravely 14/8, 2016 at 23:7

I find it can be much harder to avoid deadlocks using a pessimistic locking scheme. With such a scheme, you generally lock the row exclusively when the user signals his intent to modify it (i.e., when he first edits any column in the row). Since you cannot control the order in which every user edits the data they see, you cannot guarantee that deadlocks will not occur. In an optimistic locking model, you don't exclusively lock anything until the the saves their work. At that point, you know all the rows affected and can lock them in a consistent order (e.g., by ID ascending or something). – Armipotent 15/8, 2016 at 19:34

I'm not sure if this is correct. My understanding of optimistic locking is that it doesn't lock resources. Am I wrong? – Mediatize 27/12, 2019 at 15:52

P

8

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.

Povertystricken answered 1/12, 2016 at 11:19 Comment(4)

Thank you! But classical definition of optimistic concurrency control which implemented by using hardware transactional memory - can it have property composability? en.wikipedia.org/wiki/… – Wager 1/12, 2016 at 12:56

Also it seems that the least likelihood of deadlocks when using "serialazable isolation level", when any of modifications will be visible only after transaction commit, and Tom Kyte said " that serializable is one way to achieve high throughput and faster response times" from which it can be concluded that there is fewer collisions of threads. asktom.oracle.com/pls/apex/… Is this true that by using OCC can be implemented in MVCC only serialazable isolation level, or also any other: Read-Committed, Repeatable-read, Snapshot? – Wager 1/12, 2016 at 12:56

This should be the correct answer: OCC is deadlock free by definition. – Liv 26/6, 2018 at 10:39

Why does "The classical definition of optimistic concurrency control is not easy to implement practically." ? Are there any references explaining this? Thanks – Liv 26/6, 2018 at 10:52

G

7

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.

Gravely answered 14/8, 2016 at 21:50 Comment(6)

Thank you! Therefore Oracle Database never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. Does this mean that Deadlock is another difference Optimistic Concurrency from the Multi-Versioning Concurrency? But at the moment, when Optimistic Concurrency at finish - read-check-modify the row, do we use the lock? Or it can be only one lock per transaction at one moment, so cann't be deadlock. – Wager 14/8, 2016 at 22:5

@Wager - I'm not sure that I understand the followups. In order to update a row, you have to lock it. The difference between optimistic and pessimistic locking is whether you pessimistically lock the row just in case you might update it or whether you optimistically wait until you know you want to update it to obtain the lock. You could write an application that did every update as a separate transaction. That would reduce deadlocks but it would be horrible for data consistency. – Gravely 14/8, 2016 at 22:21

Yes, thank you, that's what I wanted to know. Can we say that the optimistic locking reduces the likelihood of deadlock by reducing the consistency? And only if every update in a separate transaction, then the likelihood of deadlock is 0%, but with this the smallest consistency. Using a certain number of optimistic approach, we can achieve the necessary trade-off between deadlock and consistency. – Wager 14/8, 2016 at 22:46

@Wager - In Oracle, deadlocks are not a trade-off. Whether you are using optimistic or pessimistic locking, if you write your code correctly, you should never get a deadlock. In SQL Server, barring cases where locks get escalated which should be very rare in an OLTP system, you should never get a deadlock. If your application gets deadlocks, it is written poorly. – Gravely 14/8, 2016 at 23:7

I find it can be much harder to avoid deadlocks using a pessimistic locking scheme. With such a scheme, you generally lock the row exclusively when the user signals his intent to modify it (i.e., when he first edits any column in the row). Since you cannot control the order in which every user edits the data they see, you cannot guarantee that deadlocks will not occur. In an optimistic locking model, you don't exclusively lock anything until the the saves their work. At that point, you know all the rows affected and can lock them in a consistent order (e.g., by ID ascending or something). – Armipotent 15/8, 2016 at 19:34

I'm not sure if this is correct. My understanding of optimistic locking is that it doesn't lock resources. Am I wrong? – Mediatize 27/12, 2019 at 15:52

A

1

No. In optimistic locking, you optimistically assume that the "locked" keys won't change before the transaction is committed. If the "locked" keys are changed before the transaction is committed, the transaction is reverted and tried again. There are no deadlocks since optimistic locking is 100% non-blocking. I even considered optimistic locking to be safer than pessimistic locking sometimes due to this.

Angry answered 7/8, 2022 at 2:27 Comment(0)

F

0

I can think of a scenario theoritically. Not sure, how this is handled practially:

Txn 1:  begin ->Write(A)  ->Read(B) ->Commit
Txn 2: begin ->Write(B) ->Read(A) ->Commit

Assume both txn's are running in parallel in single database and A, B are rows of DB

In above case Txn 1 is waiting for Read(B) but since txn 2 is not completed yet B still has write lock from txn 2. Similarly txn 2 is waiting for Read(A) but since txn 1 is not completed yet A still has write lock from txn 1.

Fairway answered 22/7 at 15:1 Comment(0)

Recommended topics

Hot tags