Oracle - Pessimistic Locking

In this post i want to discuss about how pessimistic locking can be implemented in Oracle

Transaction scopes

By default only Isolation levels which Oracle supports are

• Read Committed
• Serializable

However from performance stand point/scalability stand point Serializable in more costly. so we used Read committed

we used these spring jdbc for db integration and Platform Transaction Manager to manage transactions.

Phantom writes

In SQL Server- Isolation level Read Uncommitted is available. In such scenario always the latest value of data is used for updates

However in case of Read Committed, until Transaction is not committed, data not written/available for other threads

Consider table below

In above case, there is an account with identifier 8 and balance in account is 1000

Lets assume 2 transactions came at exact same instant

• Credit transaction of 100
• Debit transaction of 50

Net balance after both transactions should be ⇒ 1000+100 –50 = 1050

However in our case we used to observe below updates, some times

• Net Balance was 950 ( 100 Credit was lost)
• Net Balance was 1100 ( 50 Debit was lost)

Why was this happening ?

Since Isolation was Read committed, and both transactions came at same time, both threads read the value 1000, however the last one to update won

• If thread adding 100 wrote first and subtracting 50 wrote second then in that case balance was 950. (100 was lost)
• If thread subtracting 50 wrote first and adding 100 wrote second, in that case balance was 1100. (50 was lost)

How did we fix it ?

There is a technique called Optimistic locking, where in race conditions are managed at application level. We tried that with no success.

There is another feature- called Pessimistic Locking, where in record gets locked for duration of update a task gets completed

Lock gets released on Commit or RollBack

So when selecting the record for update we changed query as below

Select bal from balance where account=100 for update nowait=5

No sooner we made changes the query, faulty writes/updates vanished