In my previous post I introduced SQL virtual tables and how to use them in Go—registering modules per connection, defining schemas, and querying external sources as regular tables.

Now let’s dive into more advanced virtual-table implementations: those that support writes and full transactional behavior.

Writes and Transaction Support in Virtual Tables

SQLite’s virtual-table interface isn’t read-only. By implementing xUpdate you can expose writable tables over any data source. But true transactional integrity requires more than just row-level updates—it requires hooks for transaction boundaries:

• xBegin: Signal the start of a transaction.
• xSync: Prepare work for a durable commit; failures here abort everything.
• xCommit: Finalize the transaction (cleanup only).
• xRollback: Revert changes if the transaction is aborted.

But what happens when your virtual table is modified alongside other virtual tables—or normal tables? How does SQLite ensure everything commits or rolls back atomically?

SQLite Transactions Under the Hood

Before tackling vtable hooks, let’s review how SQLite handles transactions by default.

Rollback Journals

In its simplest mode, SQLite uses a rollback journal. Before overwriting any page, it writes the original page to a journal file. If something goes wrong, SQLite restores from the journal to guarantee atomicity.

Note: SQLite also supports WAL mode, but that’s outside the scope of this post.

Super-Journals for Multiple Databases

If you attach additional databases, a single rollback journal per file can’t coordinate commits across them. Enter the super-journal: a top-level journal file that spans all affected databases, ensuring a multi-file commit remains atomic.

For multiple virtual tables within the same database file, however, the standard rollback journal suffices; no super-journal is required. In all cases—whether multiple vtables in one file or across attached databases—virtual-table hooks (xSync, xCommit, xRollback) are invoked as part of SQLite’s transaction process.

Two-Phase Commit with Virtual Tables

SQLite’s two-phase commit breaks down as follows:

1. Phase One (xSync)

   • SQLite writes and syncs all pages (or journals) to disk for every B-tree and attached database.
   • For virtual tables, it invokes each module’s xSync hook. If any xSync fails, the entire transaction is rolled back—atomicity is preserved.

2. Phase Two (Cleanup)

   • Once durability is guaranteed, SQLite cleans up journal files and finalizes the commit.

Below is the core of phase-two logic from vdbeaux.c. Notice that errors are deliberately ignored—this is purely cleanup:

/* All files and directories have already been synced, so the following
** calls to sqlite3BtreeCommitPhaseTwo() are only closing files and
** deleting or truncating journals. If something goes wrong while
** this is happening we don't really care. The integrity of the
** transaction is already guaranteed, but some stray 'cold' journals
** may be lying around. Returning an error code won't help matters.
*/
disable_simulated_io_errors();
sqlite3BeginBenignMalloc();
for(i=0; i<db->nDb; i++){
  Btree *pBt = db->aDb[i].pBt;
  if( pBt ){
    sqlite3BtreeCommitPhaseTwo(pBt, 1);
  }
}
sqlite3EndBenignMalloc();
enable_simulated_io_errors();

/* Now perform virtual-table cleanup */
sqlite3VtabCommit(db);

And in vtab.c, the virtual-table commit hook is similarly treated as best-effort:

/* From vtab.c: errors in xCommit are ignored—this is purely cleanup. */
int sqlite3VtabCommit(sqlite3 *db){
  callFinaliser(db, offsetof(sqlite3_module,xCommit));
  return SQLITE_OK;
}

Because xSync already ensured all data is safely on disk, SQLite ignores return codes from xCommit and xRollback. These hooks should only remove temporary state (journals, locks) and must not perform work that can fail.

Implications for Virtual-Table Authors

• Durability must go in xSync. Anything that can fail—network I/O, disk writes—belongs in xSync so errors here abort the transaction.
• xRollback may still be called after xSync. Even though xSync needs to take care of durability, operations may still need to be rolled back if another xSync failed.
• Keep xCommit and xRollback idempotent. Perform only cleanup; avoid any operation that can fail.

By now, you should understand how SQLite’s rollback and super-journal mechanisms coordinate atomic commits, and how your virtual tables hook into that two-phase process to ensure consistency across both built-in and custom tables.