SQL Server Express INSERT unique incremented number from SELECT on the same table by several users without creating duplicates

by

Summary

This incident revolves around a classic concurrency pitfall: generating unique incrementing serial numbers in SQL Server without proper transactional guarantees. The system worked fine for a single user, but under multi‑user load it risked producing duplicate serial numbers because the logic relied on SELECT MAX(...) + 1 patterns without isolation or locking.

Root Cause

The root cause was the use of a non-atomic SELECT‑then‑INSERT pattern:

  • Multiple clients could simultaneously read the same “last serial number”
  • Each client computed the same “next” value
  • Each client attempted to insert that value
  • SQL Server did not automatically serialize these operations because no explicit locking or transaction isolation was used

In short: the operation was not atomic, and SQL Server does not magically guarantee uniqueness unless you explicitly design for it.

Why This Happens in Real Systems

Real systems experience this because:

  • SELECT statements do not lock rows by default in a way that prevents concurrent reads
  • INSERT operations do not block other sessions from reading the same data
  • Network latency creates windows where two clients run the same logic at the same time
  • SQL Server Express has limited concurrency optimizations compared to full editions
  • Developers often assume SQL Server “just handles it,” but uniqueness must be enforced explicitly

Real-World Impact

When this pattern fails under concurrency, systems may experience:

  • Duplicate serial numbers for the same product type
  • Integrity violations if constraints exist
  • Downstream failures in manufacturing, labeling, or traceability systems
  • Hard-to-reproduce bugs because the race condition only appears under load
  • Data cleanup requirements when duplicates slip into production

Example or Code (if necessary and relevant)

Below is a correct, concurrency-safe pattern using SERIALIZABLE isolation and an atomic update:

BEGIN TRANSACTION;

DECLARE @NextSerial INT;

SELECT @NextSerial = ISNULL(MAX(UniquePartSerialNumber), 0) + 1
FROM FactorySerial WITH (UPDLOCK, HOLDLOCK)
WHERE ProductCode = @ProductCode;

INSERT INTO FactorySerial (PrintedDefault, DummyBCR, ProductCode, DummyFactorySN, UniquePartSerialNumber)
VALUES (@PrintedDefault, @DummyBCR, @ProductCode, @DummyFactorySN, @NextSerial);

SELECT @NextSerial AS NewSerial;

COMMIT TRANSACTION;

This works because:

  • UPDLOCK + HOLDLOCK forces SQL Server to serialize access
  • SERIALIZABLE isolation ensures no phantom rows appear
  • The entire operation becomes atomic

How Senior Engineers Fix It

Experienced engineers avoid the SELECT‑then‑INSERT race condition entirely by using one of these patterns:

  • Use a dedicated sequence object (CREATE SEQUENCE) per product type
  • Use a table with one row per product type and update it using UPDATE ... OUTPUT
  • Use SERIALIZABLE transactions with locking hints to force atomicity
  • Enforce uniqueness with constraints so duplicates cannot be inserted
  • Move the logic into a stored procedure to guarantee consistent behavior

The key principle: never compute the next number outside a transaction that guarantees exclusivity.

Why Juniors Miss It

Junior developers often miss this issue because:

  • Their tests run with one user, so concurrency bugs never appear
  • They assume SQL Server “automatically prevents duplicates”
  • They misunderstand how transaction isolation levels work
  • They rely on application-side logic instead of database-enforced atomicity
  • They are unaware that SELECT MAX(…) + 1 is unsafe in multi-user environments

The result is a design that works perfectly—until it doesn’t.

Leave a Comment