On the spike of innovating databases, Lock-free reservation within Oracle Database 23ai features simplification of concurrent access to data. Especially, Duality Views in JSON Relational Duality utilize this lock-free concurrency control mechanism that consistently reads and writes for databases. As a way to modernize your database, let’s explore the next feature of lock-free reservation within Oracle Database 23ai.
Lock-free reservation in Oracle simplifies concurrent access to data by avoiding traditional locking mechanisms. It permits multiple transactions to reserve the ability to update a specific data item simultaneously without blocking one another. By sidestepping locks that can lead to delays and bottlenecks in busy environments, this method aims to boost concurrency and diminish contention within the database system. Rather than waiting for a lock to clear, transactions can reserve the update rights and continue unhindered, thereby improving performance and scalability in certain scenarios.
Lock-Free Reservation Features in Oracle Database 23ai
- Facilitates multiple concurrent updates on a numeric column without traditional locking mechanisms.
- Reduces blocking during frequent concurrent updates to reservable columns, enhancing user experience.
- Transactions can concurrently add or subtract from the same row’s reservable column without waiting for others to commit.
- Updates proceed based on specified conditions for the reservable column.
- Reservable column updates do not lock rows, allowing concurrent updates to non-reservable columns of the same row.
- Increased throughput as concurrent updates are not blocked by others, improving system performance.
- Overall benefits include improved responsiveness, scalability, and efficiency in managing concurrent updates in the database.
Benefits of Lock-Free Reservation
- Lock-free reservation improves user experience and concurrency by minimizing locking on hot data. Transactions can reserve a column value without locking it until modification occurs during commit.
- In Saga implementations, lock-free reservation automates compensating transactions, eliminating the need for complex compensating functions. Failed Saga transactions trigger automatic compensations through journals, ensuring database consistency.
- Lock-free reservation enables efficient resource usage as multiple transactions can utilize resources concurrently without blocking. This leads to more efficient resource utilization, reduced waiting times, and improved response times.
- Border Scope reservable updates on numeric aggregate data benefit various applications such as banking, inventory control, ticketing, and event reservation. They enhance concurrency and transaction efficiency, especially in long-running transactions.
Note: A Saga encapsulates a lengthy business transaction that consists of multiple microservices which are independent. Each microservice comprises one or more local transactions that are part of the same Saga.
Guidelines and Restrictions for Lock-Free Reservation
- Guidelines and Restrictions for Lock-Free Reservation
- The Schema definition of user tables declare the reservable columns with the reservable Reservable columns provide lock-free reservations.
- A reservable column can be specified for Oracle numeric data type (NUMBER, INTEGER, and FLOAT) columns only.
- A reservable column cannot be a Primary Key or an identity column (or virtual column) because the reservable column is an aggregate type.
- A user table can have atmost ten reservable columns.
- User tables that have reservable columns must have a Primary Key.
- Indexes are not supported on reservable columns.
- Composite reservable columns are not allowed.
- Guidelines and Restrictions for Update Statements:
Updates to reservable columns must be specified as one of the following:
UPDATE <table_name>
SET <reservable_column_name> = <reservable_column_name> + (<expression>)
WHERE <primary_key_column> = <expression>
OR
UPDATE <table_name>
SET <reservable_column_name> = <reservable_column_name> – (<expression>)
WHERE <primary_key_column> = <expression>
Note:
- Do not use SET <reservable_column_name> = <value>. Direct assignment of a value raises an error.
- For composite primary keys, all the primary key columns must be specified in the WHERE clause of the reservable update.
- Guidelines for Inserts and Deletes
When using lock-free reservation, follow these guidelines for the INSERT and DELETE statements issued on tables with reservable columns:
- Transactions can insert a complete row with values for the reservable columns without any alteration in behaviour.
- The inserted row remains invisible to other transactions for any reservable updates until the inserting transaction is committed.
- If a DELETE statement is executed while there are pending reservations for the rows to be deleted from a user table, transactions with active lock-free reservations against those rows must finish before the delete operation can proceed.
- Concurrent DDL statements Guidelines for Lock-Free Reservation:
- DDL in Progress: During a table’s DDL process, updates to reservable columns are paused until the DDL completes.
- Pending Reservations: If there are ongoing reservations for any rows in a table, any DDL statements are delayed until those reservations are resolved.
- Restrictions for Reservation Journal Table
Observe these restrictions for reservation journal table —User Data Manipulation Language (DML) and Data Definition Language (DDL) operations are prohibited on a reservation journal table. You are restricted from initiating or altering a reservation journal table using DML commands. SQL commands for dropping, truncating, renaming, or modifying the definition of the reservation table are not allowed.
To embrace lock-free reservation within Oracle Database 23ai or embrace the features of Duality Views, INFOLOB paves the way. Helping enterprises to implement Oracle Database 23ai by leveraging its lock-free reservation feature, we simplify concurrent data access and boosts performance.
By avoiding traditional locking mechanisms, INFOLOB ensures enhanced concurrency, reduced delays, and improved resource utilization, particularly benefiting high-transaction environments like banking and inventory control. Our expertise aids organizations in optimizing the database’s scalability and efficiency while adhering to best practices and guidelines. Overall, INFOLOB’s support in adopting Oracle Database 23ai translates to better user experiences and streamlined database operations.
For all queries, please write to:
