Part 4: Top-level EXISTS

This is part 5 of a multi-part blog series on locking aspects of assertions. In the next few blogs we’ll start looking at various different assertion query-shapes and how they influence the assertion locking algorithm. In this part we’ll deal with assertions that have a nested EXISTS, or the equivalent, a nested IN under a top-level NOT EXISTS.

Here’s an example.

create assertion emp_1 check
(not exists
   (select 'End-dated employee with current benefits'
      from EMP e
     where e.ENDDATE is not null
       and exists
             (select 'Current benefits'
                from EMP_BENEFITS eb
               where eb.EMPNO = e.EMPNO
                 and eb.IS_CURRENT = 'Y')))

The assertion mandates that end-dated employees can no longer have current benefits.

The semantically equivalent assertion using an IN-subquery:

create assertion emp_1 check
(not exists
   (select 'End-dated employee with current benefits'
      from EMP e
     where e.ENDDATE is not null
       and e.EMPNO in
             (select eb.EMPNO
                from EMP_BENEFITS eb
               where eb.IS_CURRENT = 'Y')))

And finally, the same assertion using the new ALL-SATISFY syntax:

create assertion emp_1 check
(all
   (select e.EMPNO
      from EMP e
     where e.ENDDATE is not null) e
 satisfy
   (not exists
      (select eb.EMPNO
         from EMP_BENEFITS eb
        where eb.EMPNO = e.EMPNO
          and eb.IS_CURRENT = 'Y')))

In an assertion, a nested EXISTS subquery can always be unnested into the parent query-block without changing the semantics of the assertion. All three above are equivalent to this assertion:

create assertion emp_1 check
(not exists
   (select 'End-dated employee with current benefits'
      from EMP e, EMP_BENEFITS eb
     where e.enddate is not null
       and eb.EMPNO = e.EMPNO
       and eb.IS_CURRENT = 'Y'))

And this is what we internally do. Note that the shape of the assertion query then becomes the same as the purchase_order_1 assertion’s query shape from part 3: the main query-block consists of a join without subqueries, the join only involves one join-column, and tables being joined have additional filters. The assertion is a universal quantification, and the set of quantified values (qv’s), in this case, is the set of EMPNO values.

The potentially violating DML statements for this assertion are:

  • Inserting an employee with non-null ENDDATE
  • Inserting a current benefit
  • Updating an employee’s ENDDATE to become non-null
  • Updating a benefit’s IS_CURRENT to become ‘yes’
  • Updating EMPNO (the join column) of an end-dated employee or of a current benefit.

And only in these cases we will take the qv-level enqueues in the same manner as discussed in assertion-locking-part-3-an-enqueues. To recap here this was:

  • Two potentially violating DML-statements can run concurrently as long as they involve different EMPNO values
  • Two potentially violating DML-statements that involve the same EMPNO-value can run concurrently if they operate on the same table
  • All other scenarios of two concurrently running potentially violating DML-statements will be forced to run in a serial execution order
    • This is done by taking AN-enqueues in S and SX mode that represent the EMPNO values in the two tables involved (see part 3).