interactiveTransactions: 2 concurrent writes to the same row will cause it to hang until expiring #8707
Description
Bug description
I'm trying to use interactive transactions to build a bank transfer. I'd like to try to achieve the following:
- Alice and Bob have $100
- Concurrently, Alice sends Bob $100 twice
- One of those requests goes through
- The other one is rejected saying there's not enough money
- Alice has $0, Bob has $100
How to reproduce
Reproduction Repo: https://github.com/matthewmueller/interactive-transactions
Given the following Prisma Schema
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
previewFeatures = ["interactiveTransactions"]
}
model Account {
id Int @id @default(autoincrement())
email String @unique
balance Int
}And the following script:
import { PrismaClient } from "@prisma/client"
const prisma = new PrismaClient()
async function unseed() {
await prisma.account.deleteMany({
where: {
OR: [{ email: "[email protected]" }, { email: "[email protected]" }],
},
})
}
async function seed() {
await prisma.account.create({
data: {
email: "[email protected]",
balance: 100,
},
})
await prisma.account.create({
data: {
email: "[email protected]",
balance: 100,
},
})
}
async function transfer(nth: number, from: string, to: string, amount: number) {
return await prisma.$transaction(
async (prisma) => {
console.time("send " + nth)
const sender = await prisma.account.update({
data: {
balance: {
decrement: amount,
},
},
where: {
email: from,
},
})
console.timeEnd("send " + nth)
console.time("throw " + nth)
if (sender.balance < 0) {
throw new Error(`${from} doesn't have enough to send ${amount}`)
}
console.timeEnd("throw " + nth)
console.time("recieve " + nth)
const recipient = prisma.account.update({
data: {
balance: {
increment: amount,
},
},
where: {
email: to,
},
})
console.timeEnd("recieve " + nth)
return recipient
},
{
timeout: 20000,
}
)
}
async function main() {
await prisma.$connect()
await unseed()
await seed()
console.time("transfer")
await Promise.all([
transfer(1, "[email protected]", "[email protected]", 100),
transfer(2, "[email protected]", "[email protected]", 100),
])
console.timeEnd("transfer")
}
main()
.catch(console.error)
.finally(() => prisma.$disconnect())If you setup a database and then run ts-node index.ts, you'll get:
send 1: 7.005ms
throw 1: 0.005ms
recieve 1: 0.279ms
send 2: 20.005s
throw 2: 0.367ms
recieve 2: 0.644ms
PrismaClientKnownRequestError3 [PrismaClientKnownRequestError]:
Invalid `prisma.account.deleteMany()` invocation in
/Users/m/dev/src/github.com/prisma/interactive-transactions/index.ts:5:58
2 const prisma = new PrismaClient()
3
4 async function unseed() {
→ 5 await prisma.account.deleteMany(
Transaction API error: Transaction already closed: Transaction is no longer valid. Last state: 'Expired'.
at RequestHandler.request (/Users/m/dev/src/github.com/prisma/interactive-transactions/node_modules/@prisma/client/runtime/index.js:36361:15)
at processTicksAndRejections (internal/process/task_queues.js:93:5)
at PrismaClient._transactionWithCallback (/Users/m/dev/src/github.com/prisma/interactive-transactions/node_modules/@prisma/client/runtime/index.js:36932:18) {
code: 'P2028',
clientVersion: '2.30.0-dev.8',
meta: {
error: "Transaction already closed: Transaction is no longer valid. Last state: 'Expired'."
}
}
Two notes:
- The 2nd send takes the full 20s
- An FYI that the stack trace is off. It was pointing to that line even when it was commented out.
I think this could be a bug because if I change the code to transfer serially, it works as expected:
async function main() {
await prisma.$connect()
await unseed()
await seed()
await transfer("[email protected]", "[email protected]", 100)
await transfer("[email protected]", "[email protected]", 100) // Error: alice@prisma.io doesn't have enough to send 100
}Expected behavior
I'd expect this to work as expected, you should be able to initialize multiple transactions concurrently and let the database sort it out.
Prisma information
Environment & setup
- OS: OSX
- Database: Postgres
- Node.js version: v14.16.0
Prisma Version
Environment variables loaded from .env
prisma : 2.30.0-dev.8
@prisma/client : 2.30.0-dev.8
Current platform : darwin
Query Engine (Binary) : query-engine 71d96e8bbd21982078694f00add0f51da2056a8b (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine : migration-engine-cli 71d96e8bbd21982078694f00add0f51da2056a8b (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 71d96e8bbd21982078694f00add0f51da2056a8b (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary : prisma-fmt 71d96e8bbd21982078694f00add0f51da2056a8b (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 71d96e8bbd21982078694f00add0f51da2056a8b
Studio : 0.419.0
Preview Features : interactiveTransactions