Managing Concurrency for Oracle 23ai Web Applications

Concurrency is the capability to perform many functions at the same time. Oracle provides concurrency by allowing many end-users to access the database simultaneously. A concurrency management method must ensure that each update transaction does not overwrite the updates of other users.

Concurrency management for Oracle 23ai web applications requires a fundamentally different mindset than traditional client-server applications. Because web applications are inherently stateless, a user does not hold a dedicated database connection open while viewing a webpage. The database session used to fetch data is immediately returned to the ORDS connection pool once the page renders. This structural difference makes traditional pessimistic locking — holding exclusive locks while the user reads a page — impossible to implement correctly in a web application.

Lock contention arises when one transaction attempts to read or write data — a field, row, table, or schema — that has been locked by another transaction process. Lock contention causes long response times for web users and is best diagnosed using Oracle wait event data in the Automatic Workload Repository (AWR) or Active Session History (ASH), not general-purpose log files.

Oracle prevents lock contention escalation by holding locks for the duration of SQL UPDATE and INSERT transactions, and by supporting SELECT FOR UPDATE to lock rows before an update. For in-house transactions over secure, persistent network connections, this works reliably. When using the web as a front-end, however, connections are terminated frequently — and terminated connections create lock pool resource exhaustion.

Oracle has excellent support for concurrency and performance in OLTP systems. The key locking features relevant to web application concurrency are:

Nonescalating Row-Level Locking

Oracle locks only the rows a transaction works on and never escalates these locks to page-level or table-level locks. In some databases, row locks escalate to page locks when enough rows on a page are locked — causing false lock contention where users wanting to work on unlocked rows are blocked by locks that escalated to a higher granularity level. Oracle's nonescalating model eliminates this class of contention entirely.

Terminated connections are a serious performance problem specific to web applications. When a web client disconnects from Oracle while holding row locks — because the browser was closed, the session timed out, or the network dropped — the locks may remain in the Oracle shared pool waiting for a task that will never resume. This creates two compounding problems:

1. The shared pool memory becomes clogged with zombie row locks from sessions that no longer exist.
2. Access to Oracle rows is blocked by unnecessary locks held by disconnected sessions, degrading response time for all active users.

In traditional client-server systems, a programmer uses SELECT ... FOR UPDATE to explicitly lock a row prior to an update. This places exclusive locks at retrieval time and holds them until the transaction commits or rolls back. In the following SQL, an exclusive lock is placed on the target row and no other task can update that row until the operation completes:

SELECT *
FROM   employee
WHERE  emp_name = 'Gould'
FOR UPDATE OF salary;

While this works in a closed network with persistent connections, it fails structurally in a web application. The database session is returned to the ORDS connection pool immediately after the page renders. The lock either disappears with the released session or — worse — persists indefinitely if the web client disconnects before the transaction completes. The fundamental rule for Oracle 23ai web development is: do not hold SELECT FOR UPDATE locks across page views. Transactions must be scoped to a single HTTP request.

Since a row cannot be locked while the user views the page, optimistic locking is the correct concurrency strategy for Oracle 23ai web applications. Optimistic locking assumes concurrent edits might occur but checks for conflicts only at the moment of the update — not at the moment of display.

Checksum / Hash

Generate a hash of the row data when it is queried. When the user submits changes, compare the hash of the current database row with the original hash. If they differ, another user changed the data and the update is rejected. Oracle APEX implements this automatically under the hood for all APEX form pages.

VERSION_ID Column

Add a VERSION_ID number column to tables requiring optimistic locking. Every UPDATE increments the version. The UPDATE WHERE clause includes the original version ID:

UPDATE employee
SET    salary = :new_salary,
       version_id = version_id + 1
WHERE  emp_name = 'Gould'
AND    version_id = :original_version_id;

If the UPDATE affects 0 rows, a concurrent modification occurred — another user updated the row between the display fetch and the submit. The application reports the conflict and prompts the user to refresh and retry with current data.

ORA_ROWSCN

Leverage Oracle's internal System Change Number (SCN) at the row level to detect whether a row has changed since it was queried. The table must be created or altered with ROWDEPENDENCIES enabled. This approach requires no application-side version column — the SCN serves as the implicit version identifier.

Lock-free reservations are a marquee Oracle 23ai feature that directly addresses the hot-row bottleneck — the case where many concurrent web users update the same row simultaneously, such as decrementing an inventory count or crediting a bank balance. In prior Oracle versions, 100 concurrent web users updating the same inventory row would queue 100 transactions waiting for the exclusive row lock — a massive serialization bottleneck.

In Oracle 23ai, a column can be declared RESERVABLE:

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 — for example, "reserve 1 unit from inventory as long as the total remains greater than or equal to zero." 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 by any transaction.

A fundamental rule of Oracle 23ai web development: one HTTP request equals one database transaction.

ORDS autocommit: when ORDS receives a POST or PUT request, it maps the call to a database operation. If the call succeeds without an exception, ORDS issues an automatic COMMIT. If a PL/SQL error is raised, ORDS issues an automatic ROLLBACK. Developers should not write explicit COMMIT statements inside web-facing PL/SQL procedures — let ORDS manage the transaction boundary to ensure database consistency aligns with the HTTP response code.

CLIENT_IDENTIFIER for auditing and VPD: because any pooled connection might handle the next HTTP request from the same user, standard PL/SQL package variables and session temporary tables do not persist reliably across requests. To track which user initiated a database operation — for unified auditing or Virtual Private Database policies — the web tier must set CLIENT_IDENTIFIER at the start of each HTTP request and clear it at the end:

-- Set at the start of each HTTP request
DBMS_SESSION.SET_IDENTIFIER('user_id_from_session_token');

-- Clear at the end of each HTTP request
DBMS_SESSION.CLEAR_IDENTIFIER();

The problems of lock pool resource exhaustion and database deadlocks from web application disconnects have driven the development of several alternatives to traditional shared and exclusive locks in Oracle 23ai:

1. Optimistic locking with version columns or checksums — the standard approach for most web application update scenarios, as described above.
2. Oracle 23ai lock-free reservations (RESERVABLE columns) — for high-concurrency numeric update scenarios: inventory counts, account balances, and counters.
3. Commit immediately after SELECT — for read-only display scenarios where display values do not need protection against concurrent modification, issue a COMMIT immediately after the SELECT to release implicit locks. This reduces lock pool utilization and eliminates deadlock potential on read paths.
4. ORDS sessionless transactions (Oracle 23ai) — ORDS supports sessionless REST transactions where the database session is released back to the pool between REST calls within the same logical transaction, further reducing session count requirements for high-concurrency web workloads.

The next lesson examines specific tuning techniques that implement these alternative locking and concurrency strategies.