Prisma•6d ago

Do transactions work when you nest them?

Hello, I'm trying to figure out if transactions work correctly when you nest them. In my example I've got a couple of pretty complex business-logic-required queries, and each might have their own transactions within them:

import prisma from "./db.server"
export const reorderAllChildEntities = (entity, prismaInstance?) => {
    return (prismaInstance ?? prisma).$transaction(tPrisma => {
         // do stuff with tPrisma (actual code is way to big for this)
    })
}

// in other file
export const deleteEntityAndReorderAllChildEntitiesForParent = ( entity ) => {
    return prisma.$transaction(tPrisma => {
           await tPrisma.entity.delete({where: {id: entity.id}})
           const parentEntity = await prisma.parentEntity.find(
              {where: {id: entity.parentID}}
           )
           await reorderAllChildEntities(parentEntity, tPrisma)
    })
}

import prisma from "./db.server"
export const reorderAllChildEntities = (entity, prismaInstance?) => {
    return (prismaInstance ?? prisma).$transaction(tPrisma => {
         // do stuff with tPrisma (actual code is way to big for this)
    })
}

// in other file
export const deleteEntityAndReorderAllChildEntitiesForParent = ( entity ) => {
    return prisma.$transaction(tPrisma => {
           await tPrisma.entity.delete({where: {id: entity.id}})
           const parentEntity = await prisma.parentEntity.find(
              {where: {id: entity.parentID}}
           )
           await reorderAllChildEntities(parentEntity, tPrisma)
    })
}

If deleteEntityAndReorderAllChildEntitiesForParent fails from inside the transaction within reorderAllChildEntities function, will BOTH transactions be rolled back correctly, or is this bad practice?

1 Reply

RaphaelEtim•5d ago

Hi @Jon Higger (He / Him) We do have an open feature request to support nested transactions as shown here. I'll advise you structure your code to use a single transaction that encompasses all the operations you want to perform atomically.

export const deleteEntityAndReorderAllChildEntitiesForParent = (entity) => {
  return prisma.$transaction(async (tPrisma) => {
    await tPrisma.entity.delete({ where: { id: entity.id } });
    const parentEntity = await tPrisma.parentEntity.findUnique({
      where: { id: entity.parentID }
    });
   
    // ...
  });
};

export const deleteEntityAndReorderAllChildEntitiesForParent = (entity) => {
  return prisma.$transaction(async (tPrisma) => {
    await tPrisma.entity.delete({ where: { id: entity.id } });
    const parentEntity = await tPrisma.parentEntity.findUnique({
      where: { id: entity.parentID }
    });
   
    // ...
  });
};

This approach ensures that all operations are part of a single transaction and will be rolled back together if any part fails.

GitHub

Issues · prisma/prisma

Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, MongoDB and CockroachDB - Issues · prisma/prisma

Gaming

Programming

Do transactions work when you nest them?