Drizzle Team•9mo ago

Error with Nested Transactions

I'm experiencing an issue with nested transactions on neon serverless 0.9.1, drizzle-orm 0.30.9, and ws 8.16.0. The following works locally but not in production:

async function createOrGet<T>(
    parentTxn: DbTransaction<T>,
    entity: NewEntity,
): Promise<string | null> {
    try {
        return await parentTxn.transaction(async (childTxn) => {
            return await createInTxn(childTxn, entity)
        })
    } catch (e: unknown) {
        if (isDbErrorWithCode(e, PostgresErrorCode.UNIQUE_VIOLATION)) {
            return await findWorkflowTemplateByNameInTxn(parentTxn, entity.id)
        } else {
            return null
        }
    }
}

async function createOrGet<T>(
    parentTxn: DbTransaction<T>,
    entity: NewEntity,
): Promise<string | null> {
    try {
        return await parentTxn.transaction(async (childTxn) => {
            return await createInTxn(childTxn, entity)
        })
    } catch (e: unknown) {
        if (isDbErrorWithCode(e, PostgresErrorCode.UNIQUE_VIOLATION)) {
            return await findWorkflowTemplateByNameInTxn(parentTxn, entity.id)
        } else {
            return null
        }
    }
}

The following works in production but not locally:

async function createOrGet<T>(
    parentTxn: DbTransaction<T>,
    entity: NewEntity,
): Promise<string | null> {
    try {
      return await createInTxn(parentTxn, entity)
    } catch (e: unknown) {
        if (isDbErrorWithCode(e, PostgresErrorCode.UNIQUE_VIOLATION)) {
            return await findWorkflowTemplateByNameInTxn(parentTxn, entity.id)
        } else {
            return null
        }
    }
}

async function createOrGet<T>(
    parentTxn: DbTransaction<T>,
    entity: NewEntity,
): Promise<string | null> {
    try {
      return await createInTxn(parentTxn, entity)
    } catch (e: unknown) {
        if (isDbErrorWithCode(e, PostgresErrorCode.UNIQUE_VIOLATION)) {
            return await findWorkflowTemplateByNameInTxn(parentTxn, entity.id)
        } else {
            return null
        }
    }
}

I'd expect the 1st (parent-child transactions) to work. Instead, I get a 25P01 error NO_ACTIVE_SQL_TRANSACTION with message

error: ROLLBACK TO SAVEPOINT can only be used in transaction blocks\n at file:///var/task/dist/server/entry.mjs:148485:23\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)\n at async _NeonTransaction.transaction

2 Replies

Dan•9mo ago

i'm experiencing very similar issues in a pretty different environment -- op-sqlite on iOS I've seen both this error and "Cannot start a transaction within a transaction" I'm not even intentionally using any nested transactions, all mine are single-level with a series of statements

san4dOP•9mo ago

Hmm. I haven't found a solution to this yet. I'm in the middle up updating infra and will try again after. One thing you could try: enable DB logging. Double-check that the SQL generated is what you expect.

Gaming

Programming

Error with Nested Transactions