Lesson 24 / 35

Concurrency: Optimistic and Pessimistic Locking

Prevent lost updates with version columns or row locks, and choose between the two approaches for a given workload.

By · 15 years in web development

Detect conflicts or prevent them

Transactions alone do not stop every concurrency bug. If two admins open the same product, edit the price and save, the second save silently overwrites the first: a lost update. There are two cures. Optimistic locking assumes conflicts are rare: each row has a version number; an update succeeds only if the version is still the one you read, and it increments it. If someone else saved first, zero rows match and you report a conflict. Sequelize supports this with the model option version: true, which makes save() throw an OptimisticLockError. TypeORM has @VersionColumn(), which increments on save but does not by itself reject a stale save, plus an optimistic lock option on reads that throws when the version no longer matches. In Prisma you write the check yourself with updateMany. Pessimistic locking assumes conflicts are likely and locks the row with SELECT ... FOR UPDATE inside a transaction, making others wait. It is simpler to reason about but reduces throughput and can deadlock if locks are taken in different orders.

Optimistic update in Prisma

The where includes the version you read. If another request already bumped it, count is 0 and you know there was a conflict.

const product = await prisma.product.findUniqueOrThrow({ where: { id } });

const { count } = await prisma.product.updateMany({
  where: { id, version: product.version },
  data: { price: newPrice, version: { increment: 1 } },
});

if (count === 0) {
  throw new Error('Product was changed by someone else. Reload and try again.');
}

Pessimistic row lock in TypeORM and Sequelize

Both must run inside a transaction; the lock is released at commit or rollback.

// TypeORM: SELECT ... FOR UPDATE
await dataSource.transaction(async (manager) => {
  const seat = await manager.findOne(Seat, { where: { id }, lock: { mode: 'pessimistic_write' } });
  // ...check and update seat with manager.save(seat)...
});

// Sequelize
await sequelize.transaction(async (t) => {
  const seat = await Seat.findByPk(id, { transaction: t, lock: t.LOCK.UPDATE });
  // ...check and update seat with seat.save({ transaction: t })...
});

Shared notes vs a locked cupboard

Optimistic locking is a shared Google Doc that warns you 'someone else edited this, reload'. Pessimistic locking is the lab cupboard key: only the person holding it can change what is inside, and everyone else waits at the door.

Quick check: A ticket-booking app expects heavy contention on the last few seats of a popular show. Which approach is usually more suitable for those seat rows?

  • No locking, relying on default isolation alone
  • Optimistic locking, since conflicts are expected to be rare
  • Pessimistic locking with SELECT ... FOR UPDATE inside a short transaction
  • Disabling transactions to improve speed
Answer

Pessimistic locking with SELECT ... FOR UPDATE inside a short transaction — When conflicts are frequent, optimistic locking produces many failed retries. Locking the row briefly makes contenders wait in turn, which is clearer for hot rows.