This will be multi-part blog series on locking aspects of assertions. In this first part we’ll discuss why there’s a need to enforce a serial execution order on transactions that involve the same assertion.
An assertion can express a rule that depends on more data other than the row(s) being changed, added, or deleted by a DML statement. That makes concurrency important: after execution of a DML statement, Oracle may need to examine other rows—possibly in other tables—to determine whether the change still satisfies the assertion.
Consider this assertion:
CREATE ASSERTION purchase_order_1 check
(NOT EXISTS
(SELECT 'Open order line in canceled purchase order'
FROM ORDERS o,
ORDER_LINES ol,
ORDER_EVENT_HISTORY oeh
WHERE o.ORDER# = ol.ORDER#
AND o.ORDER# = oeh.ORDER#
AND o.TYPE = 'PURCHASE'
AND ol.STATUS = 'OPEN'
AND oeh.EVENT = 'CANCELED')
);It says that a purchase order must not have both an open order line and a cancellation event.
Now suppose transaction A inserts an open line for a not yet canceled purchase order: this change satisfies the assertion. Assume this is the only currently OPEN order line for that order, there may be other order lines present already, but they all have a different status. At the same time, transaction B records a CANCELED event for that order: this change too satisfies the assertion, as this transaction cannot yet see the one uncommitted open order line of transaction A. Each transaction’s assertion check will query the related (joining) rows from the other two tables to come to the conclusion that its changes are “good to go”. If both checks proceed without coordination, each missing the other transaction’s uncommitted row, then after both transactions commit, there will be an open order line for a canceled purchase order.
| Time | Transaction A | Transaction B | Comments |
| t1 | Insert 1st OPEN order line | Check succeeds | |
| t2 | Insert CANCELED event | Check succeeds as we cannot see uncommitted open order line | |
| t3 | commit | ||
| t4 | commit | Assertion is violated |
The assertion therefore needs a way to keep relevant data stable while its check is being validated. When another transaction makes a change that could affect the same assertion, it must wait for the first transaction to finish. Once the first transaction commits, the second can proceed and validate against that committed outcome. This is serialization at the assertion’s conflict boundary. It does not mean that all transactions across the database must run one at a time. Only transactions whose changes could interfere with the same assertion need coordination.
A traditional foreign key already handles a similar race condition. If one transaction inserts the first child row referencing a parent key while another deletes that parent row, Oracle coordinates the operations so they cannot both commit in a way that leaves an orphaned child row. Depending on which transaction executes its DML first, the other transaction’s DML must wait and then, depending on commit or rollback of the first transaction that took effect, either fail or succeed the constraint check.
| Time | Transaction A | Transaction B | Comments |
| t1 | Insert 1st child for parent | Check succeeds as the parent exists | |
| t2 | Delete parent | This blocks and waits for transaction A to commit or rollback | |
| t3 | commit | Assume it commits | |
| t4 | ORA-02292 | Delete receives foreign key violation message |
Assertions generalize this idea beyond a simple parent–child relationship. Because an assertion can depend on joins and conditions across several tables, Oracle must coordinate transactions whose changes could jointly violate the assertion. Without that coordination, a valid check of each transaction’s individual change would not guarantee valid committed data.