System and method for increased transaction throughput and improved response time under high contention

Patent metadata

Field	Value
Publication number	US-12436937-B2
Application number	US-202318223822-A
Country	US
Kind code	B2
Filing date	Jul 19, 2023
Priority date	Oct 14, 2022
Publication date	Oct 7, 2025
Grant date	Oct 7, 2025

Abstract

A Lock-Free Reservation mechanism is provided. When a transaction issues an update that affects a value in a “reservable column” of a row, the database server does not immediately obtain a lock that covers the row. Instead, the database server adds a reservation to a reservation journal. At the time the transaction commits, a lock is obtained and the requested update is made. In one implementation, before adding the reservation to the reservation journal, the database server determines whether making the update would violate any constraints involving the reservable column. In one implementation, the constraint check not only takes into account the current value of the data item that is being updated and the amount of the update, but also pre-existing reservations in the reservation journal that affect the same data item.

First claim

What is claimed is:

1. A method comprising:
specifying a numeric column of a particular table to be a reservable column and creating one or more constraints involving the reservable column;
while executing a particular transaction, a database server processing an update to a particular row of the reservable column of the particular table by:
determining whether the one or more constraints involving the reservable column would be violated if the update were performed;
responsive to determining that the one or more constraints involving the reservable column would not be violated if the update were performed, without obtaining a lock that covers the particular row, adding a reservation to a reservation journal;
wherein the reservation is a record that:
identifies the particular transaction; and
specifies an amount by which to increment or decrement a value in the reservable column of the particular row;

at a commit time of the particular transaction, the database server performing:
obtaining a lock that covers the particular row;
incrementing or decrementing the value in the reservable column of the particular row by the amount; and
committing the particular transaction;

wherein the method is performed by one or more computing devices.

2. The method of claim 1 wherein:
the one or more constraints include a particular constraint that involves the reservable column and one or more non-reservable columns;
the method further comprises at the commit time of the particular transaction, determining whether the particular constraint would be violated if the update were performed, and
the step of incrementing or decrementing the value in the reservable column of the particular row is performed responsive to determining, at the commit time, that the particular constraint would not be violated if the update were performed.

3. The method of claim 1 wherein determining whether one or more constraints involving the reservable column would be violated if the update were performed includes determining whether the one or more constraints involving the reservable column would be violated if:
(a) all same-type operations recorded in the reservation journal by other uncommitted transactions were made, and
(b) all updates requested by the particular transaction were made.

4. The method of claim 1 wherein:
the particular transaction has a particular priority; and
determining whether one or more constraints involving the reservable column would be violated if the update were performed includes determining whether the one or more constraints involving the reservable column would be violated if:
(a) all same-type operations recorded in the reservation journal by other uncommitted transactions that are of equal or greater priority to the particular priority were made, and
(b) all updates requested by the particular transaction were made.

5. The method of claim 4 further comprising:
making a determination that a second transaction, that has a priority less than the particular priority, has a pre-existing reservation that, when combined with the update, causes violation of at least one constraint; and
responsive to making the determination, causing the pre-existing reservation to fail.

6. The method of claim 1 further comprising:
while executing a second transaction, the database server processing a second update to the particular row of the reservable column by:
without obtaining a lock that covers the particular row, adding a second reservation to the reservation journal;
wherein the second reservation is a second record that:
identifies the second transaction; and
specifies a second amount by which to increment or decrement the value in the reservable column of the particular row;

prior to a commit time of the second transaction, the database server rolling back at least a portion of the second transaction, wherein the portion includes the second update;
in response to rolling back the portion of the second transaction, removing from the reservation journal the second reservation.

7. The method of claim 6 wherein rolling back at least a portion is performed by rolling back to a particular savepoint.

8. The method of claim 1 wherein:
the particular transaction requests a plurality of updates to the reservable column of the particular row; and
incrementing or decrementing the value in the reservable column of the particular row is performed by executing a single update operation that increments or decrements the value based on a cumulative effect of the plurality of updates.

9. The method of claim 8 wherein:
the reservable column is a first reservable column of the particular table;
the particular table has a second reservable column;
the particular transaction requests a second plurality of updates to a value in the second reservable column of the particular row; and
the single update operation also increments or decrements the value in the second reservable column of the particular row based on a cumulative effect of the second plurality of updates.

10. The method of claim 1 further comprising:
while the reservation journal has one or more pending reservations for the reservable column, receiving a Data Definition Language (DDL) command that targets the particular table;
responsive to determining that the reservation journal has one or more pending reservations for the reservable column, retrying the DDL command until either (a) the reservable column has no remaining pending reservations, or (b) a retry termination condition is satisfied.

11. The method of claim 1 further comprising:
while the reservation journal has one or more reservations involving the particular row, receiving a delete command, from a second transaction, to delete the particular row; and responsive to determining that the reservation journal has one or more reservations involving the particular row, retrying the delete command until either (a) the reservable column has no remaining pending reservations involving the particular row, or (b) a retry termination condition is satisfied.

12. The method of claim 1 further comprising:
while executing a second transaction, the database server processing a second update to the value in the particular row of the reservable column by:
determining whether one or more constraints involving the reservable column would be violated if the second update were performed; and
responsive to determining that one or more constraints involving the reservable column would be violated if the second update were performed, performing a remedial operation selected from the group consisting of:
attempting a reservation, for the value in the particular row, of a different amount than an amount specified for the second update,
retrying the second update after waiting a retry duration, or
placing the second update on hold until occurrence of a particular event.

13. The method of claim 1 wherein:
the reservation journal has a pre-existing reservation for an update to the value in the particular row of the reservable column; and
the method further comprises causing the pre-existing reservation to automatically expire after lapse of a predetermined period of time.

14. The method of claim 13 further comprising, responsive to expiration of the pre-existing reservation, immediately cancelling the pre-existing reservation.

15. The method of claim 13 further comprising, after expiration of the pre-existing reservation, cancelling the pre-existing reservation in response to a later-received reservation request if the pre-existing reservation would cause failure of the la

Assignees

• Oracle Int Corp

Inventors

• Krishnaswamy Vasudha
• Gawlick Dieter
• Lahiri Tirthankar
• Gong Weiwei

Classifications

• G06F16/2379

  Updates performed during online database operations; commit processing · CPC title

• G06F16/2343 Primary

  Locking methods, e.g. distributed locking or locking implementation details · CPC title