Oracle 23C database Architecture New Feature Lock-Free Reservations

 Hi All,

In this post i am going to share the one of the new Features in oracle database architecture .

1. Lock-Free Reservations 

####. Lock-Free Reservations

Lock-Free Reservations enables concurrent transactions to proceed without being blocked on updates of heavily updated rows. Lock-Free reservations are held on the rows instead of locking them. Lock-Free Reservations verifies if the updates can succeed and defers the updates until the transaction commit time.

Lock-Free Reservations improves the end user experience and concurrency in transactions.

Using Lock-Free Reservation

============================

This chapter explains how to use lock-free reservation in database applications.

Topics:

# About Concurrency in Transaction Processing

# Lock-Free Reservation Terminology

# Lock-Free Reservation

# Benefits of Using Lock-Free Reservation

# Guidelines and Restrictions for Lock-Free Reservation

######### About Concurrency in Transaction Processing

Transaction processing usually involves flat transactions with data updates that replace old values with new. Most applications store numeric and aggregate values, such as quantity-on-hand of products, bank account balances, stocks, and number of seats available. These numeric aggregate data, although referenced as a single entity, are based on one or more same or similar data. Such data involve subtraction or addition of the values rather than an assignment of the form “data ← value.” For example, a bank account balance is based on the debit and credit transactions that happen on the account.

Applications that provide inventory control, supply chain, financial or investment banking, stocks, travel, and entertainment operate on numeric aggregate data. A numeric aggregate data is generally identified as a “hot” resource because applications continuously and repeatedly read or update such data. The likelihood of many transactions concurrently accessing such data is high. In conventional locking protocols, such as two-phase locking (2PL), execution of concurrent transactions is serialized after a transaction initiates an update on a row. The initiated update is complete only when the transaction finalizes with a commit or a rollback. Serialization blocks other concurrent transactions from accessing the row until the transaction that initiated the update is completed. If you allow concurrent transactions to access data, you must control the transactions to preserve application correctness.

In long-running transactions such as microservices applications, a resource remains locked for an extended period, potentially making it a hot resource. The long-term resource locking limits concurrency. In microservices applications that offer services like trip booking services, you may have flight, hotel, and car bookings in long-running transactions. These transactions are typically handled as Sagas and span different services with databases holding locks until the Saga finalization.

Let us look at an example of an online shopping cart to understand how you would handle concurrency in a normal course. A shopping cart has items added to the cart before being sold. Your application must be able to handle multiple transactions at various states like: when a user puts an item into the cart, the item becomes unavailable to other buyers and yet unsold. For concurrent transactions, managing the inventory states and count becomes complex with multiple transactions adding items to the cart and checking out or abandoning the cart. The inventory or similar fields must be locked for a transaction much before a commit or rollback can change the quantity. Locking data for long periods prevents other concurrent transactions from accessing the item until the lock is released. Therefore, aiming for isolation and allowing the application to run transactions independently, limits concurrency.

For many business applications, blocking concurrent accesses to “hot” data can severely impact performance, reducing user experience and throughput. Applications can benefit if there is improved concurrency with reduced isolation while maintaining the atomicity, consistency, and durability properties of transactions. To improve concurrency, it is important to capture the state of a hot resource during a transaction lifecycle and enable data locking only when the resource value is being modified.

See Also:

Lock-Free Reservation for more information about how reservable columns are used to control concurrency in transaction processing.

####### Lock-Free Reservation Terminology

Numeric Aggregate Data

Hot Data or Hot Resource

Transaction Lifecycle

Compensation or Compensating Transaction

Reservable

Reservable Column

Reservable Update

Reservation Journal

Lock-free Reservation

Optimistic Locking

Saga

Numeric Aggregate Data

Data that involves subtraction (consume, decrement) or addition (replenish, increment) of the quantity (numeric data) rather than an assignment of the form “data ← value”. Operations on numeric aggregate data are commutative in nature.

Hot Data or Hot Resource

Data or a resource that receives high traffic from transactions requiring frequent reads and updates or a resource that is read or updated in a long-running transaction.

Transaction Lifecycle

The states in a business transaction as it transitions from its creation to its committed or rollback state.

Compensation or Compensating Transaction

A compensation or compensating transaction compensates (rolls back) for the already committed transactions of a saga if any other transactions that is also a part of the saga fails.

Reservable

Reservable is a column property that you can define for a column with numeric data type. A reservable column keeps journals of modification made to the column. These journals are used for concurrency control and auto-compensation.

Reservable Column

A column that contains a hot resource and is identified for lock-free reservation. These columns are declared with a reservable column property keyword.

Reservable Update

Numeric aggregate data updates made on a reservable column.

Reservation Journal

A reservation journal is a table associated with the user table that records the modification (increase or decrease by a delta amount with respect to the current value) in the reservable column.

Lock-free Reservation

Any reservable update is treated as a lock-free reservation, implying that the transactions making the updates to a qualifying row do not lock the row, but indicate their intention to modify the reservable column value by a delta amount. The operation of modification is recorded in a reservation journal. Lock-free reservations are transformed to the actual updates at the commit of the transaction.

Lock-free reservation ensures that the lock is obtained only at the time of commit to update a reservable column. Lock-free reservation is used for high-concurrency hot data and are suited for microservices transaction models because of its implicit compensation support.

Optimistic Locking

Optimistic locking is a concurrency control method that allows transactions to use data resources without acquiring locks on those resources. Before committing, each transaction verifies that no other transaction has modified the data that it last read.

Saga

A saga encapsulates a long running business transaction that is composed of several independent microservices. Each microservice comprises of one or more local transactions that are part of the same saga.

######## Lock-Free Reservation

Lock-free reservation provides an in-database infrastructure for transactions operating on reservable columns to:

Enable concurrent transactions to proceed without being blocked on updates made to reservable columns

Issue automatic compensations for reservable updates of successful transactions in an aborted saga

Here’s how lock-free reservation enables applications to include concurrency and auto-compensating features into their transactions:

Declaring a Reservable Column

You can use the reservable keyword to declare a reservable column when you create or alter a table. Lock-free reservation is offered on columns with numeric data types. To identify a potential reservable column, look for hot resources with numeric aggregate data that could benefit from improved concurrency.

Reserving a Transaction Update

When a transaction issues an update operation on a reservable column, the reservable update is placed as a lock-free reservation in a reservation journal. All updates issued on reversable columns are treated as lock-free reservation.

The transaction update does not lock the row (that has the reservable update) but indicate their intention to modify (add or subtract) the reservable column in the row by a delta amount. The modification amount is reserved and promised so that the transaction may proceed without waiting on other uncommitted, concurrent transactions that have made earlier reservations on the same row’s reservable column. The reservation enables other concurrent transactions to issue reservable updates to the same row.

The operation of modification (increase or decrease by the delta amount with respect to the current value of the reservable column) is recorded in a reservation journal. Instead of reading and writing the actual value of the reservable column, transactions issue operations to increment or decrement the reservable column value. Update requests made to the reservable column are recorded in a reservation journal. Before executing the operation for reservation, each concurrent transaction accesses the journal, and checks the total reservable quantities of all uncommitted transactions.

Verifying and Deferring an Update

The transaction, based on the constraints placed on the reservable column, decides whether the quantity is sufficient to make the update. The transaction checks for the following:

Verifies that the update can succeed. The update to reservation journal is allowed to proceed if there is sufficient balance. If the balance is not sufficient to fulfill the update (consume) request, the update request to the reservable column is failed without relying on any active (not yet committed) replenishment to succeed.

Checks for any constraints or bounds (CHECK constraints) on reservable columns to enforce business rules and to ensure application correctness. Check constraints can include checks for reservable and non-reservable columns.

Defers the actual update to reservable column until the commit time to improve concurrency

Auto-compensating Successful Updates in a Failed Transaction

Lock-free reservation enables the tracking of the state transitions of a reservable column through its transaction lifecycle. If a transaction is aborted (after partial completions) due to reasons such as insufficient balance or an abort of a saga, lock-free reservation issues automatic compensation or rollback of the reservable updates.

Ensuring Durability of a Reservable Update

The lock-free reservations are transformed to the actual updates at the commit of the transaction. Rollback of the transaction voids all lock-free reservations that the transaction holds in the reservation journals. Rollback to a savepoint removes the lock-free reservations made by the transaction after the affected savepoint.

For sagas in microservices, lock-free reservations enable automatic compensation of the reservable updates for successful local transactions in an aborted saga. Lock-free reservation enables tracking the reservable updates within the database during the execution of transactions and retains the journals beyond the commit of the transaction until the saga finalization.

############## Comparing Optimistic Locking and Lock-Free Reservation

Lock-free reservation guarantees the outstanding reservations by operating within bounds as follows:

Journals on-going requests.

Introduces new reservation journal columns to track projected values based on the pending requests.

Consults the journal to allow or reject new requests based on outstanding reservations.

Rejects new requests that cannot be satisfied due to outstanding reservations or if the request quantity is greater than the availability.

In comparison, optimistic locking does not track or guarantee reservations.

Here are other drawbacks of the optimistic locking approach:

Requests may not be satisfied at the commit time because of insufficient quantity ( if over-promised).

Possibility of abort of a transaction at the end. If a long-running transaction is aborted, work is wasted and the changes must be rolled back.

*********** Creating a Reservable Column at Table Creation

When you create a new table, you can declare a reservable column using the reservable keyword.

Modify the CREATE TABLE command to enable lock-free reservation as follows:

Create_table_command::={relational_table | object_table | XMLType_table }

Relational_table::= CREATE TABLE [ schema. ] table …;

relational_properties::= [ column_definition ] 

column_definition::= column_name datatype reservable [CONSTRAINT constraint_name check_constraint]

CREATE TABLE Account( ID NUMBER PRIMARY KEY,

 Name VARCHAR2(10),

 Balance NUMBER reservable CONSTRAINT minimum_balance CHECK (Balance >= 50))

 The creation of the "Account" table with the reservable column "Balance" creates an associated reservation journal table. The reservation journal table is created under the same user schema and in the same tablespace as the user table. The reservation journal table also has deferred segment creation enabled.

Note:The CREATE TABLE statement syntax supports the RESERVABLE keyword but not the NOT RESERVABLE keyword. The NOT RESERVABLE keyword is supported in the ALTER TABLE command.

See Also:

Reservation Journal Table Columns for more information about the columns used in the reservation journal table.

Use the ALL_CONSTRAINTS view to get the constraint details.

SELECT table_name, constraint_name, search_condition

 FROM ALL_CONSTRAINTS

 WHERE table_name='ACCOUNT';

 

#########Reservation Journal Table Columns

A reservation journal table contains the following column information.

The DESCRIBE statement gives you the actual names of the columns in the reservation journal table associated with the earlier mentioned Account table:

SQL>desc SYS_RESERVJRNL_<object_number_of_base_table>;

Table 29-1 Reservation Table Columns

Name Null? Type Description

ORA_SAGA_ID$

RAW(16)

Saga ID of the transaction (0 for non-saga transactions)

ORA_TXN_ID$

RAW(8)

Transaction ID of the transaction (containing usn, slot, seq)

ORA_STATUS$

CHAR(12)

Status of the Txn ID, with values: {ACTIVE, INACTIVE, COMPENSATED}

ORA_STMT_TYPE$

CHAR(16)

DML statement type {UPDATE}

ID

NOT NULL NUMBER

Primary Key column of user table

BALANCE_OP

CHAR(7)

Reservable column operation with operations having ‘+’ or ‘-’ for replenishment or consumption

BALANCE_RESERVED

  NUMBER

Reservable column reserved and is the amount reserved from the reservable column

******** Adding or Modifying Reservable Columns

You can modify the ALTER TABLE statement to add a reservable column or change a reservable column into a non-reservable column.

To add a reservable column, modify the ALTER TABLE command as follows:

ALTER TABLE [ schema.]table

 [add [column_definition]]…;

 column_definition::= column_name datatype reservable [default <value>]

 [CONSTRAINT constraint_name check_constraint]

ALTER TABLE Account

 ADD (Balance NUMBER reservable CONSTRAINT minimum_balance CHECK (Balance >= 50));

To change an existing column to a reservable column, modify the ALTER TABLE command as follows:

ALTER TABLE [ schema.]table

 [modify [column_definition]]…;

 column_definition::= column_name reservable [default <value>]

 [CONSTRAINT constraint_name check_constraint]

To change an existing QOH column to a reservable column and to optionally add a new constraint:

ALTER TABLE PRODUCTS 

MODIFY (QOH reservable default 0 CONSTRAINT maxAmount CHECK (QOH <= 100));

To change a reservable column to a non-reservable column, modify the ALTER TABLE command as follows:

ALTER TABLE [ schema.]table

 [modify [column_definition]]…;

 column_definition::= column_name not reservable]

 To change an existing reservable column QOH to a non-reservable column:

ALTER TABLE PRODUCTS modify (QOH not reservable);

Note:A reservable column can be converted to a non-reservable column using the earlier mentioned ALTER TABLE command. Although the lock-free reservations are disabled once a reservable column is changed to a non-reservable column, applications may choose to enforce the constraints. Hence, the constraints are not automatically dropped when a column is converted to a non-reservable column. You can choose to drop the constraints using the ALTER TABLE statement.

######## About CHECK Constraints in Reservable Columns

A CHECK constraint lets you specify a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a NULL). When Oracle evaluates a check constraint condition for a particular row, any column names in the condition refer to the column values in that row.

Note:Oracle does not verify that conditions of check constraints are not mutually exclusive. Therefore, if you create multiple check constraints for a column, design them carefully so their purposes do not conflict. Do not assume any particular order of evaluation of the conditions.

Table-level CHECK Constraints for Reservable Columns

You can have reservable columns in table-level CHECK constraints. If a table-level CHECK constraint that involves reservable and non-reservable columns fails validation at commit time, then the transaction is aborted. The abort happens because the non-reservable column values cannot be guaranteed using the reservation mechanism.

For example:

CREATE TABLE Account(

 ID NUMBER PRIMARY KEY,

 Name VARCHAR2(10),

 Balance NUMBER reservable,

 Earmark NUMBER,

 Limit NUMBER,

 CONSTRAINT minimum_balance CHECK (Balance + Limit – Earmark >= 0))

 

You can also define reservable columns without constraints. For such reservable columns all lock-free reservations are successful.

No storage clause specification is supported for reservable columns.

Example: Conventional Locking and Lock-Free Reservation

The examples below provide a purchase transaction in conventional and lock-free reservation modes.

Conventional Locking (with Long-held Locks)

The following example uses traditional locking to allow a purchase of a $25 item while maintaining a $50 minimum balance:

A SELECT FOR UPDATE is first issued to read and lock the balance.

If the balance is at least 75, the item purchase is allowed.

The UPDATE then debits the balance.

The transaction then commits.

An insufficient balance causes an abort.

CREATE TABLE Account (

 ID NUMBER PRIMARY KEY,

 Name VARCHAR2(10),

 Balance NUMBER CONSTRAINT minimum_balance CHECK (Balance >= 50)); 

DECLARE current NUMBER;

BEGIN

 -- Read and Lock account balance

 SELECT Balance INTO current

 FROM Account

 WHERE ID = 12345 FOR UPDATE;

 IF current >= 75 THEN

  -- Sufficient funds: Perform item purchase

  PurchaseItem();

  -- Debit account balance and commit 

  UPDATE Account

  SET Balance = Balance - 25

  WHERE ID = 12345;

  COMMIT;

 ELSE

  ROLLBACK; -- Insufficient funds, abort

 END IF;

END;

Lock-Free Reservation (with Short-held Locks)

The following example uses lock-free reservation to allow a purchase of a $25 item while maintaining a $50 minimum balance. The reservable column constraint allows a reservation to be placed on a column value without locking the row.

The balance update reserves $25 without locking the account.

If the reservation succeeds, the item purchase is allowed to proceed.

The final commit locks the account row and applies the balance debit of $25 as recorded in the reservation.

If the reservation fails due to insufficient funds, the update statement fails with the CHECK constraint violation.

CREATE Table Account(

 ID NUMBER PRIMARY KEY,

 Name VARCHAR2(10),

 Balance NUMBER RESERVABLE CONSTRAINT minimum_balance CHECK (Balance >= 50));

BEGIN

 -- Reserve 25 from account balance

 UPDATE Account SET Balance = Balance - 25

 WHERE ID = 12345;

 -- If reservation succeeds perform item purchase

 PurchaseItem();

 -- The commit finalizes the balance update

 COMMIT; -- This gets the account row lock 

 EXCEPTION WHEN Check_Constraint_Violated

 -- This indicates that the reservation failed

 THEN ROLLBACK;

END;

****Querying Reservable Column Views

You can run queries on the dictionary views of reservable columns to get information about reservable columns.

You can run queries on the DBA_TAB_COLUMNS, USER_TAB_COLUMNS and ALL_TAB_COLUMNS views to check if a column is declared as a reservable column.

SELECT table_name, column_name , reservable_column

 FROM user_tab_columns

 WHERE table_name = <table name>;

 

You can run queries on the DBA_TAB_COLS, USER_TAB_COLS, and ALL_TAB_COLS views to check if a column is declared as a reservable column.

SELECT table_name, column_name , reservable_column 

 FROM user_tab_cols 

 WHERE table_name = <table name>;

 

Example Query:

SQL> SELECT table_name, column_name , reservable_column 

 FROM user_tab_cols 

 WHERE table_name = 'ACCOUNT';

 

Result:

TABLE_NAME     COLUMN_NAME     ESC

–-----------------------------------

ACCOUNT         NAME          NO

ACCOUNT         BALANCE       YES

ACCOUNT         ID            NO

3 rows selected

You can run queries on the DBA_TABLES, USER_TABLES, and ALL_TABLES views to check if the user table has one or more reservable columns.

SELECT table_name, has_reservable_column 

 FROM user_tables 

 WHERE table_name = <table name>;

 

Example Query:

SQL> SELECT table_name, has_reservable_column 

 FROM user_tables 

 WHERE table_name = 'ACCOUNT';

Result:

TABLE_NAME     HAS

–-------------------

ACCOUNT        YES

1 row selected

Thanks ,

Srini