SQL Assertions, ANSI join, and ORA-08697

In the previous post on consistency boundaries, we saw that an updatable join view can hide a write-skew anomaly when the developer assumes the consistency boundary is a single row, even though there are actually two underlying rows and only one is locked. I wanted to see how the new SQL Assertions introduced in "Oracle AI Database 26ai Release 23.26.1.0.0" handle this, since they use a different locking mechanism.

TL;DR: they handle it correctly, but without ANSI joins.

Without assertions, for an employee with a salary of 1000 and a commission of 100, two concurrent users could each add 42 to both values because they couldn’t see each other’s changes, and updating one column didn’t lock the other. I then added a SQL assertion that doesn’t allow the commission to exceed 1150, so that only one session can add 42. This assertion works as expected, waiting on the other transaction before checking the sum:

CREATE ASSERTION salary_plus_commission_le_1150
CHECK (
  NOT EXISTS (
    SELECT 'violation'
    FROM emp_salary s, emp_commission c
    WHERE s.empno = c.empno
      AND s.salary + c.commission > 1150
  )
);

When the first transaction committed, the second transaction detected the write skew:

ERROR at line 1:
ORA-08601: SQL assertion (FRANCK.SALARY_PLUS_COMMISSION_LE_1150) violated.

I'm writing this blog post because I had trouble finding the correct declaration for this assertion, and I hope it helps others. My first attempt was simply:

CREATE ASSERTION salary_plus_commission_le_1150
CHECK (
  NOT EXISTS (
    SELECT 'violation'
    FROM emp_salary s join emp_commission c USING (empno)
    WHERE s.salary + c.commission > 1150
  )
);

This joins the two tables and raises a violation is the sum of salary and commission is higher than 1150, but the CREATE ASSERTION failed with:

ORA-08689: CREATE ASSERTION failed
ORA-08697: SYS owned tables are not supported.

I'm not using SYS, and cannot use SYS because I'm on the managed service "Autonomous Database" which doesn't give SYSDBA privileges.

I reproduced this in a local environment to trace the SQL statements (ALTER SESSION SET EVENTS 'sql_trace bind=false, wait=false') and found that Oracle internally checked this constraint with:

/* SQL Analyze(250,0) */
SELECT /*+ ALL_ROWS BYPASS_RECURSIVE_CHECK */
1 FROM "SYS"."DUAL" WHERE (
  NOT EXISTS (
    SELECT 1
    FROM emp_salary s
    JOIN emp_commission c ON s.empno = c.empno
    WHERE s.salary + c.commission > 1150
  )
)

The good old DUAL table is owned by SYS and cannot be used in the assertion.

After trying many variations, I realized that SQL assertions are always evaluated with a SELECT from "SYS"."DUAL", even for cases that work, like in my previous post on SQL Assertions.

Then, I remembered what I’d learned from 30 years of working with Oracle Databases: if something fails with an ANSI join in Oracle, try the legacy join syntax (no JOIN clauses, only WHERE conditions). When ANSI joins were added in 9i, Oracle implemented them with internal transformations that affected other features. Most of those issues were fixed over the 20 years that followed, but SQL assertions seem to have fallen into that trap.

With the legacy join syntax, the internal query still reads "SYS"."DUAL", but it works:

=====================
PARSING IN CURSOR #140737366953320 len=208 dep=0 uid=136 oct=290 lid=136 tim=6202261308 hv=3501011950 ad='7ffff8be4960'
 sqlid='fzcfcfv8auczf'
CREATE ASSERTION salary_plus_commission_le_1150
CHECK (
  NOT EXISTS (
    SELECT 'violation'
    FROM emp_salary s, emp_commission c
    WHERE s.empno = c.empno
      AND s.salary + c.commission > 1150
  )
)
END OF STMT

...

=====================
PARSING IN CURSOR #140737364327008 len=286 dep=1 uid=136 oct=3 lid=0 tim=6202334328 hv=225567679 ad='783b9f10' sqlid='fv1va6n6r3sxz'
 /* SQL_ASSERTION obj#=72600 */ 
SELECT /*+  ALL_ROWS BYPASS_RECURSIVE_CHECK */ 1 
FROM "SYS"."DUAL" 
WHERE (   NOT EXISTS (SELECT 'violation' "'VIOLATION'" FROM "FRANCK"."EMP_SALARY" "S","FRANCK"."EMP_COMMISSION" "C" WHERE "S"."EMPNO"="C"."EMPNO" AND "S"."SALARY"+"C"."COMMISSION">1150) )
END OF STMT

Two things to remember:

• SQL Assertions provide correct consistency boundaries to avoid write skew in the absence of a serializable isolation level.
• ANSI joins are nice, but Oracle was designed before this standard, with another syntax, and some bugs persist. Falling back to the old syntax may be safer.