Preview feature feedback: interactiveTransactions

[m-abdelwahab]

Please share your feedback about the preview of Interactive Transactions support that was released in v2.29.0 in this issue.

If you encounter a bug, please open a bug report in this repo.
If the feature is working well for you, please share this in a comment below or leave a 👍 on this issue.
If you have any questions, don't hesitate to ask them in the #prisma2 channel in the Prisma Slack.

[pkellner]

I love this. Very important IMHO. I'm wondering why it is called "Interactive Transaction" and not just "Transaction". What is interactive about it?

[matthewmueller]

Hey @pkellner, thanks for raising this.

We need a separate concept for $transaction([..., ...]) and $transaction(async tx => { ... }) because we may need to treat them differently in the future.

We decided on "interactive transaction" based on prior art that @fhut researched in this comment. The interactive part is that you can insert custom logic inside the transaction.

We decided against "client-side transaction" because we thought it could be interpreted as a browser transaction of some sort. Wrong part of the stack.

[pkellner]

I agree "client-side" would have been very confusing (I'd make up what that meant and I'd be wrong). The good news I suppose is that I don't even have a guess (without reading the doc) for what an interactive transaction might be so at least I wouldn't be thrown off. Naming things is hard and thankless.

[maxerbox]

What happens if my nodejs app process is killed instantly when I have a running transaction ? Does it rollback automatically, or cause a dead lock ?

[andreimc]

we get the following, has anyone experienced this?

TypeError: Cannot read property 'id' of undefined     at PrismaService._transactionWithCallback (/workspace/libs/legacy-prisma/dist/runtime/index.js:36883:61)     at runMicrotasks (<anonymous>)     at processTicksAndRejections (internal/process/task_queues.js:95:5)
--

[2color]

Hey @andreimc,

Could you please share your schema and the transaction that triggers this error?

[millsp]

@maxerbox It will rollback on its own, either cleaned up by the QE (if still alive) or the db.

[hugbubby]

But what about the performance issues!!!

[millsp]

@hugbubby would you mind sharing your experience with more detail? Thanks!

[hugbubby]

@hugbubby would you mind sharing your experience with more detail? Thanks!

I'm joking. This is a critical feature. Thank you for finally releasing it and for the product in general <3

[maxerbox]

@hugbubby would you mind sharing your experience with more detail? Thanks!

I'm joking. This is a critical feature. Thank you for finally releasing it and for the product in general <3

Would be interesting anyway to have a benchmark :P

[michael-land]

created a separate issue #9183

---

The vscode autocomplete for typescript is very slow inside interactiveTransactions.

my prisma file have 2k lines and generated d.ts have 140k lines.

// takes about 100 ms to show the autocomplete results
await prisma.

// takes about 15-30seconds to show the autocomplete results
await prisma.$transaction(async (tPrisma) => {
  tPrisma.
});

[deodad]

Running on preview on 2.30.2. It would be great to have an exported type for a transactional client, something like PrismaTransactionClient, so functions can easily be typed that accept a client from prisma.$transaction

[spy4x]

we get the following, has anyone experienced this?

TypeError: Cannot read property 'id' of undefined     at PrismaService._transactionWithCallback (/workspace/libs/legacy-prisma/dist/runtime/index.js:36883:61)     at runMicrotasks (<anonymous>)     at processTicksAndRejections (internal/process/task_queues.js:95:5)
--

I had no such issue on my local, but once I deployed it to the production server - it broke the whole thing :D
I've reverted it back and going to figure out why it happens only on prod. I'll report here once I find the reason (or fail to do so).

P.S. Error appeared right after I added previewFeatures = ["interactiveTransactions"] to my schema file.

[spy4x]

Well... couldn't figure out an issue, but I updated to Prisma v3.1.1 and it fixed the issue, but it brought another one - now I have CPU "leak" and queries fail by timeout, lol.
I think I should create a separate issue about it here.

[jsoneaday]

My issue occurs sporadically. Basically I make two calls that internally are using interactive transactions. I await on both of them. However the second call gives an error like "An operation failed because it depends on one or more records that were required but not found. No 'Profile' record(s) (needed to inline the relation on 'Message' record(s)) was found for a nested connect on one-to-many relation 'MessageToProfile'." If I check the Profile object it's non-null.

Is it possible that the transaction returns the js object before it's completely inserted into the db?

Also when is this scheduled for official release?

Here's the first function I run:

export async function createProfile(
  prisma: PrismaClient,
  userName: string,
  fullName: string,
  email: string,
  bio: string,
  region: string,
  website: string | null,
  { transactionId, transWalletAddr, signature }: TransactionParams
): Promise<Profile | null> {
  try {
    const profile = await prisma.$transaction(async (pa) => {
      const transaction = await createTransaction(prisma, {
        transactionId,
        transWalletAddr,
        signature,
      });

      const profile = prisma.profile.create({
        data: {
          userName,
          fullName,
          email,
          bio,
          region,
          website,
          walletAddress: transWalletAddr,
          transaction: {
            connect: { id: transaction!.id },
          },
        },
      });

      return profile;
    });

    return profile;
  } catch (e) {
    throw e;
  }
  return null;
}

[awinograd]

I'm seeing behavior similar to @jsoneaday. The transaction returns the object, but if I try to query it afterwards, occasionally it doesn't exist in the database. In my below example, I believe loadedPatient should always exist if the transaction doesn't error (assuming no other DB operations take place in a different request, e.g. a delete)

In my error logs, I see the following:

1. newPatient ID
2. ensureClaims ID
3. patient record
4. ERROR patient record not found

    try {
    const patient = context.prisma.$transaction(async (prisma) => {
      const newPatient = await prisma.userPatient.create({
        data: {
         // ...
        },
      });
      console.log('newPatient', newPatient.patient_id, email);

      await ensureUserWithClaims(context.auth, username, email, {
        ...DefaultPatientClaims,
        product: organization?.productVersion || DefaultPatientClaims.product,
      });

      console.log('ensureClaims', newPatient.patient_id);

      return newPatient;
    });

    console.log(patient);

      const loadedPatient = await context.prisma.userPatient.findUnique({
        where: { patient_id: patient.patient_id },
      });
      if (!loadedPatient) {
        throw new Error('patient record not found after creation:' + pID);
      }
      return modelPatient(patient);
    } catch (e) {
      if (e instanceof PrismaClientKnownRequestError) {
        if (e.code === 'P2002') {
          throw new EmailInUseError();
        }
      }
      throw e;
    }

[awinograd]

I think I may have identified the root cause of my issue. If the transaction takes longer than timeout, the transaction returns normally as if it succeeded, even thought it has been rolled back due to the timeout.

[mamichels]

Hey everyone,

first of all, while it is my first project using Prisma I generally have lots of good feelings about it and certainly will use it for production.
While lots of basic use cases just work and feel fine, I currently feel that this is not the case for transaction handling with Prisma. Maybe its just me and I am on the wrong track here. I am really open for some clarification.

This is my experience as a dev working with prisma transactions, not so much a bug report. Sorry for the long post.

Since our code base is very much enterprisified, I try to give you some pseudo code here.
My issue is mostly concerning the scope of the transaction client. For a better understanding, please try to understand my point of view.
I also do lot of java stuff so this syntax would be my desired (dream) state, since I do not want to deal with transaction handling in detail if I dont need to.

@transactional()
public async doComplexOperation(id: number): Promise<any> {
    const obj = this.dao.retrieveObjefct(id)
    this.otherService.handleComplexProcessStuff(obj) // this may also trigger some writes further down.

    obj.updateTs = new Date().toISOString();

    return this.dao.update(obj);
}

Within our DAOs we would normally retrieve our PrismaClient, which is currently implemented as singleton, and run our basic CRUD operations on this instance.
E.g. like this:

class Dao {

    public async update(obj: TestObject): Promise<TestObject> {
        const updateQuery = {
            where: {
                id: obj.id
            },
            data: {
                updateTs: obj.updateTs,
            }
        };

        try {
            return await this.db.client.TestObject.update(updateQuery);
        } catch (e) {
            throw new CustomError();
        }
    }

}    

In which this.db.client refers to the following:

// this is a singleton
class DbContext {
    private readonly _prismaClient = new PrismaClient();

    public get client(): PrismaClient {
        return this._prismaClient;
    }
}

All of this feels goods and works like a charm. Now my app evolves and my business logic requires me to work in transactions.
While I can understand the opinion that (complex) transactions should be avoided and can surely be replaced by something else, this feature would require me to write something like this:

public async myBusinessLogic(id: number): Promise<any> {
    const dbClient = db.client; // now I have my dbContext within my business logic, certainly dont want that
    dbClient.$transact(async transactionClient => {
        //pass around the transactionClient in my business logic for further use
    })
}

Since I really can't pass around the transaction client like this, I came up with the following.
My business logic now looks like this:

public async doComplexOperation(id: number): Promise<any> {
    return await prismaTransact(async () => {
        const obj = this.dao.retrieveObjefct(id)
        this.otherService.handleComplexProcessStuff(obj) // this may also trigger some writes further down.

        obj.updateTs = new Date().toISOString();

        return this.dao.update(obj);
    }).catch(() => {
      throw new ApiError('Some error occured during the transaction.');
    });
}

The function prismaTransact wraps the $transaction and sets the transaction client to our RequestContext, in which we also track certain variables e.g. unique request id.

export const prismaTransact: (f) => Promise<any> = async (f) => {
  const prismaClient = db.client // Pseudo code: Here we retrieve our PrismaClient from our singleton above, just a bit more complex;

  return await prismaClient.$transaction(async transactionClient => {
    const id = context.get(RequestContext.REQUEST_ID);
    context.set(getTransactionClientKey(id), transactionClient); // this is our RequestContext, just a map of variables. In our Case It looks somewhat like: {'transaction_client_4d95991a-f12a-49f9-9f19-96a862b5e846': transactionClient}
    return await f();
  });
}

Now when I retrieve my PrismaClient I just check whether im currently in a transaction or not.

//This is a singleton
class DbContext {

    private readonly _prismaClient = new PrismaClient();

    public get client(): PrismaClient {
        return this.transactionClient ?? this._prismaClient;
    }

    private get transactionClient(): PrismaClient | undefined {
        return context.get(RequestContext.TRANSACTION_CLIENT + context.get(RequestContext.REQUEST_ID));
    } 
}

All of this works fine for a given transaction. When i wrap my business logic with prismaTransact it uses the transactionClient as is would expect.
I was curious and debugged for a few hours to ensure its working as I would expect it. My observations were weird and astonishing, which is probably just me forcing some weird behaviour.

I tried to run 2 longer transactions on one row updating different fields to evaluate the bahaviour of the transaction handling.
In case the transactions do not timeout the first transaction does not throw but is also just overwritten by the later one.
I would have expected that it would throw due to a concurrency error since I updated a field on the same row with the other transaction.

In case I forced one transaction to timeout it obviously throwed and rolled back as expected.

To ensure the correct transaction handling I would have assumed that I have 2 instances of transaction clients during my tests since those are within the scope of 2 different requests.
What made me feel a bit uneasy was the following addition to the above stated code.

To track which tranaction client was used for my operations I have added the id to the client itself:

export const prismaTransact: (f) => Promise<any> = async (f) => {
  const prismaClient = db.client // Pseudo code: Here we retrieve our PrismaClient from our singleton above, just a bit more complex;

  return await prismaClient.$transaction(async transactionClient => {
    const id = context.get(RequestContext.REQUEST_ID);
    transactionClient['id'] = id; // We just set it here for later.
    context.set(getTransactionClientKey(id), transactionClient); 
    return await f();
  });
}

Whenever I retrieve my client I added following log:

    public get client(): PrismaClient {
        if (this.transactionClient) {
          console.log('Using transactional client with id: ' + this.transactionClient.id); // Keep in mind, that this id was build with the unique request id.
        }
        return this.transactionClient ?? this._prismaClient;
    }

Now back to my business logic which I wrapped somewhat like this:

public async doComplexOperation(id: number): Promise<any> {
    return await prismaTransact(async () => {
        const obj = this.dao.retrieveObjefct(id)
        this.otherService.handleComplexProcessStuff(obj) // this may also trigger some writes further down.
        console.log('before: + ' request id) // retrieved from RequestContext
        // sleep(1000) - Promise await stuff
        obj.updateTs = new Date().toISOString();
        console.log('after: + ' request id) // retrieved from RequestContext

        return this.dao.update(obj);
    }).catch(() => {
      throw new ApiError('Some error occured during the transaction.');
    });
}

Sending a request through the method above and another non delayed different, but also transactional, method would result in somewhat like this

Using transactional client with id: 1
before: 1
Using transactional client with id: 2
Using transactional client with id: 2
after: 1
Using transactional client with id: 2

The last output should have stated to that the transactinal client with id 1 should be used. Since the RequestContext seems to work in general (e.g. on the request id) it feels like parallel transactions share a client and I have no idea why this is the case or if I should have expect it to behave like this. This feels off, am I completly wrong here? It feels like the RequestContext might be the culprit but this would be the first case in which it would misbehave.

Anyway, since this is more of a preview feature this certainly will not block us in going further with Prisma. I am just curious on your thoughts on handling more complex transactions like this.

[BarrySJL]

Our project need callbacks such as "afterCommit" , "afterRollback" in the transaction. so we could do some logic in callback. I look up the doc and did not find a way to listen those events. Our team hope the interactiveTransactions feature can add these callbacks.

[janpio]

That sounds like a separate feature request @BarrySJL. Please open a new issue for that, and include motivation and if possible a potential implementation as an example as well.

[pascalporedda]

I'm not sure, but I haven't found anything in the documentation that states, that one has to return something from an interactive transaction. In our case, we haven't returned anything, and the transaction was never committed.
Once we added a simple return true to the end of the transaction function, everything worked well.

[millsp]

Hey @albertorestifo, can you please open a separate issue/feature request if you want us to look into it? Thanks!

[leppaott]

Also it would be nice to support returning arrays of promises like in the regular transactions:

Modified from unit tests:

    const result = await prisma.$transaction(async (prisma) => {
      await prisma.user.create({
        data: {
          email: 'user_1@website.com',
        },
      })
      const promises = [prisma.user.create({}), prisma.user.create({})]
      return promises
    })

    expect(users.length).toBe(3)
  })

But then again are these sequential creates or concurrent?

[ziimakc]

It would be nice to expose the type that is passed to the $transaction function, so we can import it and use it in our applications.

Here it is #12436

[ziimakc]

It would be also nice to pass trx around like described here: https://vincit.github.io/objection.js/guide/transactions.html#using-a-transaction

[ziimakc]

Is there a way to log generated sql query in transaction? Default logs omit them.

[millsp]

Hey @ziimakc, this is something we're going to work on #12145.

[Akxe]

Hi, I have a solution to combat the closing of the Prisma transaction early. Prisma could use FinalizationRegistry to detect if the Prisma passed to the function is used at all, and if the Prisma object is still being used.

[aniravi24]

Excited to see more development of this feature! I've looked around the discussions and issues to see if these were addressed, but wanted to ask here more directly (also let me know if I should open issues for any of these):

Just for more context, we use PostgreSQL.

1. We tend to write more code in a functional style and avoid throwing errors if unnecessary to do so - it would be awesome to have a manual rollback function so we don't have to throw errors to have transactions rollback.
2. Is it possible to support nested transactions even if the underlying database doesn't support it? Meaning, you would call $prisma.transaction, which may contain another call to prisma.$transaction. In languages like Elixir for example, Ecto is capable of seamlessly nesting transactions and handling them gracefully.
3. If you have an interactive transaction that contains a nested write, will that cause an issue since nested writes are done with transactions?

[casey-chow]

Is there a way to disable the timeout in development across the board? It's making debugging a pain.

[millsp]

Hey @casey-chow @aniravi24 feel free to open a feature request so that we can prioritize.

[casey-chow]

Opened! #14487

[iiAku]

I really like the interactiveTransactions it fits a lot of needs into what I'm currently working on. Thanks for providing such a feature.

I am aware of this:

Note: If you use interactive transactions, then you cannot use the Data Proxy at the same time.

However, I was just wondering if that was basically something being worked on to be supported. My go-to solution, for now, would be to use PgBouncer as stated here but I guess a bunch of people would be willing to use the alternative solution Data Proxy instead.

[Akxe]

Could we get a promise wrapper that we can use to prevent timeout when a non-Prisma task is being processed? Just a special method on the passed Prisma object, something like awaitForieign?

[jacobleecd]

Really looking forward to this being GA!

[aniravi24]

@millsp sorry for the delay, opened some issues (#15211, #15212) and a discussion!

[ragrag]

Hello,
One thing i find a little bit annoying is that Prisma.TransactionClient can only be used after type generation w/prisma generate.
this limitation forces low level libraries that need to only deal with prisma internal types to actually have a dummy placeholder schema otherwise type generation is not possible, e.g:

// only used for type generation, which is kinda hacky
generator client {
    provider        = "prisma-client-js"
    previewFeatures = ["interactiveTransactions"]
}

datasource db {
    provider = "postgresql"
    url      = "postgres://postgres:postgres@127.0.0.1:5432/placeholder"
}

model Placeholder {
    id Int @id
}

// ...some other file
import { Prisma, PrismaClient } from '@prisma/client'; // Prisma only available after type generation

...

const runInTransaction = async <TReturn>(
    connection: PrismaClient,
    fn: (tx: Prisma.TransactionClient) => TReturn,
): Promise<TReturn> => {
    return connection.$transaction(async tx => {
        return fn(tx);
    });
};

...

tldr: it would be really nice to have an export for TransactionClient directly from @prisma/client without the need to generate prisma db specific types

[aqrln]

@ragrag thank you for this feature request, this will be added when interactive transactions go to general availability soon (see #16257). Note that just like regular PrismaClient, Prisma.TransactionClient stub in placeholder types will be just an alias to any — a more specific type is not possible here without the schema.

Please don't hesitate to tell us if you could benefit from anything else defined in the default index.d.ts — until now the whole Prisma namespace was missing there.

[ricardopieper]

I found a way to expose the transaction using some ""clever"" promise handling, which should be good for transactions that cross repositories and services. I am not sure it's 100% safe or reliable, but here is what we have right now:

Today Prisma offers us the following:

await prisma.$transaction(prismaTransaction => {
    **everything inside this lambda**
})

This is OK, but we see these methods growing a lot for many reasons.
However, I found a way to make this possible:

const tx = await transactionProvider.createInteractiveTransaction();

//pass the transaction along to other repo/service/etc methods
const user = await userRepo.create(..., tx);
const purchase = await purchaseRepo.create(..., tx);

//tx object has all the prisma methods and properties in a Prisma.TransactionClient
const users = await tx.users.findMany(...);

//with extra methods
await tx.commit();
await tx.rollback();

The downside is you must remember to always commit or rollback a transaction, while $transaction does it for you, but this is not unheard of in other DB abstractions.

Another downside is passing a Prisma object along the repositories, which is not very desirable... maybe some kind of integration with the techniques displayed here https://dev.to/kenfdev/cross-module-transaction-with-prisma-5d08 would make it better.

[janpio]

Thanks for all your feedback! Interactive transactions went generally available in 4.7.0. See the docs at https://www.prisma.io/docs/concepts/components/prisma-client/transactions#interactive-transactions

[nimeshvaghasiya]

Another downside is passing a Prisma object along the repositories, which is not very desirable... maybe some kind of integration with the techniques displayed here https://dev.to/kenfdev/cross-module-transaction-with-prisma-5d08 would make it better.

@ricardopieper @janpio
Agreed! Sometime your code goes very lengthy, and you have to divide it into small peace of functions to make it more understandable. Most of devs are following pattern/layered etc... code and would not like to pass transactional prisma(tx) as parameters to every sub functions.

connection-transaction-management-in-asp-net-boilerplate

"If a unit of work method calls another unit of work method, both use the same connection & transaction. The first entered method manages the connection & transaction and then the others reuse it."

Not sure this is doable in prisma, if it is then will add more value to interactive-transactions