Concurrent updates that don't interfere with each other

[nikgraf]

Problem

I need to get the first entry of a list of oneTimeKeys for a specific user and update it to be connected to the user that “claims” it.

What I did until now:

const oneTimeKeyResult = await prisma.oneTimeKey.findMany({ // could be findFirst
  where: {
    user: { id: userId },
    claimedByUserId: { equals: null },
  },
  take: 1,
});
const oneTimeKey = await prisma.oneTimeKey.update({
  where: { id: oneTimeKeyResult[0].id },
  data: {
    claimedByUser: { connect: { id: currentUser.id } },
  },
});

The problem here is that if two or more users try to claim the same oneTimeKey at the same time they can can and it actually happens sometimes in production.

Suggested solution

Running the find and update in a transaction would be nice, but this is not supported currently.

Alternatives

Since I'm running on Postgres (if I understand correctly) I could do a executeRaw with an update using FOR UPDATE SKIP LOCKED

https://stackoverflow.com/a/56441907/837709
https://dba.stackexchange.com/questions/69471/postgres-update-limit-1

[mavilein]

Hey @nikgraf ,

thanks for reaching out. Can you also provide your prisma schema with the 2 models? Wanna be sure I have the right relations in mind.

[muxahuk]

as far as i understand this scenario can happen only when user is one to many relation. So we have many users that can claim one key.

const oneTimeKey = await prisma.oneTimeKey.update({
  where: {
    id: oneTimeKeyResult[0].id,
    claimedByUserId: { equals: null },
  },
  data: {
    claimedByUser: { connect: { id: currentUser.id } },
  },
});

this should fix ur issue.. adding claimedByUserId: { equals: null }, to the update - should fail to update the record if it has been already updated (i.e. won't be able to find one).

Not sure thou if generated client allows passing that property to the where condition..

[nikgraf]

sure @mavilein

model OneTimeKey {
  id                String   @id @default(uuid())
  claimedByUser     User?  @relation(name: "ClaimedUser", fields: [claimedByUserId], references: [id])
  claimedByUserId   String?
}

model User {
  id                 String               @id @default(uuid())
  claimedOneTimeKeys OneTimeKey[]         @relation(name: "ClaimedUser")
}

(I have to add I changed some names and simplified the Models to focus on the issue)

@muxahuk I haven't tried, maybe it's just wrongly typed, but there where only accepts an id property.

[nikgraf]

Then I though updateMany would help, but it doesn't allow to update the relationship:

const oneTimeKey = await prisma.oneTimeKey.updateMany({
  where: {
    id: oneTimeKeyResult[0].id,
    claimedByUserId: { equals: null },
  },
  data: {
    claimedByUser: { connect: { id: currentUser.id } }, // THIS IS NOT ALLOWED
  },
});

[muxahuk]

yep, can confirm that there's no way to do what u want using generated prisma client.

but with some tricks i was able to achieve a workaround:

1. we need to add new column to table that would be not null and by default = 0
2. we need to add constraint on that column so that it is less then or equal to 1
3. when updating key we also increment that column by 1 (using atomic update)

I was testing on sqlite and here's what i added to the end of table creation (usually we can use alter table to add check/constraint)

"connectedTimes" INTEGER NOT NULL DEFAULT 0,
 CHECK("connectedTimes" <= 1) // we should also do "AND "connectedTimes" >= 0" but for test enaugh

then i modified update call to:

await prisma.oneTimeKey.update({
		where: {
			id: keyId,
		},
		data: {
			connectedTimes: { // here we use atomic update
				increment: 1, 
			},
			claimedByUser: {
				connect: {
					id: userId,
				},
			},
		},
	});

so, basically what we are trying to achieve is so that whenever we try to connect a user we also increment "connectedTimes", if we try to do that 2nd time - it will fail bcs of check/constraint we added before resulting in error that we can catch and retry with different key or reply to user that error...

only downside that there is - when we disconnect user or delete - we should reset "connectedTimes" to 0 so that new user could be connected. That should be achievable via trigger (on db level) or somewhere in your application logic

PS: this seems over engineered. Not DB specialist, but if we could add a check/constraint on "claimedByUser" column so that once it is set it can not be changed to something else then what it is now or null.. i guess can be achieved with trigger..

[nikolasburk]

Then I though updateMany would help, but it doesn't allow to update the relationship:

const oneTimeKey = await prisma.oneTimeKey.updateMany({
  where: {
    id: oneTimeKeyResult[0].id,
    claimedByUserId: { equals: null },
  },
  data: {
    claimedByUser: { connect: { id: currentUser.id } }, // THIS IS NOT ALLOWED
  },
});

Ah sorry, this is indeed not possible. However, with the new preview feature uncheckedScalarInput which allows to set foreign keys directly this would work:

await prisma.post.updateMany({
  where: {
    id: oneTimeKeyResult[0].id,
    claimedByUserId: { equals: null }, 
  },
  data: {
    claimedByUserId: currentUser.id // this is possible since v2.11.0
  }
})

To use this feature, you need to set the uncheckedScalarInput on the Prisma Client generator:

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["uncheckedScalarInputs"]
}

Don't forget to run npx prisma generate afterwards (sometimes I also need to rm -rf node_modules for the latest version to show up).

[nikgraf]

@nikolasburk ahh nice, makes a lot of sense.

Just for the record in the end I went for a raw query since the queries above would have required me to write my own retry logic. If I understand correctly using FOR UPDATE SKIP LOCKED for Postgres it's not necessary.

This post explains how queues with Postgres are possible using FOR UPDATE SKIP LOCKED https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/

[matthewmueller]

Glad to hear you found a solution! I think you could also use the OCC pattern for cases like this: https://www.prisma.io/docs/guides/prisma-guides/prisma-client-transactions-guide#optimistic-concurrency-control