Transactions don't seem to respect parallel requests

[EvHaus]

I'm using PlanetScale with Prisma. I have a model like this:

model Thing {
    id                 String       @id @default(cuid())
    createdAt          DateTime     @default(now())
    thingId            String
    organizationId     String

    @@unique([organizationId, thingId])
}

The thingId value is a string that looks like this: RP-1 or XX-999, etc... It's a value that must be unique within an organizationId scope and should be incremented by 1 for each new insert. Since it's a string field, I cannot use autoincrement() on it. I was hoping to use transactions for this but it's not working for me. My transaction looks like this:

const createThing = async () => {
  const thing = await prisma.$transaction(async (tx) => {
      // Get the last thing for a given organization
      const lastThing = await tx.thing.findFirst({
	  orderBy: { createdAt: 'desc' },
	  select: { thingId: true },
	  where: { organizationId },
      });
  
      // Generate a unique `thingId`
      let thingId = null;
      if (!lastThing) {
	  thingId = `XX-1`;
      } else {
	  const digit = parseInt(lastThing.thingId.split('-')[1], 10);
	  thingId = `XX-${digit + 1}`;
      }
      
      return await tx.thing.create({
	  data: {
		  organizationId,
		  thingId,
	  },
     });
   };
});

This works just fine if I call createThing() one at a time, but if I try to simulate many different things being created concurrently via:

await Promise.all([
    createThing(),
    createThing(),
    createThing(),
])

I start to get these errors:

Unique constraint failed on the (not available)
 code: "P2002"

To me this seems like a limitation/bug in PlanetScale in that it doesn't lock the table during ongoing transactions. I couldn't find anything in the PlanetScale docs around this so I'm wondering if perhaps this is an undocumented limitation or maybe I'm doing something stupid.

[orware]

Hello @EvHaus,

Overall, I would say that something like the approach you shared should probably work just fine, although we do tend to have users run into difficulties with Prisma transactions if they are accidentally kept open for an extended period of time, which can result in users encountering our 20 second transaction timeout limit, but that's different than the error you shared.

I don't know if it's a typo or something else, but out of hte information you provide the unique index seemed to stand out as potentially having a mistake or referencing a field that doesn't exist in the table definition you provided:

@@unique([organizationId, reportId])

I would be curious if each instance of createThing() is causing the same next value (e.g. XX-2) to be used in your concurrent example?

Based on a quick read through of the Prisma docs here:
https://www.prisma.io/docs/guides/performance-and-optimization/prisma-client-transactions-guide#nested-writes

It feels like using $transaction is intended for independent writes, but I feel in this case these are dependent writes since technically they should occur one after the other even if they are happening concurrently.

This one feels like it'll require a bit more experimentation on the Prisma side to achieve the expected behavior, but unless you are also testing against a vanilla MySQL 8 instance and observing behavior that's different from the PlanetScale database, I would expect them to mostly be behaving the same in this situation.

[EvHaus]

Thanks @orware. I had a typo in my example. The index should have been @@unique([organizationId, thingId]).

I just tried my code with MySQL and it doesn't work there either. So looks like my expectations around how transactions should be working are incorrect.