Oracle’s lock-free reservations, introduced in version 23c, address the issue of concurrent transaction blocking when updating specific columns. This functionality allows multiple transactions to modify the same row without traditional locking mechanisms.
Here’s a breakdown of how it works:
- Reservable Columns: You designate specific numeric columns within a table as “reservable.” These columns are typically used for quantities, inventory levels, or account balances.
- Check Constraints: A check constraint is applied to the reservable column to define valid update operations. Typically, this allows additions or subtractions using the
+or-operators. - Lock-Free Updates: Transactions can update the reservable column concurrently without acquiring locks. The database manages these updates ensuring data consistency.
Here are some benefits of using lock-free reservations:
- Improved Concurrency: Multiple transactions can update the same row simultaneously, enhancing overall database performance.
- Reduced Locking: No traditional row-level locking occurs for reservable column updates, eliminating bottlenecks caused by waiting transactions.
- Automatic Rollback: If a transaction fails, the reservable column updates are automatically rolled back, maintaining data integrity.
However, there are limitations to consider:
- Data Type: Only numeric data types are supported for reservable columns.
- Limited Operations: Updates are restricted to additions or subtractions using the reservable column itself as the operand.
- No Indexes: Reservable columns cannot be indexed.
Syntax to Create Reservable Column at Table Creation:
CREATE TABLE ACCOUNT(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(10),
BALANCE NUMBER RESERVABLE CONSTRAINT MINIMUM_BALANCE CHECK (BALANCE >= 200));Overall, lock-free reservations are a valuable feature for improving concurrency in scenarios where frequent updates occur on specific numeric columns.
Basic GoldenGate replication support for Reservable columns:
- Support for replication from reservable columns on the source to the reservable columns on the target with built-in (delta) conflict resolution for reservable columns
- Support for replication from reservable columns on the source to non-reservable column on the target
- Support for replication from non-reservable columns on the source to reservable columns on the target
For more details and implementation guidance, you should refer to the official Oracle documentation: https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/using-lock-free-reservation.html
https://blogs.oracle.com/coretec/post/lock-free-reservation-in-23c
alexlima.com 