This is part 4 of a multi-part blog series on locking aspects of assertions. In the first three parts we’ve demonstrated how AN enqueues are used to enforce a serial execution order of potentially violating DML statements for an assertion. We deliberately used a fairly simple assertion example: a top-level NOT EXISTS without nested subqueries, nor different join-columns in the top-level query block. Before we discuss how these influence our assertion lock algorithm, we’ll first look at the (completely different) locking aspects of an assertion that has a top-level EXISTS instead of a NOT EXISTS.
Here’s an example.
create assertion hq_finance_1 check
(exists
(select 'a finance department at an HQ location'
from DEPT d, LOCATION l
where d.LOCATION_ID = l.LOCATION_ID
and l.HAS_HQ_FACILITIES = 'YES'
and d.TYPE = 'FINANCE'))It enforces that we have a finance department that’s located at an HQ location.
Before you can successfully create this assertion you would have to pre-seed the tables such that the query under the EXISTS is returning a row. Alternatively you can create the assertion in DISABLE/NOVALIDATE state, pre-seed the tables, and then alter the assertion to ENABLE/VALIDATE.
In terms of potentially violating DML statements the dynamics are the opposite compared to an assertion with a top-level NOT EXISTS. If the database state currently produces rows for the query under the EXISTS, then inserting more rows – into any of the two tables involved – can never make the query suddenly produce no rows anymore, which would introduce a violation of the assertion. In this case DELETEs are potentially violating, two specific DELETEs for this assertion:
- DELETE of a finance department
- DELETE of an HQ location
And UPDATE’s follow from these:
- UPDATE of a finance department to become a different type of department
- UPDATE of an HQ location to become a non-HQ location
Including UPDATEs of the join-column:
- Moving a finance department to another location (if this was *the* finance department at an HQ-location, then we need to check if the new location is an HQ-location too).
- Changing LOCATION_ID of an HQ location (if it had *the* finance department, then we need to check there’s a finance department with the new LOCATION_ID too).
And just like assertions with top-level NOT EXISTS, for assertions with top-level EXISTS we also first check whether your DML statement is potentially violating. If it isn’t then we don’t acquire any AN enqueues as the assertion will remain unaffected and needs no revalidation
Top-level EXIST assertions can be viewed as iterated logic OR over some set of values. In formal logic theory this is called an existential quantification. For the assertion above the iteration is over the set of LOCATION_IDs. Yes it’s again the join-column that produces the quantified values (qv’s).
Now let’s look at a scenario of two potentially violating DML statements that run concurrently. Assume the database currently has two HQ locations (LOCATION_IDs 1 and 2), and each has a finance department. So the assertion is currently good, in fact the query under the EXISTS returns two rows. Here’s the scenario for this database state:
| Time | Transaction A | Transaction B | Comments |
| t1 | Delete the finance department at location 1 | Check succeeds, as there’s still the finance department at location 2 returned by the query | |
| t2 | Update HQ location 2 to no longer be an HQ location | This should block, otherwise the assertion check will succeed here too as the delete from TX A isn’t committed yet, so the assertion query would still produce the row for location 1 in this session |
Note that in this case we need to enforce a serial execution order while the transactions affect different LOCATION_IDs. This was not the case for top level NOT EXISTS assertions. Let’s see if this makes sense. Assume there are currently 100 locations (with LOCATION_IDs 1..100), each with departments located in them. And we still have the two HQ locations, LOCATION_ID 1 and 2, both having a finance department. Then the assertion would be equivalent to:
“Location 1 is an HQ location with a finance department” — This one is true
OR
“Location 2 is an HQ location with a finance department” — This one is true
OR
…
OR
“location 100 is an HQ location with a finance department”
And here too, the dynamics of an iterated logic OR are totally different: if a transaction falsifies one of the above predicates, then we need to check if there’s still another predicate that evaluates to true. In a sense we would have to lock all the other LOCATION_IDs to prevent another concurrent transaction from making changes to them that we cannot see. Both tables need to remain stable with respect to the assertion. Of course we cannot acquire a qv-level AN enqueue for each LOCATION_ID value that’s currently in both tables. Instead we’re taking node-level AN enqueues.
Here are the enqueues that are taken when transaction A deleted the finance department at location 1 (using the script we provided in part 3).
SESSION ASSERTION TYPE ID1 ID2 P2_TXT LMOD REQU CTIME
---------------- --------------- ---- -------- ---------- -------------------- ---- ---- ------
AW@23ai_246268 HQ_FINANCE_1 AN 87987 0 DDL_LCK S 63
HQ_FINANCE_1 AN 87987 2705263069 NOD_LCK T#:0 C#:0: X 63
HQ_FINANCE_1 AN 87987 4158220438 NOD_LCK T#:1 C#:0: S 63Again there’s the DDL lock to prevent other sessions performing DDL on the assertion. And next to that there’s two other enqueues, which we refer to as node-level locks. Why two in this case? Because there are two table references in the assertion. The first node lock represents the DEPT table on which the DML operated, and it’s held in X-mode. The other node lock represents the LOCATION table and is held in S-mode. ID1 again is the OBJECT_ID of the assertion, and ID2 now is a hash of a string that involves the node name.
Why do we get the enqueue representing the table that the DML operated on in X-mode and not SX-mode like we did for qv-level locks for a top-level NOT EXISTS assertion? Because in the case of top level EXISTS we cannot allow two concurrent potentially violating DML statements on the same node (which we do allow for top-level NOT EXISTS). To illustrate this, let’s say the database state only has one HQ location with two finance departments. Now think of the scenario where two concurrent transactions each remove one of the finance departments. These two DELETEs need to run in a serial execution order, because if they don’t, we’d end up with the HQ location not having a finance department.
Continuing with above transaction A scenario, when transaction B issues the update, we end up with this blocking scenario:
SESSION ASSERTION TYPE ID1 ID2 P2_TXT LMOD REQU CTIME
---------------- --------------- ---- -------- ---------- -------------------- ---- ---- ------
AW@23ai_246268 HQ_FINANCE_1 AN 87987 0 DDL_LCK S 396
HQ_FINANCE_1 AN 87987 2705263069 NOD_LCK T#:0 C#:0: X 396
HQ_FINANCE_1 AN 87987 4158220438 NOD_LCK T#:1 C#:0: S 396
AW@23ai_247409 HQ_FINANCE_1 AN 87987 0 DDL_LCK S 4
HQ_FINANCE_1 AN 87987 2705263069 NOD_LCK T#:0 C#:0: S 4Transaction B is blocked while trying to acquire the node-level enqueue for DEPT in S-mode, which is currently held in X-mode by transaction A.
It should be clear now that top-level EXISTS is the extreme case in terms of allowed concurrency for potentially violating DML statements. Remember:
- Top-level NOT EXISTS has the least restrictions. It allows concurrent potentially violating DML statements when they involve different qv’s, or when they involve the same qv and operate on the same node.
- Top-level EXISTS only allows one concurrent potentially violating DML statement (irrespective of qv). But as always all other DML statements remain unaffected.