Alternative Concurrency Mechanisms for Oracle 23ai Web Applications

Oracle 23ai offers a range of alternatives to traditional shared and exclusive locks, ensuring efficient concurrent data access while preserving data integrity. In high-concurrency web applications — where hundreds of users may simultaneously read and update the same rows — traditional locking causes contention, long wait times, and throughput degradation. The right concurrency mechanism depends on the workload pattern.

Scenario	Recommended Approach	Why
High-contention numeric updates (inventory, balances)	Lock-Free Reservations	Eliminates blocking for concurrent reservations
Web/REST/JSON applications	Optimistic locking + JSON Duality Views	Lock-free, document-friendly
General high concurrency	Optimistic locking + short transactions	Reduces lock duration
Queue-like processing	SELECT FOR UPDATE SKIP LOCKED	Non-blocking dequeue
Mixed workloads	MVCC + selective FOR UPDATE	Default Oracle strength
Prioritized workloads	Priority Transactions (23ai)	Auto-rolls back lower-priority blockers

Oracle has used Multi-Version Concurrency Control (MVCC) since Oracle 7. Rather than applying shared locks on read operations — which would block writers — Oracle provides each query with a consistent view of the data as of the beginning of the statement or transaction. Undo segments store the before-images of changed rows, allowing readers to reconstruct the data as it existed at their query start SCN.

• Non-blocking reads: multiple transactions query data without blocking writers or other readers. A reader never waits for a writer; a writer never waits for a reader.
• Consistent view: each transaction sees a snapshot of data from a specific point in time — the SCN at statement start (statement-level read consistency) or transaction start (transaction-level read consistency via SET TRANSACTION READ ONLY).
• Reduced contention: by eliminating shared locks for readers, contention is minimized in read-heavy web workloads. This is Oracle's foundational concurrency advantage over databases that implement reader-writer locking.

MVCC combined with short transactions and proper indexing eliminates the need for explicit shared lock management in most Oracle 23ai web application scenarios.

Exclusive locks ensure that once a transaction modifies a row, no other transaction can access or modify it until the first transaction completes. For web applications where users read data, consider their changes for an indeterminate period, and then submit — holding an exclusive lock across that interval is impossible. The database session and HTTP connection are long gone by the time the user submits the form.

Optimistic locking defers conflict detection to the moment of update rather than the moment of display:

VERSION_ID Column

The application reads a record and notes its version number. When the user submits changes, the UPDATE WHERE clause includes the original version value. If the version has not changed, the update proceeds and increments the version. If the version has changed — another user modified the row while this user was reading — the UPDATE affects 0 rows and the application detects the conflict via SQL%ROWCOUNT.

ORA_ROWSCN

Leverage Oracle's internal System Change Number at the row level to detect concurrent modification without an application-maintained version column. The table must be created or altered with ROWDEPENDENCIES enabled. The SCN serves as the implicit version identifier.

JSON Relational Duality Views (Oracle 23ai)

Optimistic locking is especially powerful with Oracle 23ai JSON Relational Duality Views, which provide lock-free document-style updates while maintaining relational underpinnings. The ETAG generated by a Duality View read serves as the optimistic version token — if the ETAG has changed at update time, the update is rejected without any lock having been held.

Row-Level Locking

When some locking is required, Oracle provides fine-grained row-level locking rather than table or page locking. Multiple transactions can lock different rows of the same table simultaneously, even rows in the same data block. This granular control dramatically reduces contention compared to page-level or table-level locking used by some other database systems.

Automatic Deadlock Resolution

Oracle automatically detects and resolves deadlocks by rolling back one of the statements involved and returning ORA-00060 to the application. The application retries the rolled-back statement. Oracle's deadlock detection is cycle-based and operates without external DBA intervention.

Lock-free reservations are the standout new concurrency feature in Oracle 23ai for high-contention numeric columns — inventory counts, account balances, counters, and seat availability. Multiple transactions can concurrently reserve values on the same column without blocking each other, even with uncommitted changes from other transactions in flight.

Oracle maintains a reservation journal for RESERVABLE columns and applies compensation logic at commit time. Declare a reservable column with a CHECK constraint:

ALTER TABLE inventory
MODIFY units_available RESERVABLE CONSTRAINT units_non_negative
CHECK (units_available >= 0);

Instead of locking the row, Oracle issues an asynchronous reservation. The transaction proceeds without holding a row lock, allowing massive concurrent throughput on highly contested data. If the constraint cannot be satisfied — inventory would go negative — the reservation is rejected without any row lock being held. This eliminates the serialization bottleneck that previously made high-throughput e-commerce and financial workloads Oracle's most challenging concurrency scenario.

Priority Transactions are a new Oracle 23ai feature that assigns priority levels — HIGH, MEDIUM, or LOW — to transactions. When a lower-priority transaction holds a row lock that blocks a higher-priority transaction, Oracle automatically rolls back the lower-priority transaction to allow the higher-priority one to proceed.

This is particularly useful for web applications where background batch jobs (LOW priority) should yield immediately to interactive user requests (HIGH priority) that contend for the same rows.

Two common tuning techniques replace the Oracle exclusive locking scheme for web applications. The first — and most widely applicable — is to issue all updates with a comprehensive WHERE clause that re-validates the original display values at the moment of the update. The second is SELECT FOR UPDATE SKIP LOCKED for queue-style workloads.

The UPDATE WHERE pattern embeds the original display values into the WHERE clause of the UPDATE statement. Oracle re-validates all original values at update time before applying the change — effectively implementing optimistic locking at the SQL level without a VERSION_ID column or application-managed version token.

The diagram consolidates six sequential steps into one reference view:

1. Original display: the row displayed to the user includes emp_name, performance_flag, and salary — the values visible when the page rendered.
2. Submit UPDATE: the web request sends one UPDATE statement with a WHERE clause containing all three original column values.
3. Re-check values: the original display values are embedded in the WHERE clause so Oracle compares them against the current row state at update time.
4. Validate row: Oracle evaluates the WHERE clause — if all three values still match, the row qualifies for the update.
5. Detect conflict: if any value changed since the row was displayed, the WHERE clause matches 0 rows and the UPDATE affects 0 rows.
6. Report result: the application checks SQL%ROWCOUNT. If 0, a concurrency conflict occurred — the application re-retrieves the updated record and presents it to the user with its current value.

Use single quotes for character string literals in Oracle SQL. The correct UPDATE WHERE form:

UPDATE employee
SET    salary = salary * 1.1
WHERE  emp_name = 'Richards'
AND    performance_flag = 9
AND    salary = 120000;

Plain SQL UPDATE does not raise NOT FOUND when 0 rows are matched — it silently succeeds with 0 rows affected. The application must check SQL%ROWCOUNT in PL/SQL or the row count returned by the JDBC driver:

UPDATE employee
SET    salary = salary * 1.1
WHERE  emp_name = 'Richards'
AND    performance_flag = 9
AND    salary = 120000;

IF SQL%ROWCOUNT = 0 THEN
   RAISE_APPLICATION_ERROR(-20001,
      'Record was modified by another user. Please refresh and retry.');
END IF;

For web applications implementing queue-like processing — task queues, job queues, notification processing — SELECT FOR UPDATE SKIP LOCKED provides non-blocking concurrent dequeue. Rather than waiting for locked rows, SKIP LOCKED bypasses them and processes the next available unlocked row:

SELECT task_id, task_data
FROM   work_queue
WHERE  status = 'PENDING'
AND    ROWNUM = 1
FOR UPDATE SKIP LOCKED;

Multiple ORDS worker threads process the queue concurrently without any thread waiting on another. Each thread skips rows locked by other threads and picks up the next available task. This pattern replaces message queue middleware for many Oracle-native task processing scenarios.

The next lesson examines date and timestamp management in Oracle 23ai web applications — covering timezone handling, timestamp precision, and Oracle 23ai improvements to date/time operations.