How to check transaction rollback

Run BEGIN, an INSERT, then ROLLBACK, and count the rows again: the count returns to what it was. On SQLite 3.50.6 the insert took the table from 1 row to 2, and after ROLLBACK it was 1 again. The harder case is a failed statement, which aborts itself and leaves the transaction open.

Why check this

Rollback is the promise a service makes when a multi-step write fails halfway. An order is inserted, the payment record fails, and the order must not survive. Test it on every path that writes more than one row, and again after a migration changes a constraint, because a constraint is what turns a silent partial write into a visible error.

The failure this catches is the partial order. A batch importer that runs in autocommit inserts 500 of 1000 rows, reports an error, and leaves the half-import in the table. The next run fails on the rows already there, and the support ticket says the import is broken rather than half-done.

Prerequisites

• A sqlite3 command line client. The transaction documentation covers BEGIN, COMMIT and ROLLBACK. The shell used here is the Android platform-tools build, 3.50.6, 32-bit.
• Node 22, which bundles node:sqlite over SQLite 3.51.3, for the checks that need an application holding the connection. Two clients, two library versions, and they do not agree on every pragma, so name yours when you report a result.
• A table with a constraint you can violate on demand. Save this as schema.sql.

CREATE TABLE orders (id INTEGER PRIMARY KEY, ref TEXT NOT NULL UNIQUE, total INTEGER NOT NULL);
INSERT INTO orders (ref, total) VALUES ('A-1000', 500);

• Create the database with sqlite3 shop.db < schema.sql before each run, so every count starts from one row.
• This page runs against SQLite. The mechanics of BEGIN and ROLLBACK carry over to other engines, the behaviour after a failed statement does not, and the difference is the whole point of step 2.

Steps

1. 1

   Step 1.

   Confirm rollback undoes an ordinary write.

   sqlite3 shop.db "BEGIN; INSERT INTO orders (ref,total) VALUES ('A-1001', 700); SELECT count(*) FROM orders; ROLLBACK; SELECT count(*) FROM orders;"
   

   2
   1

   Two counts from one command. Inside the transaction the table has 2 rows; after ROLLBACK it has 1. That is the answer most people are looking for, and it is the easy half.

2. 2

   Step 2.

   Make a statement fail inside the transaction, then commit anyway.

   sqlite3 shop.db <<'EOF'
   .bail off
   BEGIN;
   INSERT INTO orders (ref,total) VALUES ('A-1001',700);
   INSERT INTO orders (ref,total) VALUES ('A-1000',900);
   COMMIT;
   EOF
   

   Runtime error near line 4: UNIQUE constraint failed: orders.ref (19)

   The duplicate was rejected. Now read the table.

3. 3

   Step 3.

   Count what survived the error.

   sqlite3 shop.db "SELECT id, ref, total FROM orders;"
   

   1|A-1000|500
   2|A-1001|700

   Row 2 is there. The UNIQUE violation aborted the failing statement only. The transaction stayed open, COMMIT ran, and the earlier insert became permanent. Nothing rolled back, and the client saw an error.

4. 4

   Step 4.

   Watch the same thing from the application, where the error is caught. Save this as rollback-probe.mjs.

   import { DatabaseSync } from 'node:sqlite';
   
   const db = new DatabaseSync('shop.db');
   const rows = () => db.prepare('SELECT count(*) AS n FROM orders').get().n;
   
   // Probe: BEGIN throws only when a transaction is already open.
   function openTransaction() {
     try { db.exec('BEGIN'); db.exec('ROLLBACK'); return false; } catch { return true; }
   }
   
   console.log('before        rows=%d open=%s', rows(), openTransaction());
   db.exec('BEGIN');
   db.exec("INSERT INTO orders (ref,total) VALUES ('A-1001',700)");
   try {
     db.exec("INSERT INTO orders (ref,total) VALUES ('A-1000',900)");
   } catch (err) {
     console.log('caught        %s', err.message);      // the service logs and carries on
   }
   console.log('after error   rows=%d open=%s', rows(), openTransaction());
   db.exec('COMMIT');
   console.log('after commit  rows=%d open=%s', rows(), openTransaction());
   

   Recreate the database, then run it.

   rm -f shop.db && sqlite3 shop.db < schema.sql && node --disable-warning=ExperimentalWarning rollback-probe.mjs
   

   before        rows=1 open=false
   caught        UNIQUE constraint failed: orders.ref
   after error   rows=2 open=true
   after commit  rows=2 open=false

   Read the open column. After the caught error the transaction is still open, so the handler that logs and moves on is holding an uncommitted write. The COMMIT two lines later keeps it.

5. 5

   Step 5.

   Compare a batch with a transaction against the same batch without one. Save this as batch.mjs.

   import { DatabaseSync } from 'node:sqlite';
   import { rmSync } from 'node:fs';
   
   function load(label, wrap, refs) {
     rmSync('batch.db', { force: true });
     const db = new DatabaseSync('batch.db');
     db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, ref TEXT NOT NULL UNIQUE)');
     const ins = db.prepare('INSERT INTO t (ref) VALUES (?)');
     const t0 = performance.now();
     let note = 'all inserted';
     try {
       if (wrap) db.exec('BEGIN');
       for (const ref of refs) ins.run(ref);
       if (wrap) db.exec('COMMIT');
     } catch (err) {
       if (wrap) db.exec('ROLLBACK');
       note = err.message;
     }
     const n = db.prepare('SELECT count(*) AS n FROM t').get().n;
     console.log('%s  %s ms  rows=%d  %s', label, (performance.now() - t0).toFixed(0).padStart(5), n, note);
     db.close();
   }
   
   const clean = Array.from({ length: 1000 }, (_, i) => 'r' + i);
   const dirty = [...clean];
   dirty[500] = 'r0';                       // one duplicate halfway through
   
   load('autocommit, clean batch ', false, clean);
   load('transaction, clean batch', true, clean);
   load('autocommit, one dup     ', false, dirty);
   load('transaction, one dup    ', true, dirty);
   

   Run it. The script builds its own database, so no setup is needed.

   node --disable-warning=ExperimentalWarning batch.mjs
   

   autocommit, clean batch    3483 ms  rows=1000  all inserted
   transaction, clean batch      4 ms  rows=1000  all inserted
   autocommit, one dup        1321 ms  rows=500  UNIQUE constraint failed: t.ref
   transaction, one dup          2 ms  rows=0  UNIQUE constraint failed: t.ref

   Line 3 is the partial import: 500 rows kept after the same error that line 4 undid completely. Lines 1 and 2 are the cost of the missing transaction, 3483 ms against 4 ms for the identical work, because each unwrapped insert commits on its own.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | The count returns to its starting value after ROLLBACK | Rollback works on this path | Repeat with a failing statement in the middle, as in step 2. | | Rows remain after an error the client reported | The failing statement aborted, the transaction did not | Roll back in the error handler, do not rely on the engine. | | open=true after a caught error | The connection still holds an uncommitted write | Find the catch block that swallowed the error and add a ROLLBACK. | | Row counts between 0 and the batch size | The loop ran in autocommit | Wrap the loop in one transaction and rerun. |

Common mistakes

What to check next

• How to check transaction isolation level: what another connection sees while your transaction is open.
• How to check database locks: the other way a transaction ends, when the commit cannot get the lock.
• How to test unique constraint: the constraint that produced the error in step 2.
• How to test database migration: whether the down path rolls back as cleanly as this one.
• How to verify row counts after migration: the count that catches a partial write after the fact.

FAQ

How do I test transaction rollback in a unit test?

Assert on the row count before and after, not on the absence of an exception. Force the failure with a constraint violation, catch it, then read the table through a second connection. A test that only asserts that an error was thrown passes against the partial write in step 3.

Does DDL roll back?

In SQLite it does. A CREATE TABLE and an ALTER TABLE inside a transaction both disappeared after ROLLBACK here, and .schema showed the original definition. Other engines commit implicitly on DDL, which is not verified on this machine, so check it on yours before writing a migration that depends on it.

Why did my rollback report that no transaction is active?

The transaction was closed before the handler reached it. SQLite ends the transaction itself on a few errors, and the sqlite3 shell discards an open one at exit. Log the result of the BEGIN probe from step 4 next to the error to tell the two cases apart.

Is autocommit ever the right choice?

For a single statement, yes, since it already runs in its own transaction. For a loop, it removes the only thing that makes the batch recoverable, and it was 870 times slower on the measurement in step 5.

Verified