prisma: interactiveTransactions: 2 concurrent writes to the same row will cause it to hang until expiring

Engine deadlocks have been fixed via https://github.com/prisma/prisma-engines/pull/2619 and https://github.com/prisma/prisma/pull/10797

@matthewmueller Any chance it will be fixed in v3.5.0? For now because of that, the interactive transactions are unusable in any http/graphql scenario when the requests can come at the same time 😕

Unfortunately, I’ve updated to 3.6.0 in prod and am seeing the same failure case. Concurrent transactions stall, consume 100% of CPU, and eventually time-out.

Hey @millsp, is this still slated for milestone 3.1.0? Actively blocking me at the moment.

I just tested it with Postgres via pg. First I used a single connection:

import { Client } from "pg"
import dotenv from "dotenv"
dotenv.config()

const client = new Client({
  connectionString: process.env.DATABASE_URL,
})

async function unseed() {
  await client.query(`delete from "Account" where email in($1, $2)`, [
    "alice@prisma.io",
    "bob@prisma.io",
  ])
}

async function seed() {
  await client.query(`insert into "Account" (email, balance) values ($1, $2)`, [
    "alice@prisma.io",
    100,
  ])
  await client.query(`insert into "Account" (email, balance) values ($1, $2)`, [
    "bob@prisma.io",
    100,
  ])
}

async function transfer(nth: number, from: string, to: string, amount: number) {
  await client.query("begin")
  console.time("send " + nth)
  const sender = await client.query(
    `update "Account" set balance = balance - $1 where email = $2 returning *`,
    [amount, from]
  )
  console.timeEnd("send " + nth)
  console.time("throw " + nth)
  if (sender.rows[0].balance < 0) {
    await client.query("rollback")
    console.timeEnd("throw " + nth)
    return
  }
  console.timeEnd("throw " + nth)
  console.time("recieve " + nth)
  const recipient = await client.query(
    `update "Account" set balance = balance + $1 where email = $2 returning *`,
    [amount, to]
  )
  console.timeEnd("recieve " + nth)
  await client.query("commit")
  return recipient.rows[0]
}

async function main() {
  await client.connect()
  await unseed()
  await seed()
  console.time("transfer")
  await Promise.all([
    transfer(1, "alice@prisma.io", "bob@prisma.io", 100),
    transfer(2, "alice@prisma.io", "bob@prisma.io", 100),
  ])
  console.timeEnd("transfer")
}

main()
  .catch(console.error)
  .finally(() => client.end())

Running ts-node postgres.ts,

send 1: 1.634ms
throw 1: 0.009ms
send 2: 2.25ms
recieve 1: 0.763ms
throw 2: 0.552ms
transfer: 4.121ms

The result:

This sort of makes sense because with 1 connection, 2 concurrent requests, it’s not clear in what order the queries happen.

Next I setup the pool and used a separate connection per transfer.

import { Pool, PoolClient } from "pg"
import dotenv from "dotenv"
dotenv.config()

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
})

async function unseed(client: PoolClient) {
  await client.query(`delete from "Account" where email in($1, $2)`, [
    "alice@prisma.io",
    "bob@prisma.io",
  ])
}

async function seed(client: PoolClient) {
  await client.query(`insert into "Account" (email, balance) values ($1, $2)`, [
    "alice@prisma.io",
    100,
  ])
  await client.query(`insert into "Account" (email, balance) values ($1, $2)`, [
    "bob@prisma.io",
    100,
  ])
}

async function transfer(
  pool: Pool,
  nth: number,
  from: string,
  to: string,
  amount: number
) {
  const client = await pool.connect()
  await client.query("begin")
  console.time("send " + nth)
  const sender = await client.query(
    `update "Account" set balance = balance - $1 where email = $2 returning *`,
    [amount, from]
  )
  console.timeEnd("send " + nth)
  console.time("throw " + nth)
  if (sender.rows[0].balance < 0) {
    await client.query("rollback")
    client.release()
    console.timeEnd("throw " + nth)
    return
  }
  console.timeEnd("throw " + nth)
  console.time("recieve " + nth)
  const recipient = await client.query(
    `update "Account" set balance = balance + $1 where email = $2 returning *`,
    [amount, to]
  )
  console.timeEnd("recieve " + nth)
  await client.query("commit")
  client.release()
  return recipient.rows[0]
}

async function main() {
  const client = await pool.connect()
  await unseed(client)
  await seed(client)
  client.release()
  console.time("transfer")
  await Promise.all([
    transfer(pool, 1, "alice@prisma.io", "bob@prisma.io", 100),
    transfer(pool, 2, "alice@prisma.io", "bob@prisma.io", 100),
  ])
  console.timeEnd("transfer")
}

main()
  .catch(console.error)
  .finally(() => pool.end())

This works as expected:

From this, it seems like Prisma should use a separate connection per transaction. I’m still not sure why it hangs since even on the same connection, pg still processes the queries.

I did experience this too. What I also found is that when using the interactiveTransactions preview feature, regular transaction will also suffer from the same probleme, i.e a transaction using an array of Prisma Promise. When disabling the feature, regular transaction work as expected.

I have seen the same thing in my application. I created a “Poor Man’s” SEQUENCE for MySQL, and it uses the transaction array listed in s-montigny-desautels comment above. The application is running in AWS with a Health Check that hits it about every 10 seconds. I put many changes in, including upgrading all the packages, and Prisma to 3.7. Suddenly my application was crashing randomly (varying from <30 minutes to 4+ hours), and forcing an AWS restart. This was too difficult to narrow down for debugging purposes, as when the CPU went to 100%, it was only 1 - 2 minutes before AWS restarted the application, and it wasn’t happening locally.

I was at a complete loss to determine what was going on, due to the number of changes, but it finally occurred to me it was the Prisma change, as two applications were crashing, neither of which were doing anything at the time, except the Health Checks. After investigating the Health Check, I realized it was using a prisma.$transaction([]), and had a lead, since I had also turned on the previewFeatures = [“interactiveTransactions”], and even though it was not being used anywhere, it was a likely cause.

I commented out the interactiveTransaction in the schema.prisma, and redeployed. The attached graph shows the Random crashes before the ~ 9:30am deployment, and none since.

I could provide the code that caused it, but I don’t think it is needed, as it is pretty obvious that the Health Check Transaction was somehow getting called again, before completing the previous call. The only reason this would likely happen, is that the prisma.$transaction wasn’t finishing and/or cleaning up anything it was doing before returning from the Promise, so the next call to it is not starting with a “clean slate”.

NOTE: This is some how tied to the previewFeature interactiveTransactions.

Update:

After looking at the graphs highly expanded (image attached), another thought occurred to me. These Memory Usages were from 2 separate Tasks running in separate Containers. I am now thinking that whatever Prisma is using to create (identify) the Transaction is stored in the Database (possibly a timestamp ?), because the Database requests are coming from 2 different systems at the same time, and both systems CPU usage went to 100%, and then both got restarted. I am guessing that the 2nd transaction in is somehow corrupting the first one because it ends up with the same identifier and then whatever they use to check completion goes into an infinite loop, causing both to go to 100% CPU utilization.

I think I might have a repro - which differs from the OP since that issue has been fixed. Instead, I just performed a load test and triggering many transactions at once. That sometimes works and other times it leads to the screenshot below. binary engine seems more susceptible to this, even though it happens on library too (without proof of where is stalls on that one though).

@janpio I’ll see if I can get some time from my client to set up a reproduction repo.

We’re working on it! I agree it’s really bad right now. I can’t promise 3.5.0, but it’s WIP.

Sorry, I had a dyslectic moment with the Prisma Version numbers in the first Graph above. I have corrected it now…

I think I might have a repro - which differs from the OP since that issue has been fixed. Instead, I just performed a load test and triggering many transactions at once. That sometimes works and other times it leads to the screenshot below. binary engine seems more susceptible to this, even though it happens on library too (without proof of where is stalls on that one though).

I did experience this too. What I also found is that when using the interactiveTransactions preview feature, regular transaction will also suffer from the same probleme, i.e a transaction using an array of Prisma Promise. When disabling the feature, regular transaction work as expected.

More investigation here: https://github.com/matthewmueller/interactive-transactions