Dyalog 18.0 ⎕SQL auto-commit change: retrying failed writes without duplicating rows

Confirmed Change in Transaction Semantics

Between Dyalog 16.0 and 18.0, ⎕SQL shifted from implicit transaction handling to explicit control. In 16.0, statements executed outside explicit blocks would auto-commit as a unit. In 18.0, any statement not wrapped in BEGIN TRANSACTION…COMMIT or ROLLBACK runs in auto-commit mode per statement. This means a multi-statement operation that fails mid-way may leave partial changes committed.

Idempotency Through Unique-Key Constraints

The documented mechanism for retry-safe INSERTs in 18.0 is application-level idempotency enforced by a unique-key constraint on the target table. When a write fails and the application retries, the constraint prevents duplicate rows. Dyalog itself provides no automatic rollback on interpreter interrupt; the responsibility lies with the application to handle retries safely.

Retry Pattern for INSERT Operations

1. Ensure a unique constraint or primary key exists on the target table for the columns that define row identity.
2. Wrap INSERT in explicit transaction when atomicity is required:

   ⎕SQL 'BEGIN TRANSACTION'
   ⎕SQL 'INSERT INTO table (col1, col2) VALUES (val1, val2)'
   ⎕SQL 'COMMIT'

3. On failure, retry the statement. The unique constraint will reject duplicates if the first attempt partially committed.
4. If using multi-row inserts, verify ⎕ML and ⎕IO remain unchanged across retries to prevent silent column misalignment.

Array-Valued Write Considerations

For INSERT statements with array arguments, ⎕ML (match language) and ⎕IO (input output) affect how array indices map to columns. A mismatch between retry attempts can cause data to be written to wrong columns without raising an error. Pin these values at the start of the retry loop:

originalIO ← ⎕IO
originalML ← ⎕ML
:repeat
    ⎕IO ← originalIO
    ⎕ML ← originalML
    result ← ⎕SQL 'INSERT ...'
:until result≡0

Interpreter Interrupts and Partial Commits

If the interpreter is interrupted (e.g., Ctrl+C) during an explicit transaction block, it may not issue a ROLLBACK. This leaves any committed statements from that transaction in place. The unique-key constraint is essential to prevent duplicates on retry. There is no interpreter-level option to enforce automatic rollback or atomic re-execution of failed statements in 18.0.

Lock Timeout and Connection Settings

Lock timeout defaults may differ between versions. Check the connection string for an explicit timeout parameter to ensure behavior matches expectations from 16.0.