Explaining Optimistic Locking with Coupon Redemption

I tried explaining optimistic locking through coupon redemption. When you apply it, the core idea is this: “Read the version together with the coupon, and when redeeming it, update the row only if the version you read is still current.”

Why does this matter? The answer is at the end.

Example table:

CREATE TABLE coupons (
  id BIGINT PRIMARY KEY,
  code VARCHAR(50) UNIQUE NOT NULL,
  used BOOLEAN NOT NULL DEFAULT FALSE,
  used_by BIGINT NULL,
  used_at TIMESTAMP NULL,
  version BIGINT NOT NULL DEFAULT 0
);

Coupon redemption flow:

-- 1. Fetch the coupon
SELECT id, code, used, version
FROM coupons
WHERE code = 'WELCOME10000';

Query result:

id: 1
code: WELCOME10000
used: false
version: 3

When the user tries to redeem the coupon, include the version = 3 you previously read in the update condition.

UPDATE coupons
SET
  used = true,
  used_by = 123,
  used_at = NOW(),
  version = version + 1
WHERE
  id = 1
  AND used = false
  AND version = 3;

If the number of updated rows is 1, the redemption succeeded.

affected rows = 1
Coupon redeemed successfully

If two people try to use the same coupon at the same time, it plays out like this.

User A fetches: used=false, version=3
User B fetches: used=false, version=3

A updates first:

UPDATE coupons
SET used = true, used_by = 123, used_at = NOW(), version = version + 1
WHERE id = 1 AND used = false AND version = 3;

Result:

affected rows = 1
Success

The coupon state is now:

used = true
version = 4

B updates later:

UPDATE coupons
SET used = true, used_by = 456, used_at = NOW(), version = version + 1
WHERE id = 1 AND used = false AND version = 3;

Result:

affected rows = 0
Failure

It fails because the coupon in the database is already version = 4 and used = true, so it no longer matches the version = 3 condition that B knew about.

In application code, it would look roughly like this.

async function useCoupon(code: string, userId: number) {
  const coupon = await db.coupons.findUnique({
    where: { code },
    select: {
      id: true,
      used: true,
      version: true,
    },
  });

  if (!coupon) {
    throw new Error("Coupon does not exist.");
  }

  if (coupon.used) {
    throw new Error("Coupon has already been used.");
  }

  const result = await db.coupons.updateMany({
    where: {
      id: coupon.id,
      used: false,
      version: coupon.version,
    },
    data: {
      used: true,
      usedBy: userId,
      usedAt: new Date(),
      version: {
        increment: 1,
      },
    },
  });

  if (result.count === 0) {
    throw new Error("Another user redeemed this coupon first.");
  }

  return {
    success: true,
  };
}

The important part is updateMany. With a typical update, handling condition mismatches can be ambiguous, so using count === 0 to detect a conflict is much clearer.

Doing this without optimistic locking is dangerous.

UPDATE coupons
SET used = true, used_by = 123
WHERE id = 1;

In this case, if multiple requests arrive at the same time, the last request can overwrite the earlier one.

For coupon redemption, you usually include these conditions together.

WHERE id = ?
  AND used = false
  AND version = ?

For quantity-based coupons, the example changes a little.

CREATE TABLE coupon_campaigns (
  id BIGINT PRIMARY KEY,
  code VARCHAR(50) UNIQUE NOT NULL,
  total_quantity INT NOT NULL,
  used_quantity INT NOT NULL DEFAULT 0,
  version BIGINT NOT NULL DEFAULT 0
);

Redemption handling:

UPDATE coupon_campaigns
SET
  used_quantity = used_quantity + 1,
  version = version + 1
WHERE
  id = 1
  AND used_quantity < total_quantity
  AND version = 7;

Here too, affected rows = 1 means success. If it is 0, you either refetch and retry, or handle it as “all coupons have been exhausted.”

To summarize:

Single-use coupon:
WHERE used = false AND version = previous_version

Quantity-limited coupon:
WHERE used_quantity < total_quantity AND version = previous_version

Success check:
If the UPDATE row count is 1, it succeeded. If it is 0, there was a conflict or the coupon is exhausted.

In production, you would usually also keep a coupon usage history table.

CREATE TABLE coupon_usages (
  id BIGINT PRIMARY KEY,
  coupon_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  used_at TIMESTAMP NOT NULL,
  UNIQUE (coupon_id),
  UNIQUE (coupon_id, user_id)
);

To make it safer, wrap the coupon status update and the usage record insert in a single transaction.

BEGIN;

UPDATE coupons
SET
  used = true,
  used_by = 123,
  used_at = NOW(),
  version = version + 1
WHERE
  id = 1
  AND used = false
  AND version = 3;

-- Run only when affected rows is 1
INSERT INTO coupon_usages (id, coupon_id, user_id, used_at)
VALUES (1001, 1, 123, NOW());

COMMIT;

Why You Should Use Optimistic Locking

In distributed system applications, features become more complex and multiple tables are referenced through many different paths. As that happens, it becomes difficult to keep lock acquisition order consistent across every transaction. In that situation, pessimistic locks such as SELECT FOR UPDATE can increase the chance of deadlocks, and as traffic grows, the cost of lock waiting and retries can also grow.

So for workflows where conflicts are not frequent and failed operations can be retried, it is usually appropriate to consider optimistic locking before pessimistic locking.

Optimistic locking includes the version or state from the time of the read in a conditional UPDATE, detects conflicts at the actual point of modification, and then retries or rejects only the failed request. This avoids holding locks for a long time and can reduce the chance of deadlocks.

Traffic growth itself is not the direct cause of deadlocks, but as the number of concurrent transactions increases, lock contention and the probability of deadlocks also increase. Therefore, in domains where lock acquisition order cannot be fully controlled, use optimistic locking and conditional UPDATE statements rather than designing primarily around pessimistic locks.

Depending on the domain, if maintaining consistency through locks is more important, you should also consider combining approaches such as unique constraints or queue-based serialization.