The Physician’s On-Name Shift solved with SQL Assertions

In a earlier article, I defined that imposing application-level guidelines, similar to “every shift should all the time have no less than one physician on name”, usually requires both serializable isolation or specific locking.

There’s one other chance when imposing the principles within the database, so that they fall underneath ACID’s C as a substitute of I, with SQL assertions. The SQL databases carried out solely a part of the SQL normal, limiting the constraints to single-row CHECK constraints, distinctive constraints between rows in the identical desk, or referential integrity constraints between desk rows. Oracle carried out lately one lacking half: SQL assertions that may specific cross-row and cross-table situations, together with some joins and subqueries.

Oracle 23.26.1

Right here is an instance. The model the place SQL assertions can be found is Oracle AI Database 26ai Enterprise Version Launch 23.26.1.0.0. Right here is how I began a Docker container:

# get the picture (12GB)
docker pull container-registry.oracle.com/database/enterprise:23.26.1.0

# begin the container
docker run -d --name ora26 container-registry.oracle.com/database/enterprise:23.26.1.0

# wait some minutes to begin the database
till docker logs ora26 | grep "DATABASE IS READY TO USE" ; do sleep 1 ; carried out

# use a weak password for this lab
docker exec -it ora26 ./setPassword.sh "franck"

# create a consumer with the proper privileges to check assertions
docker exec -i ora26ai sqlplus sys/franck@ORCLPDB1 as sysdba <<'SQL'
 grant join, useful resource to franck recognized by franck;
 grant create assertion to franck;
 grant alter session to franck;
 alter consumer franck quota limitless on customers;
SQL

# Begin the command-line
docker exec -it ora26 sqlplus franck/franck@ORCLPDB1

Create the single-table schema

I created the identical desk as within the earlier posts:

SQL> CREATE TABLE docs (
      shift_id  INT NOT NULL,
      identify      VARCHAR2(42) NOT NULL,
      on_call   BOOLEAN NOT NULL,
      CONSTRAINT pk_doctors PRIMARY KEY (shift_id, identify)
);

Desk DOCTORS created.

Two docs are on-call for the shift ‘1’:

SQL> INSERT INTO docs VALUES 
      (1, 'Alice', true),
      (1, 'Bob',   true)
;

2 rows inserted.

SQL>  COMMIT
;

Commit full.

In line with SQL-92 normal, the next assertion would assure that for each shift, the variety of docs on_call=”Y” is ≥ 1:

CREATE ASSERTION at_least_one_doctor_on_call_per_shift
CHECK (
    NOT EXISTS (
        SELECT shift_id
          FROM docs
         GROUP BY shift_id
        HAVING COUNT(CASE WHEN on_call THEN 1 END) < 1
    )
);

Sadly, this isn’t supported but:

SQL> CREATE ASSERTION at_least_one_doctor_on_call_per_shift
CHECK (
    NOT EXISTS (
        SELECT shift_id
          FROM docs
         GROUP BY shift_id
        HAVING COUNT(CASE WHEN on_call THEN 1 END) < 1
    )
);  
          FROM docs
               *
ERROR at line 5:
ORA-08689: CREATE ASSERTION failed
ORA-08661: Aggregates usually are not supported.
Assist: https://docs.oracle.com/error-help/db/ora-08689/

Oracle implements a practical, efficiency‑oriented subset of SQL‑92 assertions. It makes use of inside change monitoring, much like session‑scope materialized view logs, to restrict what have to be validated. Aggregates are at present disallowed, and assertions are primarily carried out as anti‑joins.

Here’s a extra artistic option to specific “Each shift will need to have no less than one on-call physician” as a double negation: “There should not exist any physician who belongs to a shift that has no on-call physician”:

DROP ASSERTION IF EXISTS no_shift_without_on_call_doctor;
CREATE ASSERTION no_shift_without_on_call_doctor
CHECK (
    NOT EXISTS (
        SELECT 'any physician'
          FROM docs
         WHERE NOT EXISTS (
             SELECT 'potential on-call physician in similar shift'
               FROM docs on_call_doctors
              WHERE on_call_doctors.shift_id = docs.shift_id
                AND on_call_doctors.on_call = TRUE
         )
    )
);

If the interior NOT EXISTS is true, this shift has ZERO on-call docs, so we discovered a health care provider who belongs to a shift with no on-call physician, and the outer NOT EXISTS turns into false, which raises the assertion violation. This prevents a health care provider to be faraway from the on-call if there isn’t any different on-call for a similar shift.

I re-play the conflicting adjustments from the earlier put up. In a single session, Bob removes his on-call standing:

SQL> UPDATE docs 
      SET on_call = false 
      WHERE shift_id = 1 AND identify = 'Bob'
;

In one other session, Alice additionally tries to take away her on-call standing:

SQL> UPDATE docs 
      SET on_call = false 
      WHERE shift_id = 1 AND identify = 'Alice'
;

Bob’s replace succeeds, however earlier than committing, Alice’s replace hangs, ready on enq: AN - SQL assertion DDL/DML. As soon as Bob commits, Alice’s assertion fails:

SQL> replace docs set on_call = false the place shift_id = 1 and identify = 'Alice';
replace docs set on_call = false the place shift_id = 1 and identify = 'Alice'
*
ERROR at line 1:
ORA-08601: SQL assertion (FRANCK.NO_SHIFT_WITHOUT_ON_CALL_DOCTOR) violated.
Assist: https://docs.oracle.com/error-assist/db/ora-08601/

This prevents race-condition anomalies by studying each the present state—which, with out a serializable isolation degree, is weak to write-skew anomalies—and the in-flight adjustments to different rows made by concurrent periods. This reveals the distinction between isolation ranges, which disguise concurrent ongoing adjustments from customers, and integrity constraints, the place the database internally should see concurrent exercise when imposing consistency.

Some internals: change monitoring and enqueue

You may hint what occurs, recursive statements and locks:

ALTER SESSION SET EVENTS 'sql_trace bind=true, wait=true';
ALTER SESSION SET EVENTS 'hint[ksq] disk medium';
ALTER SESSION SET tracefile_identifier=AliceRetry;

UPDATE docs 
      SET on_call = false 
      WHERE shift_id = 1 AND identify = 'Alice'
;

The replace shops the change in an inside change monitoring desk prefixed by ORA$SA$TE_ (SQL Assertion Desk Occasion):

INSERT INTO "FRANCK"."ORA$SA$TE_DOCTORS" 
(DMLTYPE$$, OLD_NEW$$, SEQUENCE$$, CHANGE_VECTOR$$, ROW$$ ,"SHIFT_ID","ON_CALL")
 VALUES (
'U', -- replace
'O', -- outdated worth
  2, -- ordered session-level sequence ORA$SA$SEQ$$
...,  -- change vector bits
'0000031D.0000.0000' -- ROWID,
1, false -- column values
);

INSERT INTO "FRANCK"."ORA$SA$TE_DOCTORS" 
(DMLTYPE$$, OLD_NEW$$, SEQUENCE$$, CHANGE_VECTOR$$, ROW$$ ,"SHIFT_ID","ON_CALL")
 VALUES (
'U',  -- replace
'N',  -- new worth
  2,  -- ordered per-session sequence ORA$SA$SEQ$$
'0a', -- change vector bits to seek out rapidly which column has modified
'0000031D.0000.0000' -- ROWID,
1, true -- column values
);

You may spot the column names utilized in materialized view logs—launched in Oracle7 as a “snapshot log”. It’s an inside function from 1992 utilized in 2026 to implement a 1992 SQL function 🤓 Nevertheless, on this context the desk isn’t a daily MV log, however an inside GTT (international momentary desk) with particular learn restrictions:

SQL> choose * from "FRANCK"."ORA$SA$TE_DOCTORS";
choose * from "FRANCK"."ORA$SA$TE_DOCTORS"
                       *
ERROR at line 1:
ORA-08709: Reads from SQL assertion auxiliary tables are restricted.
Assist: https://docs.oracle.com/error-assist/db/ora-08709/

This variation-tracking desk is used solely to determine if revalidation is required, in addition to what to lock, and revalidate solely what’s crucial, with out scanning entire tables. For instance, right here, it may well detect that it has solely to validate one SHIFT_ID and use it because the useful resource to lock, like a variety lock utilized by databases that present a serializable isolation degree.

That is a substitute for a data-modeling resolution the place we now have a “shifts” desk and use SELECT FOR UPDATE specific locking on it.

In the course of the validation, the lock sort is AN, completely different from the well-known TX and TM lock sorts, and the ksq hint reveals an unique lock acquired by Bob when it has detected that the adjustments could necessitate a validation of the assertion:

ORCLCDB_ora_7785_BOB.trc:
2026-01-31 23:44:54.587860*:ksq.c@7635:ksqcmi():ksqcmi AN-00011B78-C8CC96CA-739F2901-00000000 mode=0 timeout=0 lockmode=6 lockreq=0

The locked useful resource is a hash bucket computed from the assertion definition mixed with the SHIFT_ID worth. Alice’s session requested as comparable lock and waited on enq: AN - SQL assertion DDL/DML:

ORCLCDB_ora_7592_ALICE.trc:
2026-01-31 22:55:53.607221*:ksq.c@7635:ksqcmi(): ksqcmi AN-00011B78-C8CC96CA-739F2901-00000000 mode=6 timeout=21474836 lockmode=0 lockreq=0

That is the place Alice waited throughout her replace, and earlier than the validation of the assertion.

Conclusion

With Oracle 23.26.1, SQL assertions lastly transfer a protracted‑standing SQL‑92 function from principle into sensible enforcement, combining declarative constraint semantics with change monitoring and tremendous‑grained locking.

There at the moment are three options to resolve the physician’s on-call shift:

• Serializable isolation degree (not accessible in Oracle Database) the place the appliance verifies the enterprise logic earlier than updating.
• Knowledge modelling that permits specific application-level locking (accessible in all databases even when not a part of the SQL normal) with parent-child relationship as international keys or in a single doc.
• SQL Assertion to deploy the enterprise logic within the database (at present accessible solely the newest launch of Oracle Database)

Remaining tip: remember to grant create assertion as it isn’t a part of the grant useful resource privileges.