cannot commit/rollback - no transaction is active

This error occurs when you try to COMMIT or ROLLBACK without an active transaction. Usually caused by duplicate commits or missing BEGIN.

The no transaction is active error means you tried to end a transaction that doesn't exist.

Understanding the Error

Error: cannot commit - no transaction is active
Error: cannot rollback - no transaction is active

Common Causes

1. Double COMMIT

SQL

BEGIN;
INSERT INTO users VALUES (1, 'Alice');
COMMIT;
COMMIT;  -- Error: already committed

2. Missing BEGIN

SQL

-- No BEGIN
INSERT INTO users VALUES (1, 'Alice');
COMMIT;  -- Error: no transaction

3. ROLLBACK After COMMIT

JAVASCRIPT

try {
  db.exec('BEGIN');
  db.run('INSERT INTO users VALUES (1, "Alice")');
  db.exec('COMMIT');
} catch (err) {
  db.exec('ROLLBACK');  // Error if no error occurred
}

4. Auto-Commit Mode

In auto-commit mode, each statement is its own transaction:

JAVASCRIPT

// Each statement auto-commits
db.run('INSERT...');  // Auto-committed
db.exec('COMMIT');    // Error: nothing to commit

How to Fix It

Solution 1: Always Pair BEGIN/COMMIT

JAVASCRIPT

db.exec('BEGIN');
try {
  db.run('INSERT INTO users VALUES (1, "Alice")');
  db.exec('COMMIT');
} catch (err) {
  db.exec('ROLLBACK');
  throw err;
}

Solution 2: Check Transaction State

JAVASCRIPT

function safeCommit(db) {
  if (db.inTransaction) {
    db.exec('COMMIT');
  }
}

function safeRollback(db) {
  if (db.inTransaction) {
    db.exec('ROLLBACK');
  }
}

Solution 3: Use Transaction Wrapper

JAVASCRIPT

function transaction(db, fn) {
  db.exec('BEGIN');
  try {
    const result = fn();
    db.exec('COMMIT');
    return result;
  } catch (err) {
    db.exec('ROLLBACK');
    throw err;
  }
}

// Usage
transaction(db, () => {
  db.run('INSERT INTO users VALUES (1, "Alice")');
  db.run('INSERT INTO users VALUES (2, "Bob")');
});

Solution 4: Use Library Methods

JAVASCRIPT

// better-sqlite3
const insert = db.transaction((users) => {
  for (const user of users) {
    db.run('INSERT INTO users VALUES (?, ?)', [user.id, user.name]);
  }
});

insert(users);  // Handles BEGIN/COMMIT/ROLLBACK

Checking Transaction State

JAVASCRIPT

// better-sqlite3
console.log(db.inTransaction);  // true/false

// SQLite direct
const result = db.prepare('SELECT 1 WHERE sqlite3_get_autocommit(?) = 0').get();

SQL

-- Check autocommit status (0 = in transaction)
SELECT * FROM pragma_compile_options WHERE compile_option LIKE '%AUTOCOMMIT%';

Best Practices

Use transaction helpers from your library
Check inTransaction before commit/rollback
Use try/catch/finally pattern
Don't manually manage transactions if library does it
Log transaction state during debugging