Concurrent updates are one of the most common sources of subtle bugs in modern web applications. When two users try to modify the same record at the same time, one of them will silently overwrite the other’s changes unless you implement a proper concurrency strategy. In this practical tutorial, we’ll show you how to implement optimistic locking in Prisma with PostgreSQL, using a version field pattern that works reliably in production Node.js applications.

What is Optimistic Locking?

Optimistic locking (also called Optimistic Concurrency Control or OCC) is a strategy that assumes conflicts between concurrent transactions are rare. Instead of locking a database row while it’s being read or edited, you allow every process to read freely, and you only check for conflicts at the moment of writing.

The idea is simple:

1. Read a record along with its current version number.
2. Perform your business logic in the application layer.
3. When updating, check that the version in the database still matches the one you read. If it does, update and increment the version. If it doesn’t, reject the update because someone else got there first.

Optimistic vs Pessimistic Locking

Before jumping into the implementation, it helps to understand when each strategy makes sense.

Aspect	Optimistic Locking	Pessimistic Locking
Assumption	Conflicts are rare	Conflicts are frequent
Mechanism	Version field or timestamp	SELECT FOR UPDATE row locks
Performance	High throughput, no blocking	Blocks other transactions
Best for	Web apps, REST APIs, editing UIs	Financial transactions, inventory decrement
Prisma support	Native (via updateMany with where clause)	Requires raw SQL

Setting Up Your Prisma Schema

Let’s build a realistic example: an Article model in a collaborative editing platform. Multiple editors may attempt to update the same article, and we want to make sure no one silently overwrites another editor’s changes. Concurrency Control in DBMS: Locking, MVCC & More is a useful companion to this.

Open your schema.prisma file and add a version field:

model Article {
  id        String   @id @default(cuid())
  title     String
  content   String
  authorId  String
  version   Int      @default(1)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

The version field is what makes optimistic locking possible. It starts at 1 and will be incremented every time the record is successfully updated. For the wider picture, see Transactions and batch queries.

Run the migration:

npx prisma migrate dev --name add_version_to_article

Implementing Optimistic Locking with Prisma

The core trick is to use updateMany instead of update. Why? Because updateMany lets you add extra conditions to the where clause (like the version), and it returns the number of rows affected. That gives us everything we need to detect a conflict.

Step 1: Fetch the Record with its Version

const article = await prisma.article.findUnique({
  where: { id: articleId },
});

if (!article) {
  throw new Error('Article not found');
}

// Send article.version back to the client along with the data
return {
  id: article.id,
  title: article.title,
  content: article.content,
  version: article.version,
};

Step 2: Update With a Version Check

When the client submits the edited article, it must send back the version it originally received. We then use it in the where clause:

async function updateArticle(
  articleId: string,
  data: { title: string; content: string },
  expectedVersion: number
) {
  const result = await prisma.article.updateMany({
    where: {
      id: articleId,
      version: expectedVersion,
    },
    data: {
      title: data.title,
      content: data.content,
      version: { increment: 1 },
    },
  });

  if (result.count === 0) {
    throw new ConflictError(
      'The article was modified by another user. Please refresh and try again.'
    );
  }

  return prisma.article.findUnique({ where: { id: articleId } });
}

If result.count is 0, it means no row matched both the id and the expected version. That’s your signal that another process updated the record in the meantime.

Step 3: Handle the Conflict in Your API Layer

In an Express or Fastify route, translate the conflict into a proper HTTP response, typically 409 Conflict:

app.put('/articles/:id', async (req, res) => {
  try {
    const updated = await updateArticle(
      req.params.id,
      { title: req.body.title, content: req.body.content },
      req.body.version
    );
    res.json(updated);
  } catch (err) {
    if (err instanceof ConflictError) {
      const fresh = await prisma.article.findUnique({
        where: { id: req.params.id },
      });
      return res.status(409).json({
        error: 'CONFLICT',
        message: err.message,
        currentVersion: fresh,
      });
    }
    res.status(500).json({ error: 'INTERNAL_ERROR' });
  }
});

Returning the current server state alongside the 409 response is a nice touch: your frontend can display a diff or a merge UI, instead of just telling the user to refresh.

Handling Retries Automatically

For some operations, it’s safe to retry automatically instead of bothering the user. For example, when incrementing a counter or updating a status field where the business logic doesn’t depend on the previous state:

async function incrementViewCount(articleId: string, maxRetries = 3) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    const article = await prisma.article.findUnique({
      where: { id: articleId },
      select: { version: true },
    });

    if (!article) throw new Error('Not found');

    const result = await prisma.article.updateMany({
      where: { id: articleId, version: article.version },
      data: {
        viewCount: { increment: 1 },
        version: { increment: 1 },
      },
    });

    if (result.count === 1) return;
  }
  throw new Error('Could not update after retries');
}

Important: only retry when the operation is idempotent or when re-reading the record and re-computing is safe. Never blindly retry a business operation whose result depends on the previous state you read.

Using Timestamps Instead of Integer Versions

Some teams prefer to use the updatedAt timestamp as the version indicator. It works, but has a caveat: two updates within the same millisecond can produce the same timestamp on some systems. A dedicated integer counter is safer and more explicit.

If you still want to use a timestamp:

const result = await prisma.article.updateMany({
  where: {
    id: articleId,
    updatedAt: expectedUpdatedAt,
  },
  data: {
    title: newTitle,
  },
});

Combining Optimistic Locking with Prisma Transactions

Optimistic locking pairs well with Prisma's $transaction API when you need to update multiple related records atomically:

await prisma.$transaction(async (tx) => {
  const updated = await tx.article.updateMany({
    where: { id: articleId, version: expectedVersion },
    data: { content: newContent, version: { increment: 1 } },
  });

  if (updated.count === 0) {
    throw new ConflictError('Stale data');
  }

  await tx.articleHistory.create({
    data: { articleId, content: newContent, editedBy: userId },
  });
});

If the version check fails, throwing an error inside the transaction rolls back the history insert too, keeping your data consistent.

Common Pitfalls to Avoid

• Forgetting to increment the version in the data block. If you don't increment, every subsequent update will still see the old version and succeed forever, defeating the whole mechanism.
• Using update instead of updateMany. The regular update throws if no row is found, but it also doesn't let you add the version condition cleanly. Stick with updateMany.
• Not returning the new version to the client. After a successful update, always return the freshly incremented version so the client can chain further edits.
• Retrying too aggressively. Unbounded retries can hide real bugs and create cascading load under heavy contention.
• Ignoring the frontend UX. A 409 error without context is frustrating. Show the user what changed and let them decide.

When Should You Use Pessimistic Locking Instead?

Optimistic locking is not always the right answer. If your workload has high contention on a small number of rows (think: last seat on a flight, inventory countdown during a flash sale), the optimistic approach will produce so many conflicts that users experience it as broken. In those cases, use SELECT ... FOR UPDATE via a raw Prisma query:

await prisma.$transaction(async (tx) => {
  const [row] = await tx.$queryRaw`
    SELECT * FROM "Article" WHERE id = ${articleId} FOR UPDATE
  `;
  // ... perform updates safely, no one else can touch this row
});

FAQ

Does Prisma support optimistic locking natively?

Prisma does not have a dedicated @version decorator like some ORMs do, but it fully supports the pattern through updateMany with a version field in the where clause. This is the officially recommended approach.

Can I use optimistic locking with databases other than PostgreSQL?

Yes. The pattern is database-agnostic. It works identically on MySQL, SQLite, SQL Server, and any other engine Prisma supports.

What HTTP status code should I return on a conflict?

Use 409 Conflict. It's the semantically correct code for this scenario and is widely understood by HTTP client libraries.

Does optimistic locking work with Prisma's soft-delete or middleware?

Yes, but be careful. If middleware modifies the where clause (for example, adding deletedAt: null), make sure the version check remains part of the final query. The team at dev.to reached a similar conclusion.

Should I increment the version on every field change, or only on important ones?

Increment on every update. Trying to be selective creates subtle bugs where two updates targeting different fields overwrite each other's metadata or audit trail.

Conclusion

Implementing optimistic locking with Prisma is straightforward once you understand the pattern: add a version field, use updateMany with a version check in the where clause, and handle the zero-row case as a conflict. This scales beautifully for typical web application workloads, avoids the overhead of row locks, and gives your users a much better experience than silent data loss.

At Box Software, we've deployed this pattern across many production Node.js applications, from SaaS dashboards to collaborative editing tools. If you need help architecting a robust concurrency strategy for your PostgreSQL and Prisma stack, our team is here to help.