Q+A: Understanding Optimistic Concurrency in Databases

I have struggled in this area, but have found no consolidated information in a single place. So I've tried to summarize everything here! I've done it in a Q&A format for best understanding.

Getting Started

ACID helps make concepts clear through question and answers. In this example, we will assume that lock-based RDBMS is being used. I will only talk about "read committed" and "read uncommitted" isolation levels because "read committed" is the most preferred isolation level set in the database. I mention threads in the context of Java.

To understand this post, you need to know the basics of Java, Hibernate, SQL, and RDBMS.

What is a transaction?

A transaction is a single threaded unit of work in which multiple operations either occur in a particular defined serial order or don't occur at all.

By default, in an RDBMS, does every statement execute inside some transaction?

Yes!

Does every statement execute in a separate transaction?

Well, it depends. If AutoCommit is set to true, then yes, it executes in a separate transaction.

What are the locks in RDBMS?

• Shared read lock: When a single row is read, a read lock is held. This allows another transaction from another thread to have a read lock on the same record — but no other transaction from another thread can impose a write lock to update it.

• Update lock: When an update lock is held, other threads can only acquire a shared read lock — not update nor an exclusive write lock.

• Exclusive write lock: An update lock is promoted to a write lock. I think this promotion is only possible when only one lock, which is this update lock (no other shared read lock), is locking this row. When a write lock holds a row, no other transaction from any other thread can hold any lock on that row.

Is reading a single row or writing to a single row with where clause atomic?

Answer: Yes. When the row is discovered with a binary search on the index, an update lock is acquired on the row before modifying it in case of writing to it. At the time of modification, the lock is updated to a write lock. No other transaction can modify it during this whole locking period. To be precise, after a row that qualifies the where clause is discovered, it is locked with an update lock so that other updating transactions cannot even discover it. In the case of read committed isolation, the lock is held until the transaction commits or rolls back.

In the case of reading a row, a read lock is held. No other transaction can modify it during the locking period. But simultaneous read is possible, as this lock is shared.

Are statements with nested queries atomic?

No. They are treated as separate statements in the same transaction.

Why is there a need for isolation levels?

They are used for concurrent multiple transaction scenarios to guarantee data consistency.

• Read uncommitted: This happens after Transaction T1 updates but does not commit the row (it may commit later or may roll back later depending on the scenario). Another transaction may read/modify the row by acquiring read/write lock. This results in a lost update.

• Read committed: This happens after Transaction T1 updates but does not commit the row (it may commit later or may roll back later depending on the scenario). Another transaction may not read/modify the row by acquiring read/write lock because T1 does not release the write lock (update is promoted to the write lock at the time of modifying) until T1 ends with a commit or rollback.

Where are the isolation levels applied?

In the database level or through a JDBC connection. A connection object is provided by the DBMS driver, which in turn is provided by the DBMS vendor.

Does ORM framework use the same JDBC connection object to set isolation level?

Yes!

Can the read committed isolation level alone guarantee bulletproof consistency in a concurrent scenario?

No. ORMs use optimistic version control, which jointly works with read committed isolation level set in the database to assure consistency.

How does Hibernate implement optimistic concurrency control?

Here's an example.

Say a version column is added to the database table Tbl and say Query1 and Query2 execute serially in transaction T1.

When an entity is loaded into a session, the following query takes place:

SELECT *
FROM Tbl
WHERE RowID = 1

Query 1

Following is the row loaded (as an entity object) as a result of the SELECT query:

RowID: 1
Col1: 10
VersionNo: 12

When an entity is updated in RDBMS, in the session, the following query takes place:

UPDATE Tbl
SET Col1 = 20, VersionNo = 13
WHERE RowID = 1 AND VersionNo = 12

Query 2

But if another transaction has committed the row with a higher version number (say, 13) before the UPDATE query takes place, the update fails and the transaction T1 rolls back. If the update is successful, then VersionNo is incremented by 1 and becomes 13. Following is the result if Query 2 is a success.

RowID: 1
Col1: 20
VersionNo: 13

How can deadlock be avoided?

Through transaction timeout.

And that's it! I hope that these answers proved helpful to you.