Optimistic Concurrency Control

[schickling]

Problem statement

The current API doesn't allow to implement application-level Optimistic Concurrency Control, which is a pattern commonly implemented by applications with high concurrency needs, to avoid creating a bottleneck on the database, while obtaining certain guarantees about the integrity of the modified data.

Use cases

Imagine data that is bound to a given user profile, which only the said user can modify, or their admin. There are few chances that the same user would try to update the same data at the same time, which makes the use of pessimistic locking or long-running transaction solutions suboptimal. Implementing OCC, in this case, will be likely more performant.

Context about Optimistic Concurrency Control

OCC assumes that multiple transactions can frequently complete without interfering with each other, hence proposes to not block concurrent transactions and require the application to rollback in case of a detected conflict:

This SO answer gives a great summary

Optimistic Concurrency Control implies reading a record, taking note of a version number (other methods to do this involve dates, timestamps or checksums/hashes), and checking that the version hasn't changed before the record gets written back. When writing the record back, one filters the update on the version to make sure it's atomic. (i.e. hasn't been updated between when checking the version and writing the record to the disk) and update the version in one hit.

If the record is dirty (i.e. has a different version to the user's) the transaction should abort and require the process to be restarted.

Pessimistic Concurrency Control

PCC is assuming that data will often be accessed by multiple processes at the same time and proposes to lock data to sequence how these processes access it.

In practice, database vendors like Postgres or MySQL implement a concept of table- or row-level locking, giving control over what can be read and written (depending on the transaction's isolation level):

• SELECT ... FOR SHARE which leaves the data available to read, but not to update outside the transaction.
• SELECT ... FOR UPDATE which prevents the selected data from being read or updated outside of the transaction.
• the NOWAIT variant to fail if a row is locked
• the SKIP LOCKED variant to skip locked rows
• ...

This approach comes with risk of deadlocks, which can block the application, so should be considered carefully.

Scope

While we should consider an API design allowing us to evolve towards supporting and controlling both policies, the first iteration should focus on supporting Optimistic Concurrency Control.

Pessimistic Concurrency Control requires a deeper analysis of combinations of transaction isolation levels along with the locking strategy for each record, which makes it significantly more complex.

Solution ideas

The API should offer a way to specify, on update and in $transaction calls, whether to check on a certain field used to implement Optimistic Concurrency Control (version, timestamp...).

Proposal 1

A single attribute defining the locking policy, so that people can implement Optimistic Concurrency Control:

lockingPolicy: {
  optimistic: 'fieldName'
} 

This design would possibly allow defining a pessimistic policy in the future:

lockingPolicy: {
  pessimistic: 'ROW' // or 'TABLE', depends on database vendor
}

Applied example

Imagine users detaining a number of points in a competition. And that points can be updated based on how each user behaves in the application.

model User {
  team        Team
  numPoints   Int
  updatedAt   DateTime @updatedAt
}

The exchange of points needs to be done in a consistent fashion across users to ensure a fair game.

Pseudo code:

// Optimistic
const scored = 2
prisma.$transaction([
	prisma.user.update({
		data: { 
		  numPoints: {
		    decrement: scored
		  } 
		}, 
		where: {
		  id: 'loser-user-id'
		},
		lockingPolicy: {
      optimistic: 'updatedAt'
    } 
  ),
	prisma.user.update({
		data: { 
		  numPoints: {
		    increment: scored
		  } 
		}, 
		where: {
		  id: 'winner-user-id'
		},
		lockingPolicy: {
      optimistic: 'updatedAt'
    } 
  )
])

// Pessimistic (used to proof the API design, not meant for implementation)
const scored = 2
prisma.$transaction([
	prisma.user.update({
		data: { 
		  numPoints: {
		    decrement: scored
		  } 
		}, 
		where: {
		  id: 'loser-user-id'
		},
		lockingPolicy: {
      pessimistic: 'ROW'
    } 
  ),
	prisma.user.update({
		data: { 
		  numPoints: {
		    increment: scored
		  } 
		}, 
		where: {
		  id: 'winner-user-id'
		},
		pessimisticLock: {
      on: 'ROW'
    } 
  )
], {
  isolation: 'REPEATABLE READ'
})

Proposition 2

// Optimistic
const scored = 2
const user = prisma.user.findOne({ where: { id: 'loser-user-id' } })
const controlPoints = user.points

try {
	// Throws if `numPoints` has changed between the fetching and the update
	prisma.user.update({
		data: { 
		  numPoints: {
		    decrement: scored
		  } 
		},
		where: {
		  id: 'loser-user-id'
		},
		if: {
	      numPoints: controlPoints
	    }
	  )
} catch (e) {
  // Consider redoing, or error out.
}

[hugbubby]

Anybody working on this?

[vimutti77]

My current approach is to use prisma.$executeRaw

const tableName = 'User'
const userId = 1
const version = 1

prisma.$executeRaw(`
  do $$
  begin
    update "${tableName}" set "version" = "version" + 1 where "id" = '${userId}' and "version" = '${version}';
    if not found then raise exception 'Data version mismatch';
    end if;
  end $$;
`)

[matthewmueller]

As a workaround for now, you can write OCC yourself using an auto-incrementing version. Take a look at our Transaction Guide for more details.

[vimutti77]

As a workaround for now, you can write OCC yourself using an auto-incrementing version. Take a look at our Transaction Guide for more details.

The problem with updateMany workaround are

1. it does not support nested writes
2. it does not raise an error, so if you use it with $transactions it will not rollback

[barakcoh]

@matthewmueller is there a way to do OCC with a nested update/delete?

In the following example, I can do OCC for ParentModel, but not for ChildModel

const result = await prisma.parentModel.update({
  where: {
    id_version: {
      id,
      version: expectedVersion,
    },
  },
  data: {
    ...
    childModel: {
      update: { /* list of fields to update on ChildModel, no place to add an OCC check */}
    },
    version: {
      increment: 1,
    },
  },       
});

[antonkarsten]

I have added a use case for OCC in #10250. @vimutti77 and @barakcoh maybe you can find some inspiration in the example and my solution. I also had a similar problem of doing a nested create in an updateMany.

[vdjurdjevic]

So currently there is no way to use OCC for two updates on different tables, within a transaction? updateMany is not raising an error, so the transaction won't rollback. Can we rollback transactions manually somehow?

[vdjurdjevic]

Is OCC support going to land any time soon?

[vdjurdjevic]

We have implemented the MVP version of the product with Prisma, but we had to introduce application-level logic for locking to ensure data consistency. I just need to know if better OCC support is even considered in near future (like six months, a year) so that we can live with the workaround for a while, or we need to switch to something else sooner the better. Official transaction guide offers OCC method, but that's very limited, almost useless. Please don't get me wrong, I am not trying to be rude or bash Prisma, I actually love it, DX is awesome, far superior to anything else out there. I just need to know what do you think of this issue, in the long run, to be able to make a decision.

[janpio]

(Removed the @ mention here, as I can not give you an answer. Please open a discussion which is meant for such questions. Thanks.)

[vdjurdjevic]

"Please open a discussion which is meant for such questions" - What do you mean? Open a new issue? I think that this is the right discussion, there is a problem, some ideas and proposals, few people confirmed that problem exists and it really important. The only problem is that we don't get any answers :)

[MaikuMori]

He meant https://github.com/prisma/prisma/discussions.

But I do agree that this should be the appropriate place. Considering how long this issue has been open and the non-answers, I wouldn't expect anything soon. Maybe if you tried to contribute a PR.

[janpio]

This issue is a feature request. We usually share updates here when we are working on implementing said feature request. We never give ETAs on feature before we implemented them - that never ends well.

Mentioning random team members that are active on GitHub is not the way to get any answer (and only requires those then to manually edit the message, as I did above). If you are unhappy with our official transaction guide, how it is limited and "almost useless", open a discussion and ask how it is supposed to be used.

And if you know that this will not lead to success, share your use case here with details on why that is the case and why that confirms the need for implementation of this feature request.

[vdjurdjevic]

We never give ETAs on feature before we implemented them - that never ends well.

I absolutely agree with this, I did not ask for ETA, just a long-run possibility for something like this to happen and if something like this is considered at all.

Mentioning random team members that are active on GitHub is not the way to get any answer

That's why I did not mention random a team member but the head of engineering :D

If you are unhappy with our official transaction guide, how it is limited and "almost useless", open a discussion and ask how it is supposed to be used.

I am not unhappy with the guide, just saying that the workaround you gave us can cover only the simplest use cases. My use case is limited by the same issue @vimutti77 mentioned and @barakcoh also provided an example where we hit this limitation. @antonkarsten also confirmed this.

With all that said, I taught that this issue is the perfect place to ask something like this. Again, @schickling explained everything very clearly, gave two examples of how improved API could look like, few other people agreed, confirmed they experience the same issue and explained how the proposed official guide is limited.

I just wanted to know if the Prisma team agrees with this, and will it be even considered in a reasonable amount of time (like a year). Did not ask for ETA :) Sorry if I offended you in any way.

[janpio]

No harm done (except the @ mention which I removed - I really don't like that when I was not previously involved in an issue. Notifications are busy enough, hope you understand.)

We do agree that OCC could be useful, and are tracking this as a feature request. But that is all we really can say about this, except in this case we also have a guide that documents a partial workaround with current functionality. (The opposite to us treating this as valid, would be that we do not find it reasonable and close the issue with a message about that instead)

[Weakky]

Hey folks,

A solution was implemented by prisma/prisma-engines#3281. It will be available in the next release under a new preview flag called extendedWhereUnique.

If you have any feedback you'd like to give about this feature, please post it here #15837 (comment)

Thanks for your patience, closing now 🙏.