Back to results
US 11,080,268 B2Granted
Method for serializing transactions to enforce general-purpose integrity constraints
AssigneeOracle International Corporation
InventorToon Koppelaars
Life of this patent
Estimating the term…
Abstract
Techniques are disclosed for serializing assertion-triggering transactions by locking special purpose resources (i.e. other than rows or tables), which enables a high level of concurrency for these transactions while at the same time ensuring correctness of the SQL assertion validation code. The techniques involve creating objects referred to therein as “Assertion-Locking Memory-Objects” or “ALMOs”. The database server causes each assertion-triggering transaction to obtain locks on one or more ALMOs prior to determining whether the changes made by assertion-triggering transaction violates the assertion. Because locks on the ALMOs are finer than the table locks, fewer transactions are blocked by ALMO locks.
Claims14
A method comprising:
- receiving, at a database server, a request to register a SQL assertion, wherein the SQL assertion is a MULTI-SET-UQ assertion;
- after the SQL assertion is registered, the database server detecting that an uncommitted transaction performed an assertion-triggering DML (Data Manipulation Language) operation on a table referenced in the SQL assertion;
- prior to committing the uncommitted transaction, performing the steps of:
- determining that the table belongs to a single join-group of a UQ-JoinGraph of the SQL assertion;
- obtaining a plurality of looks, the plurality of locks comprising:
- a share lock on a first memory object that corresponds to the entire SQL assertion;
- an exclusive lock on a second memory object that corresponds to a combination of:
- the SQL assertion;
- the single join group;
- and a quantified value within the single join group;
- a share-exclusive lock on a third memory object that corresponds to a combination of:
- the SQL assertion;
- and the table;
- after obtaining the plurality of locks, determining whether the assertion-triggering DML operation violated the SQL assertion;
- if the assertion-triggering DML operation violated the SQL assertion, then preventing the uncommitted transaction from committing;
- wherein the method is performed by one or more computing devices.
13 more elements
A method comprising:
- receiving, at a database server, a request to register a SQL assertion, wherein the SQL assertion is a MULTI-SET-UQ assertion;
- after the SQL assertion is registered, the database server detecting that an uncommitted transaction performed an assertion-triggering DML (Data Manipulation Language) operation on a table referenced in the SQL assertion;
- prior to committing the uncommitted transaction, performing the steps of:
- determining that the table belongs to a plurality of join-groups of a UQ-JoinGraph of the SQL assertion;
- obtaining a plurality of locks, the plurality of locks comprising;
- a share lock on a first memory object that corresponds to the entire SQL assertion;
- for each join-group of the plurality of join-groups, an exclusive lock on a respective memory object that corresponds to a combination of:
- the SQL assertion;
- the join group;
- and a quantified value within the join group;
- a share-exclusive lock on a third memory object that corresponds to a combination of:
- the SQL assertion;
- and the table;
- after obtaining the plurality of locks, determining whether the assertion-triggering DML operation violated the SQL assertion;
- if the assertion-triggering DML operation violated the SQL assertion, then preventing the uncommitted transaction from committing;
- wherein the method is performed by one or more computing devices.
13 more elements
One or more non-transitory computer-readable media storing instructions which, when executed by one or more computing devices, cause:
- receiving, at a database server, a request to register a SQL assertion, wherein the SQL assertion is a MULTI-SET-UQ assertion;
- after the SQL assertion is registered, the database server detecting that an uncommitted transaction performed an assertion-triggering DML (Data Manipulation Language) operation on a table referenced in the SQL assertion;
- prior to committing the uncommitted transaction, performing the steps of:
- determining that the table belongs to a single join-group of a UQ-JoinGraph of the SQL assertion;
- obtaining a plurality of locks, the plurality of locks comprising:
- a share lock on a first memory object that corresponds to the entire SQL assertion;
- an exclusive lock on a second memory object that corresponds to a combination of:
- the SQL assertion;
- the single join group;
- and a quantified value within the single join group;
- a share-exclusive lock on a third memory object that corresponds to a combination of:
- the SQL assertion;
- and the table;
- after obtaining the plurality of locks, determining whether the assertion-triggering DML operation violated the SQL assertion;
- and if the assertion-triggering DML operation violated the SQL assertion, then preventing the uncommitted transaction from committing.
12 more elements
One or more non-transitory computer-readable media storing instructions which, when executed by one or more computing devices, cause:
- receiving, at a database server, a request to register a SQL assertion, wherein the SQL assertion is a MULTI-SET-UQ assertion;
- after the SQL assertion is registered, the database server detecting that an uncommitted transaction performed an assertion-triggering DML (Data Manipulation Language) operation on a table referenced in the SQL assertion;
- prior to committing the uncommitted transaction, performing the steps of:
- determining that the table belongs to a plurality of join-groups of a UQ-JoinGraph of the SQL assertion;
- obtaining a plurality of locks, the plurality of locks comprising:
- a share lock on a first memory object that corresponds to the entire SQL assertion;
- for each join-group of the plurality of join-groups, an exclusive lock on a respective memory object that corresponds to a combination of:
- the SOL assertion;
- the join group;
- and a quantified value within the join group;
- a share-exclusive lock on a third memory object that corresponds to a combination of:
- the SQL assertion;
- and the table;
- after obtaining the plurality of locks, determining whether the assertion-triggering DML operation violated the SQL assertion;
- and if the assertion-triggering DML operation violated the SQL assertion, then preventing the uncommitted transaction from committing.
12 more elements
Dependencies are inferred from the claim text.