1. Introduction

When multiple users or threads access and modify the same data concurrently, data integrity becomes a serious concern.
For example, consider a banking system where two users try to withdraw money from the same account simultaneously — without proper locking, you could easily end up with inconsistent or lost updates.

To handle this, databases and ORMs like JPA (Java Persistence API) provide concurrency control mechanisms, primarily:

• Pessimistic Locking
• Optimistic Locking

Both aim to ensure data consistency and prevent conflicts, but they differ in when and how they detect conflicts.

---

2. The Need for Locking

Concurrency control is about ensuring that transactions are isolated from one another.

Without locking, you can encounter:

• Lost Updates – Two users modify the same record; one update overwrites another.
• Dirty Reads – A transaction reads uncommitted data from another transaction.
• Non-repeatable Reads – Data changes between two reads within the same transaction.
• Phantom Reads – A query returns different sets of rows on repeated executions.

Locks help avoid these issues by coordinating access to shared data.

---

3. What is Pessimistic Locking?

Concept

Pessimistic Locking assumes that conflicts are likely to happen.
Therefore, it locks the record immediately when it’s read for modification, ensuring no other transaction can update or delete that record until the lock is released.

Think of it as:

“I’ll lock this data now so that no one else can touch it while I’m working.”

This approach prioritizes data consistency over performance.

---

How Pessimistic Locking Works (Example)

Suppose two transactions try to update the same record:

1. Transaction 1
   • Reads row with ID = 100.
   • Locks it using a FOR UPDATE clause.
   • Updates the record and commits.
2. Transaction 2
   • Tries to read the same row FOR UPDATE.
   • Must wait until Transaction 1 commits or rolls back.

---

In Oracle Database

Oracle provides row-level locking using the SELECT ... FOR UPDATE statement.

Example:

SELECT * FROM accounts WHERE account_id = 100 FOR UPDATE;

This does two things:

• Locks the selected row(s).
• Prevents other transactions from acquiring the same lock until the current one commits or rolls back.

If another transaction executes the same query:

SELECT * FROM accounts WHERE account_id = 100 FOR UPDATE;

It will wait until the first transaction releases the lock.

You can also specify:

• FOR UPDATE NOWAIT → raises an error if the row is already locked.
• FOR UPDATE SKIP LOCKED → skips locked rows and continues with others.

---

In JPA/Hibernate

JPA provides LockModeType.PESSIMISTIC_READ and LockModeType.PESSIMISTIC_WRITE for pessimistic locking.

Example using EntityManager:

Account account = entityManager.find(Account.class, 100L, LockModeType.PESSIMISTIC_WRITE);
account.setBalance(account.getBalance() - 500);
entityManager.persist(account);

This translates to:

SELECT * FROM ACCOUNT WHERE ID = 100 FOR UPDATE;

and ensures no other transaction can modify or delete that row.

You can also specify locks using queries:

Query query = entityManager.createQuery("SELECT a FROM Account a WHERE a.id = :id");
query.setParameter("id", 100L);
query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
Account account = (Account) query.getSingleResult();

---

Types of Pessimistic Locks in JPA

Lock Type	Description
PESSIMISTIC_READ	Prevents data modification but allows other transactions to read.
PESSIMISTIC_WRITE	Prevents both reading and updating by others.
PESSIMISTIC_FORCE_INCREMENT	Similar to PESSIMISTIC_WRITE, but also increments the version number to force an update.

---

Pros and Cons

Advantages:

• Guarantees strong consistency.
• Prevents conflicts completely.

Disadvantages:

• Can cause deadlocks if not carefully managed.
• Reduces concurrency because others must wait.
• Not suitable for high-traffic systems.

---

4. What is Optimistic Locking?

Concept

Optimistic Locking assumes that conflicts are rare.
Instead of locking data in advance, it allows multiple transactions to read and modify data freely, but checks for conflicts at commit time.

Think of it as:

“I’ll work freely, but before saving, I’ll check if someone else changed this data.”

This approach prioritizes performance and scalability over immediate consistency.

---

How Optimistic Locking Works (Example)

1. Transaction 1 reads an account with balance = 1000, version = 1.
2. Transaction 2 reads the same row (version = 1).
3. Transaction 1 updates balance to 900 → version becomes 2.
4. Transaction 2 tries to update → checks version, sees mismatch (1 vs 2) → fails with OptimisticLockException.

Thus, no data inconsistency occurs.

---

In Oracle Database

Optimistic locking is implemented logically — there’s no direct SQL keyword like FOR UPDATE.
Instead, it’s achieved by maintaining a version column (or timestamp).

For example:

UPDATE accounts
SET balance = 900, version = version + 1
WHERE account_id = 100 AND version = 1;

If the WHERE clause affects 0 rows, it means someone else updated the row — and the update fails.

---

In JPA/Hibernate

JPA provides @Version annotation to implement optimistic locking.

Entity Example:

@Entity
public class Account {

    @Id
    private Long id;

    private Double balance;

    @Version
    private Integer version;

    // getters and setters
}

When you update this entity, Hibernate automatically adds a WHERE version = ? clause and increments the version.

Generated SQL Example:

UPDATE ACCOUNT 
SET BALANCE = ?, VERSION = VERSION + 1 
WHERE ID = ? AND VERSION = ?;

If the row count = 0 → OptimisticLockException is thrown.

---

Handling OptimisticLockException

You can catch and retry:

try {
    account.setBalance(account.getBalance() - 100);
    entityManager.persist(account);
} catch (OptimisticLockException e) {
    // Retry or inform the user
    System.out.println("Conflict detected, please refresh and try again.");
}

This allows the system to re-fetch data and retry the transaction gracefully.

---

Pros and Cons

Advantages:

• No need to lock rows early → high concurrency.
• Deadlock-free approach.
• Ideal for systems with more reads than writes (like CRMs or e-commerce).

Disadvantages:

• Possible update failures if conflicts occur.
• Requires explicit handling of OptimisticLockException.

---

5. Comparison: Pessimistic vs Optimistic Locking

Feature	Pessimistic Locking	Optimistic Locking
When conflict is detected	During transaction (immediate lock)	At commit time (version mismatch)
Lock type	Database row lock	Version/timestamp column
Performance	Slower due to blocking	Faster due to non-blocking
Use case	High-conflict environments	Low-conflict, read-heavy environments
Deadlocks	Possible	Not possible
Database support	SELECT FOR UPDATE	Implemented at ORM/application level

---

6. Choosing Between Pessimistic and Optimistic Locking

• Use pessimistic locking when:
  • Conflicts are frequent.
  • Data is highly sensitive (e.g., financial systems, ticket booking).
  • Immediate consistency is mandatory.
• Use optimistic locking when:
  • Conflicts are rare.
  • Application needs high scalability.
  • Performance and throughput are priorities.

---

7. Example Scenario: Bank Account System

Optimistic Locking

• Used when multiple users mostly read balances, not update them.
• If a rare simultaneous withdrawal occurs, one update will fail, and the user retries.

Pessimistic Locking

• Used for fund transfers or ticket reservations where even a small conflict can cause financial or logical errors.
• Locking ensures strict sequential updates.

---

8. Oracle Isolation Levels and Locking

Oracle supports several transaction isolation levels that interact with locking:

Isolation Level	Description	Lock Behavior
READ COMMITTED (default)	Prevents dirty reads	Locks only modified rows
SERIALIZABLE	Prevents non-repeatable reads and phantoms	Transactions execute as if serially
READ ONLY	No updates allowed	No locks acquired

Pessimistic locking (FOR UPDATE) can be combined with these to enforce stricter behavior.

9. Best Practices

1. Always use short transactions — hold locks for as little time as possible.
2. Use Optimistic Locking by default — it scales better for most web applications.
3. Use @Version carefully — ensure it’s updated on every modification.
4. Avoid unnecessary locking — only lock when you need to modify data.
5. Handle OptimisticLockException properly — retry or show a friendly message.
6. Use appropriate isolation level — Oracle’s READ COMMITTED is often sufficient with JPA.

---

10. Summary Table

Aspect	Optimistic Locking	Pessimistic Locking
Mechanism	Version field check	Database row lock
JPA Support	@Version	LockModeType.PESSIMISTIC_*
Oracle SQL	UPDATE ... WHERE version = ?	SELECT ... FOR UPDATE
Conflict Handling	Throws OptimisticLockException	Second transaction waits or fails
Performance	High	Lower (due to blocking)
Ideal For	Read-heavy, low-conflict systems	High-conflict, critical consistency