cannot start a transaction within a transaction

This error occurs when you try to BEGIN a transaction while one is already active. SQLite doesn't support nested transactions but does support savepoints.

The cannot start a transaction within a transaction error means you tried to start a new transaction when one is already in progress.

Understanding the Error

Error: cannot start a transaction within a transaction

SQLite doesn't support true nested transactions.

Common Causes

1. Double BEGIN

SQL

BEGIN TRANSACTION;
-- some operations
BEGIN TRANSACTION;  -- Error!

2. Forgot to COMMIT

JAVASCRIPT

db.exec('BEGIN');
db.run('INSERT INTO users VALUES (1, "Alice")');
// Forgot COMMIT
// Later...
db.exec('BEGIN');  // Error: already in transaction

3. Auto-Commit Confusion

Some libraries auto-start transactions:

JAVASCRIPT

// Library auto-started transaction
db.exec('BEGIN');  // Error: already in transaction

4. Nested Function Calls

JAVASCRIPT

function insertUser(db, user) {
  db.exec('BEGIN');  // Might already be in transaction
  db.run('INSERT...');
  db.exec('COMMIT');
}

function insertTeam(db, users) {
  db.exec('BEGIN');
  for (const user of users) {
    insertUser(db, user);  // Tries to BEGIN again!
  }
  db.exec('COMMIT');
}

How to Fix It

Solution 1: Use SAVEPOINT for Nesting

SQL

BEGIN TRANSACTION;
  INSERT INTO users VALUES (1, 'Alice');

  SAVEPOINT sp1;
    INSERT INTO posts VALUES (1, 1, 'Hello');
  RELEASE sp1;  -- or ROLLBACK TO sp1

COMMIT;

Solution 2: Check Transaction State

JAVASCRIPT

function safeTransaction(db, fn) {
  const wasInTransaction = db.inTransaction;

  if (!wasInTransaction) {
    db.exec('BEGIN');
  }

  try {
    fn();
    if (!wasInTransaction) {
      db.exec('COMMIT');
    }
  } catch (err) {
    if (!wasInTransaction) {
      db.exec('ROLLBACK');
    }
    throw err;
  }
}

Solution 3: Use Library Transaction Helpers

JAVASCRIPT

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

insertMany(users);  // Auto BEGIN/COMMIT

Solution 4: Pass Transaction Flag

JAVASCRIPT

function insertUser(db, user, { inTransaction = false } = {}) {
  if (!inTransaction) db.exec('BEGIN');

  try {
    db.run('INSERT INTO users VALUES (?, ?)', [user.id, user.name]);
    if (!inTransaction) db.exec('COMMIT');
  } catch (err) {
    if (!inTransaction) db.exec('ROLLBACK');
    throw err;
  }
}

function insertTeam(db, users) {
  db.exec('BEGIN');
  for (const user of users) {
    insertUser(db, user, { inTransaction: true });
  }
  db.exec('COMMIT');
}

SAVEPOINT Example

JAVASCRIPT

db.exec('BEGIN');

try {
  db.run('INSERT INTO orders VALUES (1, 100)');

  db.exec('SAVEPOINT items');
  try {
    db.run('INSERT INTO order_items VALUES (1, 1, 2)');
    db.run('INSERT INTO order_items VALUES (1, 2, 3)');
    db.exec('RELEASE items');
  } catch (err) {
    db.exec('ROLLBACK TO items');
    // Order still exists, items rolled back
  }

  db.exec('COMMIT');
} catch (err) {
  db.exec('ROLLBACK');
  throw err;
}

Best Practices

Use library transaction helpers when available
Use SAVEPOINT for nested operations
Track transaction state in complex code
Keep transactions short and simple
Always handle COMMIT and ROLLBACK