New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Preview feature feedback: interactiveTransactions
#8664
Comments
I love this. Very important IMHO. I'm wondering why it is called "Interactive Transaction" and not just "Transaction". What is interactive about it? |
Hey @pkellner, thanks for raising this. We need a separate concept for 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. |
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. |
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 ? |
we get the following, has anyone experienced this?
|
Hey @andreimc, Could you please share your schema and the transaction that triggers this error? |
@maxerbox It will rollback on its own, either cleaned up by the QE (if still alive) or the db. |
But what about the performance issues!!! |
@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 |
created a separate issue #9183 The vscode autocomplete for typescript is very slow inside 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.
}); |
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 |
I had no such issue on my local, but once I deployed it to the production server - it broke the whole thing :D P.S. Error appeared right after I added |
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. |
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:
|
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 In my error logs, I see the following:
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;
} |
I think I may have identified the root cause of my issue. If the transaction takes longer than |
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. 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. @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. 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 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. 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. 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 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 //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 I tried to run 2 longer transactions on one row updating different fields to evaluate the bahaviour of the transaction handling. 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. 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. |
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. |
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. |
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. |
Hey @albertorestifo, can you please open a separate issue/feature request if you want us to look into it? Thanks! |
Also it would be nice to support returning arrays of promises like in the regular transactions: Modified from unit tests:
But then again are these sequential creates or concurrent? |
Here it is #12436 |
It would be also nice to pass |
Is there a way to log generated sql query in transaction? Default logs omit them. |
Hi, I have a solution to combat the closing of the Prisma transaction early. Prisma could use |
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.
|
Is there a way to disable the timeout in development across the board? It's making debugging a pain. |
Hey @casey-chow @aniravi24 feel free to open a feature request so that we can prioritize. |
Opened! #14487 |
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:
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. |
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 |
Really looking forward to this being GA! |
@millsp sorry for the delay, opened some issues (#15211, #15212) and a discussion! |
Hello, // 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 |
@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 Please don't hesitate to tell us if you could benefit from anything else defined in the default |
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:
This is OK, but we see these methods growing a lot for many reasons.
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. |
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 |
@ricardopieper @janpio 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 |
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.
The text was updated successfully, but these errors were encountered: