Web Applications   «Prev  Next»
Lesson 9Alternative concurrency mechanisms for Web applications
ObjectiveImplement alternative locking for a Web application

Locking Alternatives in Oracle RDBMS: A Comprehensive Guide

Which alternative to 1) shared locks and 2) exclusive locks should I consider when working with an Oracle RDBMS?
Oracle RDBMS, known for its sophisticated concurrency and locking mechanisms, offers a range of alternatives to traditional shared and exclusive locks, ensuring efficient and concurrent data access while preserving data integrity. Let's explore the alternatives to both shared and exclusive locks in the context of Oracle:

Alternative to Shared Locks

Read Consistency using Multi-Version Concurrency Control (MVCC)

Oracle RDBMS employs MVCC to allow high concurrency with a minimal amount of contention. Instead of applying shared locks on read operations (which can block writers), Oracle provides a consistent view of the data as of the beginning of the operation.
  • Non-blocking Reads: Multiple transactions can query data without blocking writers or other readers.
  • Consistent View: Each transaction sees a snapshot of data from a specific point in time, ensuring read consistency.
  • Reduced Contention: By eliminating shared locks for readers, contention is minimized, which can significantly improve performance in high-concurrency scenarios.

2. Alternative to Exclusive Locks

Optimistic Locking

While exclusive locks ensure that once a transaction modifies data, no other transaction can access or modify it until the first transaction completes, such a mechanism can be too restrictive, leading to contention. An alternative is optimistic locking.
  • Versioning Mechanism: With optimistic locking, the application reads a record, noting its version. When the application later tries to update or delete that record, it checks the version. If it hasn't changed, the operation proceeds. If it has, the operation is retried or aborted.
  • Reduced Lock Time: Locks, if needed, are held for a minimal amount of time, reducing the chance of contention.
  • Application Handling: The application logic handles conflicts, deciding whether to abort, retry, or merge changes.

Row-level Locking

Instead of exclusively locking a whole table or page, Oracle offers fine-grained, row-level locking.
  • Granular Control: Allows multiple transactions to lock different rows of the same table simultaneously, even if they are in the same data block.
  • Decreased Contention: By locking only specific rows affected by a transaction, other transactions can continue to work with the table, reducing overall contention.

Automatic Deadlock Resolution

Deadlocks, a situation where two or more competing actions wait for the other to finish, can be problematic. Oracle RDBMS automatically detects and resolves deadlocks by rolling back one of the statements involved, freeing up resources.

While shared and exclusive locks have their places in transactional systems, Oracle RDBMS provides sophisticated alternatives like MVCC and optimistic locking to address potential contention and scalability issues. By understanding and judiciously using these mechanisms, developers can optimize performance and ensure data integrity in high-concurrency Oracle environments.

Tuning techniques to replace the Oracle Locking Scheme

There are several reasons, described in the previous lesson, to consider alternatives to shared and exclusive locks. There are two common tuning techniques that are used to replace the Oracle locking scheme. The first is to issue all updates with a WHERE clause.

Update with a WHERE clause

Let's look at an example of this in the series of images below.

Issue updates with where

Gould's row contains the performance_flag, salary and name columns.
1) Gould's row contains the performance_flag, salary and name columns.
The UPDATE required is sent with a WHERE clause.
2) The UPDATE required is sent with a WHERE clause.
Each of the columns, are specified in the update command to ensure that they have not changed since the transaction began.
3) Each of the columns, are specified in the update command to ensure that they have not changed since the transaction began.
This re-validates all of the existing new values from the original display.
4) This re-validates all of the existing new values from the original display.
If any of these values have changed since the row was initially retrieved, Oracle willl reject the transaction, not perform the update, and will return a non-zero error status.
5) If any of these values have changed since the row was initially retrieved, Oracle willl reject the transaction, not perform the update, and will return a non-zero error status.
The database will return with a NOT FOUND SQL code.
6) The database will return with a NOT FOUND SQL code.

  1. Gould's row contains the performance_flag, salary and name column.
  2. The UPDATE required is sent with a WHERE clause.
  3. Each of the columns, are specified in the update command to ensure that they have not changed since the transaction began.
  4. This re-validates all of the existing new values from the original display.
  5. If any of these values have changed since the row was initially retrieved, Oracle willl reject the transaction, not perform the update, and will return a non-zero error status.
  6. The database will return with a NOT FOUND SQL code.


Note: As a further enhancement, the WebServer application can interpret the NOT FOUND error code returned after this transaction, re-retrieve the updated record, and present it to the user with its new value.
The next lesson discusses date time stamp.