Header Ads Widget

Responsive Advertisement

Optimistic Locking and pessimistic locking

Kartik Chandra Mandal October 29, 2015

 

In Oracle (and relational databases in general), Optimistic Locking and Pessimistic Locking are two approaches to handling concurrent access to data in a multi-user environment. They help manage conflicts that arise when multiple transactions try to read and write the same data simultaneously.

1. Optimistic Locking

Optimistic locking is based on the assumption that conflicts between transactions are rare. It allows multiple transactions to read the same data without locking it, but before updating the data, it checks if any other transaction has modified the data. If a conflict is detected, the transaction is rolled back, and the user may need to retry the operation.

How it works:

Ø  Read Phase: The transaction reads the data without acquiring any locks.

Ø  Update Phase: When the transaction attempts to update the data, the system checks whether the data has been modified by another transaction since it was read.

ü  If the data has been changed, the transaction is rejected (rolled back), and the application can retry.

ü  If no conflict is detected, the transaction proceeds with the update.

Example:

In Oracle, optimistic locking can be implemented by adding a version column to a table (often called a "timestamp" or "version number"). When updating a row, the application checks if the version has changed since it was first read:

sql

UPDATE employees

SET salary = salary + 1000, version = version + 1

WHERE employee_id = 101 AND version = :old_version;

 

If the version has changed (indicating that another transaction updated the row), the UPDATE statement will fail, preventing conflicting updates.

Pros:

Ø  Concurrency: High, as no locks are held during the read phase.

Ø  Performance: Suitable for scenarios where conflicts are rare, leading to better performance.

Cons:

Ø  Rollback: If a conflict is detected, the transaction is rolled back, leading to potential overhead.

Ø  Retries: Applications need to handle retries in case of conflicts.

2. Pessimistic Locking

Pessimistic locking assumes that conflicts are likely and takes a more cautious approach by locking the data when it is read, preventing other transactions from modifying it until the lock is released. This ensures that once a transaction acquires a lock on a row, no other transaction can change it until the lock is released.

How it works:

Ø  Locking Phase: When a transaction reads the data, it immediately locks the rows (either exclusively or in shared mode), preventing other transactions from modifying them.

Ø  Update Phase: The transaction can safely update the data without worrying about conflicts because other transactions are blocked from modifying the locked rows.

Ø  Commit/Rollback: After the transaction commits or rolls back, the locks are released.

Example:

In Oracle, you can use a SELECT FOR UPDATE statement to explicitly lock rows:

sql

SELECT employee_id, salary

FROM employees

WHERE department_id = 10

FOR UPDATE;

 

This locks the rows in the employees table, preventing other transactions from modifying or locking these rows until the transaction completes.

Pros:

Ø  Conflict Avoidance: Prevents conflicts by locking data early in the transaction.

Ø  Consistency: Ensures that once data is read, it will not be modified by others until the transaction completes.

Cons:

Ø  Concurrency: Low, as locked rows are unavailable to other transactions.

Ø  Deadlocks: Risk of deadlocks increases, especially in systems with complex transactions involving multiple resources.

Ø  Performance: Can lead to performance bottlenecks due to the locking mechanism, especially in high-traffic systems.

Key Differences

Aspect

Optimistic Locking

Pessimistic Locking

Concurrency

High (no locks during read)

Low (locks are held during the read phase)

Locking Behavior

Data is not locked until the update phase

Data is locked when it is read

Use Cases

Best when conflicts are rare

Best when conflicts are common

Risk of Deadlocks

No risk of deadlocks

Increased risk of deadlocks

Rollback Handling

May need to retry transactions on conflict

No need for retries (locking prevents conflicts)

Performance

Better in systems with low contention

Can cause delays in high-concurrency systems

 

 

We now have an improved concept in the Java application.

 

In Oracle, both Optimistic Locking and Pessimistic Locking can be implemented using Java with JDBC or frameworks like JPA (Java Persistence API). The main difference between the two approaches lies in how they handle concurrent data access, and their handling at the time of commit also differs. Here's how they work in a Java context when using Oracle databases:

1. Optimistic Locking in Java with Oracle (Using JPA)

Optimistic locking in Java is typically implemented with JPA (Java Persistence API) by using a version column (e.g., a timestamp or numeric version) to check whether the data has been modified before committing the transaction.

Steps:

  1. Read Data: The data is read without any lock.
  2. Perform Updates: The application modifies the entity.
  3. Version Check on Commit: Before committing, the version column is checked. If another transaction has modified the row and updated the version column, an exception is thrown, and the transaction is rolled back.

JPA Example (Optimistic Locking):

java

import javax.persistence.EntityManager;

import javax.persistence.EntityTransaction;

 

public void updateEmployeeSalary(EntityManager em, int employeeId, double newSalary) {

    // Start transaction

    EntityTransaction tx = em.getTransaction();

    tx.begin();

 

    // Fetch the entity using the EntityManager

    Employee emp = em.find(Employee.class, employeeId);

 

    // Update the entity's salary

    emp.setSalary(newSalary);

 

    // Commit the transaction

    try {

        tx.commit();

    } catch (javax.persistence.OptimisticLockException e) {

        // Handle optimistic locking failure (data has been modified by another transaction)

        tx.rollback();

        System.out.println("Optimistic Locking Conflict: " + e.getMessage());

    }

}

 

In the above code:

Ø  Version Check: If another transaction updates the Employee entity, it will increment the version field (which is typically annotated with @Version in the entity class). If the current transaction tries to commit with an outdated version, an OptimisticLockException is thrown.

Entity with Version Column:

java

@Entity

public class Employee {

    @Id

    private int id;

 

    private String name;

    private double salary;

 

    @Version

    private int version; // Version for optimistic locking

 

    // Getters and setters

}

 

At commit time, JPA will ensure that the version column in the WHERE clause of the UPDATE statement matches the current value in the database. If it doesn't, the commit fails, and the transaction is rolled back.

Commit Behavior:

Ø  If the version matches, the transaction proceeds to commit.

Ø  If the version is outdated, the transaction throws an OptimisticLockException, and the commit is rolled back.

2. Pessimistic Locking in Java with Oracle (Using JPA or JDBC)

Pessimistic locking locks the data when it is read to ensure that no other transaction can modify it until the current transaction is complete. In Java, this can be implemented using JPA or plain JDBC.

Pessimistic Locking with JPA (Using LockModeType.PESSIMISTIC_WRITE):

java

import javax.persistence.EntityManager;

import javax.persistence.EntityTransaction;

import javax.persistence.LockModeType;

 

public void updateEmployeeWithLock(EntityManager em, int employeeId, double newSalary) {

    // Start transaction

    EntityTransaction tx = em.getTransaction();

    tx.begin();

 

    // Fetch the entity and apply a pessimistic lock

    Employee emp = em.find(Employee.class, employeeId, LockModeType.PESSIMISTIC_WRITE);

 

    // Update the entity's salary

    emp.setSalary(newSalary);

 

    // Commit the transaction

    tx.commit();

}

 

In the above code:

Ø  Lock Acquisition: The LockModeType.PESSIMISTIC_WRITE ensures that when the Employee entity is fetched, an exclusive lock is obtained, preventing other transactions from modifying the same row.

Ø  Commit: The commit happens normally, but during the transaction, the locked rows cannot be modified by other transactions.

Pessimistic Locking with JDBC:

In plain JDBC, you can use SELECT FOR UPDATE to acquire a lock on the row:

java

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

 

public void updateEmployeeWithLockJDBC(Connection conn, int employeeId, double newSalary) throws SQLException {

    // Begin transaction

    conn.setAutoCommit(false);

 

    // Select the row with a FOR UPDATE lock

    String selectSQL = "SELECT id, salary FROM employees WHERE id = ? FOR UPDATE";

    PreparedStatement pstmt = conn.prepareStatement(selectSQL);

    pstmt.setInt(1, employeeId);

 

    ResultSet rs = pstmt.executeQuery();

 

    // If the employee exists, update the salary

    if (rs.next()) {

        String updateSQL = "UPDATE employees SET salary = ? WHERE id = ?";

        PreparedStatement updateStmt = conn.prepareStatement(updateSQL);

        updateStmt.setDouble(1, newSalary);

        updateStmt.setInt(2, employeeId);

        updateStmt.executeUpdate();

    }

 

    // Commit transaction

    conn.commit();

}

 

In the above code:

Ø  Lock Acquisition: FOR UPDATE locks the selected rows in the employees table until the transaction is either committed or rolled back.

Ø  Commit: The commit occurs after the UPDATE statement, releasing the lock.

Commit Behavior:

Ø  In pessimistic locking, once a row is locked, other transactions are blocked until the current transaction commits or rolls back. At commit, the lock is released.

Ø  If the commit fails for any reason, the lock is also released as part of the rollback.

Key Differences in Commit Behavior:

Aspect

Optimistic Locking

Pessimistic Locking

When is Lock Applied?

Lock is only applied at commit time (if data has changed)

Lock is applied immediately when data is read

Transaction Conflicts

Conflicts are detected at commit (if another transaction changed the data)

Other transactions are blocked from modifying the data once it's locked

Handling Conflicts at Commit

If conflict, an OptimisticLockException is thrown, requiring retry

No conflict at commit because other transactions are blocked earlier

Performance Impact

Higher concurrency, potential retry on conflict

Lower concurrency, no retries, but locks may block other transactions

 

Conclusion:

Ø  Optimistic Locking is useful when conflicts are rare, as it allows higher concurrency. However, conflicts are detected only at commit time, and retries may be necessary.

Ø  Pessimistic Locking is better when conflicts are expected, as it locks the data early, preventing other transactions from modifying it. This approach can reduce concurrency but ensures safe data updates.






Optimistic Locking and pessimistic locking
Optimistic Locking and pessimistic locking


Tags:

Post a Comment

0 Comments

Post a Comment