racket/sql: transaction rollback fails with 'a command in the transaction has already caused a commit' after ALTER TABLE

Resolving Transaction Rollback Failures

To avoid the error "a command in the transaction has already caused a commit", you must move Data Definition Language (DDL) statements—such as ALTER TABLE—outside of the (sql:transaction ...) block. In databases that do not support transactional DDL, the transaction form cannot guarantee atomicity for schema changes because the database engine forces a commit the moment the DDL command is executed.

The Cause: Implicit Commits

This behavior is a characteristic of the underlying database engine rather than a bug in racket/sql. Many RDBMS (most notably MySQL and Oracle) implement implicit commits. When a DDL statement is encountered, the engine automatically commits any pending changes and completes the current transaction before executing the schema change.

When racket/sql encounters an exception later in the thunk, it attempts to issue a ROLLBACK. However, since the database has already finalized the transaction via the ALTER TABLE command, the rollback request is invalid, resulting in the reported error.

Safe Implementation Strategies

Depending on your database backend, use one of the following approaches to ensure stability:

• For Non-Transactional DDL Engines (e.g., MySQL): Execute schema changes as standalone operations. If a multi-step migration is required, implement a manual "undo" script or a versioning table to track which changes were applied, as the database cannot revert these automatically.
• For Transactional DDL Engines (e.g., PostgreSQL): These engines generally allow ALTER TABLE within a transaction. If you are seeing this error on PostgreSQL, verify that you are not calling a stored procedure or extension that triggers an internal commit.
• Engine-Agnostic Pattern: Separate your migration logic into two phases: a Schema Phase (executed without sql:transaction) and a Data Phase (executed within sql:transaction).

Verification Steps

To confirm if your specific environment supports transactional DDL, run the following test sequence in a development environment:

;; Test for implicit commit behavior
(sql:transaction
  (lambda ()
    (sql:execute db "INSERT INTO test_table (col) VALUES ('test')")
    (sql:execute db "ALTER TABLE test_table ADD COLUMN new_col INT")
    (raise (string-exception "Triggering Rollback"))))

If the INSERT persists in the database despite the exception, your engine performs implicit commits, and DDL must be handled outside of transactions.

Diagnostic Requirement

To provide a more specific recommendation, please specify the database engine and version you are using, as DDL behavior varies significantly between PostgreSQL, MySQL, and SQLite.