Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 37 ยท Transactions

Locking and Isolation Levels

What isolation levels prevent, how the lost update happens, and how to fix it with FOR UPDATE locks or optimistic version columns in TypeORM and Sequelize.

A transaction protects you from your own half-finished work, but two transactions running at the same time can still step on each other. Two clerks open the same stock record, both read "10 left", both sell one and both write "9". One sale has vanished. This is the lost update, the most common concurrency bug in ORM-based apps, and it happens even when every write sits inside a transaction.

This lesson shows what isolation levels promise and the two practical cures: pessimistic locking (block others while you work) and optimistic locking (let everyone work and detect conflicts at write time). The runnable blocks use node:sqlite; TypeORM and Sequelize code is read-only.

What isolation protects against

Problem Meaning
Dirty read You see another transaction's uncommitted change
Non-repeatable read You read a row twice and get different values
Phantom read You run a query twice and new rows appear
Lost update / write skew Two read-then-write sequences overwrite each other

The SQL standard defines four levels, from weakest to strongest: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Engines differ: PostgreSQL's default is READ COMMITTED and it treats READ UNCOMMITTED as READ COMMITTED; MySQL InnoDB defaults to REPEATABLE READ; SQLite is effectively serializable. Check your database's documentation, because "the same level" does not behave identically everywhere.

TypeScript
// TypeORM
await dataSource.transaction('REPEATABLE READ', async (manager) => { /* ... */ });

// Sequelize
await sequelize.transaction(
  { isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE },
  async (t) => { /* ... */ },
);

Higher levels are safer but cause more waiting or more serialization failures that you must retry (see Deadlocks and Retries).

Seeing isolation with two connections

SQLite lets us open two connections to one file, enough to show that uncommitted work is invisible to others.

Node.js Two connections, one uncommitted change

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

The lost update, step by step

Here two "requests" interleave. Each reads, computes in application code, then writes the computed value.

Node.js A lost update

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Two items were sold but stock only dropped by one.

Cure 0: let the database do the arithmetic

If the change is a simple increment, skip the read: update stock set qty = qty - 1. The database applies it atomically against the current value. In TypeORM that is repository.decrement({ sku: 'pen' }, 'qty', 1); in Sequelize Stock.decrement('qty', { by: 1, where: { sku: 'pen' } }).

Cure 1: pessimistic locking

When you must read, think and then write, lock the row at read time. Other transactions that try to lock the same row wait until you commit.

TypeScript
// TypeORM: SELECT ... FOR UPDATE
await dataSource.transaction(async (m) => {
  const item = await m.findOne(Stock, {
    where: { sku: 'pen' },
    lock: { mode: 'pessimistic_write' },
  });
  item!.qty -= 1;
  await m.save(item!);
});
JavaScript
// Sequelize
await sequelize.transaction(async (t) => {
  const item = await Stock.findOne({ where: { sku: 'pen' }, lock: t.LOCK.UPDATE, transaction: t });
  await item.update({ qty: item.qty - 1 }, { transaction: t });
});

Locks only work inside a transaction. SQLite has no row locks (it locks the whole database), so this part is read-only. Use skipLocked or nowait options when you build job queues so workers do not wait on each other.

Cure 2: optimistic locking

Add a version number. Every update says "change this row only if it still has the version I read", and bumps the version. If someone else got there first, zero rows match and you know there was a conflict.

Node.js Optimistic locking with a version column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Both libraries automate this:

TypeScript
// TypeORM
@VersionColumn() version!: number;
// save() on a stale entity throws; or use lock: { mode: 'optimistic', version }
JavaScript
// Sequelize
const Stock = sequelize.define('Stock', { qty: DataTypes.INTEGER }, { version: true });
// updating a stale instance throws OptimisticLockError

Which to choose

Pessimistic Optimistic
Conflicts are frequent rare
Cost waiting, possible deadlocks a retry on conflict
Held across user think time no, locks are short yes, perfect for edit forms
Typical use stock, seat booking, job queues editing a profile or document

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Two requests read qty 10, each writes 9. What is this bug called?

    The second write overwrites the first because both computed from the same stale read.
  2. 2. Which approach needs a version column?

    The update is conditional on the version still matching what was read.
  3. 3. What does lock mode pessimistic_write usually translate to in SQL?

    The selected rows are locked until the transaction ends.
  4. 4. For decrementing a counter, what is the simplest correct fix?

    An in-database increment never reads a stale value.

Summary

  • Transactions do not stop lost updates by themselves.
  • Isolation levels trade safety against waiting and retries, and engines differ.
  • Prefer atomic in-database arithmetic; otherwise lock rows (FOR UPDATE) or use a version column.
  • Choose pessimistic locks for hot rows and optimistic ones for rare conflicts.

Next: Deadlocks and Retries