Two requests, one person, two users: fixing a race condition with PostgreSQL advisory locks

Most bugs show up in the logs. This one showed up as a person who existed twice.

I work on a central user database: the systems that know something about a customer send it to one API, and the API decides whether this is a new person or someone we already know. If we know them, the new information is merged into the existing record. If not, a new user is created.

That decision is a classic upsert, and it had a gap.

The problem: check-then-insert#

The logic was straightforward:

1. Look for an existing user that matches the incoming data.
2. If there is one, merge and update it.
3. If there isn't, insert a new user.

Each step is correct on its own. The problem is the time between step 1 and step 3.

When a customer signs up, several systems often report them at nearly the same moment. Two requests for the same email arrive a few milliseconds apart. Both look for the user, both find nothing because neither has written anything yet, and both insert. Now the same person exists twice, and every system downstream has to guess which record is the real one.

It is rare per request, but with enough traffic, rare still means it happens.

Why the obvious fixes did not fit#

A unique index on email. This is the textbook answer, and it works when one column identifies a person. Here it doesn't. Matching is heuristic: a person can be recognised by an email, a phone number, a cookie ID and other attributes, each with a weight, and a match is found when the weights add up to a threshold. Someone can arrive with only a cookie ID today and with an email tomorrow. A person can have several emails. There is no single column a unique constraint could protect.

SERIALIZABLE isolation. Postgres would detect the conflict and abort one of the transactions. That is correct, but it moves the problem into the application: every caller has to retry, and under load the retries collide again. I wanted the second request to simply wait and then do the right thing.

Locking rows with SELECT … FOR UPDATE. You can only lock rows that exist. The whole problem is the row that does not exist yet.

What I needed was a lock on an idea: "the person with this email", whether or not a row for them exists.

The fix: transaction-scoped advisory locks#

PostgreSQL has exactly that. An advisory lock is a lock on an arbitrary number that means whatever your application decides it means. Postgres does not attach it to any table; it only guarantees that two transactions cannot hold the same one at the same time.

The idea:

1. From the incoming data, build a lock key for every attribute that can identify a person, using the normalised value: email:anna@example.com, cookie:7f3a…, phone:+4917….
2. At the start of the upsert transaction, take a transaction-scoped advisory lock for each key (the key is hashed to the number Postgres expects).
3. Only then search for a match, and insert or update.
4. The locks are released automatically when the transaction commits or rolls back.

Now the second request blocks on the lock until the first one has committed. When it continues, the first request's row is already there, so it finds it and updates it instead of inserting a duplicate.

A few details made the difference between "works in the demo" and "works in production":

• Transaction-scoped, not session-scoped. A session-level lock survives the transaction and has to be released by hand. With a connection pool, one forgotten unlock leaves a lock sitting on a connection that the next unrelated request will reuse. Transaction-scoped locks cannot leak.
• Sorted keys. A request with an email and a phone and another with the same phone and email would otherwise lock in different orders and deadlock each other. Sorting the keys gives every request the same order.
• All matching attributes become keys, regardless of weight. My first version only locked attributes that could reach the match threshold on their own. A test (more on those below) showed that two weak attributes can add up to a match together, so those requests slipped past each other. Every attribute that takes part in matching now gets a key.
• A lock timeout. If something holds a lock far too long, a request should fail clearly instead of hanging. The upsert sets a short lock_timeout for its own transaction only.
• One round trip. Taking the locks one query at a time added latency for requests with many identifiers, so all locks are taken in a single statement.

The second gap: same person, different keys#

With the first fix in place, the tests found a subtler problem.

A user can be reachable by different keys. One request arrives with Anna's email and a new phone number. At the same moment, another arrives with Anna's cookie ID and a second phone number. They lock different keys (email:… and cookie:…), so they don't wait for each other. Both find the same existing user, both read it, both merge their phone number into what they read, and the one that writes last overwrites the other. One phone number is silently lost.

The fix is a second lock on the user itself: once a request has decided which existing user it matches, it takes a lock on user:<id> and re-reads the row before merging. If another request got there first, the second one now waits, re-reads the freshly committed data, and merges on top of it. Both phone numbers survive.

The re-read is the important part. Without it, the lock would only make the two requests take turns writing stale data.

A related bug: one person, three phone formats#

Lock keys are only as good as their normalisation. +49 170 1234567, 0170 1234567 and 0049-170-1234567 are the same number, but as strings they are three different keys, and three different keys don't block each other. The matcher missed those matches too.

In a follow-up change I replaced the hand-written phone cleanup with libphonenumber-js, which parses numbers into the international E.164 format (+491701234567) with a sensible default country. Since then, lock keys and stored values agree, no matter how a form or an import wrote the number.

Proving it: real concurrency, real Postgres#

A race condition you have not reproduced is a race condition you have not fixed. Unit tests with a mocked database can't show this bug at all: a mock answers one call at a time.

So the tests run against the real thing:

• Testcontainers starts a fresh Postgres (and Redis) container for every test run and throws it away afterwards.
• The real API is built and started against that database.
• The tests fire requests in parallel with Promise.all and then check the database directly.

One detail is easy to miss: by default the API's database pool had one connection. With a single connection every request is queued behind the previous one, so the race can never happen and a concurrency test passes whether the fix is there or not. Raising the pool size for the tests is what made them honest. If you test concurrency, check that your test setup is actually concurrent.

The suite covers the scenarios that matter:

Scenario	Expected
Two identical upserts at the same time	1 user
Same email, different other attributes	1 user, attributes merged
Matched by cookie ID, different emails	1 user
10 identical upserts at once	exactly 1 row
Same user reached via different keys (email vs cookie)	both updates kept, nothing lost
Match reached only by summing weak attributes	1 user
Another transaction holds the lock past the timeout	clean error, no hang
Requests with no matching attributes	each creates its own user, no blocking

The summed-weights case is the one that found a gap in my first version of the fix: it led to locking every matching attribute, not only the strong ones.

What I took away#

• "Check, then act" is never atomic unless something makes it so. If the thing you check for might not exist yet, a row lock can't help; an advisory lock can.
• Lock the concept, then lock the row. The key locks prevent duplicates; the user lock and the re-read prevent lost updates. They solve different problems.
• Normalise before you compare, and before you lock. Two spellings of the same value are two different locks.
• Make your tests capable of failing. A concurrency test that runs on one connection proves nothing. The most useful moment in this project was watching a test go red for the right reason.

---

Stack: PostgreSQL 16, TypeScript, Drizzle ORM, Vitest, Testcontainers, libphonenumber-js.