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.

Community
  • 1
  • 1
Alex
  • 12,578
  • 15
  • 99
  • 195

3 Answers3

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.

Mootaz Elnozahy
  • 81
  • 1
  • 1
  • Thank you! But classical definition of optimistic concurrency control which implemented by using hardware transactional memory - can it have property composability? https://en.wikipedia.org/wiki/Software_transactional_memory#Composable_operations – Alex Dec 01 '16 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. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:12684560615138 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? – Alex Dec 01 '16 at 12:56
  • 1
    This should be the correct answer: OCC is deadlock free by definition. – mljrg Jun 26 '18 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 – mljrg Jun 26 '18 at 10:52
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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 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. – Alex Aug 14 '16 at 22:05
  • 1
    @Alex - 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. – Justin Cave Aug 14 '16 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. – Alex Aug 14 '16 at 22:46
  • 2
    @Alex - 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. – Justin Cave Aug 14 '16 at 23:07
  • 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). – Matthew McPeak Aug 15 '16 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? – Florian Wicher Dec 27 '19 at 15:52
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.

Jessie Lesbian
  • 1,273
  • 10
  • 14