Database Locks: Why You Shouldn't Let Your AI Loose

Code written by large language models (LLMs) tends to default to database locking to deal with concurrency. I want to explain why this happens, and why it's bad if you're not paying attention.

Context

When two or more requests target the same database row for a change at the same time, the system has to decide what happens: who wins. LLM-written code usually defaults to a lock-driven approach. It opens a transaction and locks the row with a SELECT ... FOR UPDATE or, worse, an explicit advisory lock, so other requests that try to do the same thing stop at that point and wait for permission to acquire that same lock. The database only lets one connection hold that lock at a time. It's a funnel, and requests queue up to get through.

A second way, although less common in my experience, is to run the transaction with SERIALIZABLE isolation, which makes databases such as Postgres reject one of the conflicting transactions.

Anyway, both approaches are technically correct, and the intent is correct. They just don't account for scale and production load.

Some factors contribute to this model behaviour. I can list a few without deep research:

• Training data is dominated by tutorials, sample applications and simple projects, where a lock is the simplest way to achieve the correct solution.
• Prompting tends to be safety-first. You know: "don't break anything", "make no mistakes". A lock is the cheapest, most straightforward and effective way to do just that.
• Locks make code work in isolation. The alternative, eventual consistency, requires context and reasoning about operation order, latency, late arrivals, retries, and all that fun stuff.

Eventual consistency just means that the system accepts each operation without coordinating with other concurrent operations. Instead of all readers seeing the same value right away, they will eventually see the same value. Last write wins is the simplest form of eventual consistency: if two or more edits update the same data, the last one to write it (commit) is the value that persists.

The grain of salt: the earlier edit is overwritten without warning, this works for a time entry description, but not for a bank balance. Still, there are better ways to handle it, such as versioning or idempotency.

Now, to measure the cost of defaulting to locks, I made a simple benchmark.

Benchmark

Let's say we have a time-tracking API, just like we do at Toggl, using Go and PostgreSQL. We want to measure how many users can save edits to their own time entries (e.g. changing the description) before writes start taking longer than 2 seconds.

We'll implement this in a few different ways:

• a) Row lock (FOR UPDATE). Four steps inside a database transaction: BEGIN the transaction, read the time entry with FOR UPDATE (which locks the row until commit), UPDATE, and then COMMIT.
• b) Workspace lock (advisory lock). An explicit advisory lock on the whole workspace: a lock named by the workspace id, which blocks every other transaction asking for the same key until commit, even when they touch different time entries.
• c) Serializable. The same read and update inside a SERIALIZABLE transaction. When two transactions conflict, Postgres aborts one of them, and that request retries.
• d) Last write wins. No explicit locks. A single UPDATE statement with no explicit transaction. PostgreSQL commits it immediately, and if two requests update the same row, the second one waits only for the first statement to finish and then overwrites it (an implicit row lock, managed by Postgres itself and held only for that one statement).
• e) Version check. If you must make sure nobody changed the row in between, each row carries a version number. You read the version, then update only if it's still the same, bumping it as you go. If someone else got there first, the update matches 0 rows and you read and try again. No lock is held between the read and the write.
• f) Time entry lock (advisory lock). The same explicit advisory lock, but named by the time entry id instead of the workspace, so only writes to the same time entry wait for each other.

We perform the same change with every pattern: update time_entry.description for one entry. In its simplest form (last write wins), that's UPDATE time_entry SET description=$2 WHERE id=$1.

This was done on my local development machine, using Postgres 18 and a tiny Go API. It is by no means a representation of a production setup, but again, this is about what happens, you can extrapolate from here.

Results

Each pattern ran 3 times, and here I’m reporting the median run. Each run was concurrent with 500 “timer-page” reads per second in the background.

Users served

This is the highest number of users at which at least 99.9% of saves finished within 2 seconds. Each user edits their own running time-entry once a minute, e.g. 360k users is 6k writes per second.

Data

	Users, each saving once a minute
Last write wins	720k
Version check	480k
Row lock	480k
Serializable	360k
Time entry lock	360k
Workspace lock	240k

The last write wins pattern served 720k users, version check and row lock 480k, serializable and the time entry lock 360k, and the workspace lock 240k. Row lock reached 480k and serializable 360k in their median runs, but both only 240k in their worst run.

Time to write as users grow

Time from the moment a user presses save until the change is written, including any wait for a database connection.

• Last write wins
• Version check
• Row lock
• Serializable
• Time entry lock
• Workspace lock
• Deadline: slower saves fail

Data

Users, each saving once a minute	Last write wins	Version check	Row lock	Serializable	Time entry lock	Workspace lock
120k	3 ms	4.6 ms	7.1 ms	7.1 ms	8.9 ms	10 ms
240k	3 ms	4.5 ms	7.5 ms	8.8 ms	9.6 ms	10.9 ms
360k	3 ms	6.1 ms	33 ms	11 ms	52 ms	238 ms
480k	3 ms	72 ms	800 ms	1.1 s	1.1 s	1.3 s
720k	23 ms	1.2 s	1.4 s	1.9 s	2 s	2 s
1M	1.1 s	2 s	2 s	2 s	2 s	2 s

At 480k users, the median write took 3 ms with the last write wins pattern, 72 ms with version check, 0.8 s with row lock, and 1.1 to 1.4 s with serializable and both advisory locks. Last write wins stayed at 3 ms up to 480k users and took 23 ms at 720k.

Adding async flows alongside locks

What happens when the authorization or external call is made?

For 360k users, every write waits on a call that takes 0, 20, 100 or 500 ms. The four locking patterns make the async call inside the transaction (don’t do this!), while holding the lock and a connection. As you would expect, only patterns with no explicit lock, or serialization, make it 100% of the time.

• Last write wins
• Version check
• Row lock
• Serializable
• Time entry lock
• Workspace lock

Data

Duration of the authorization or external call	Last write wins	Version check	Row lock	Serializable	Time entry lock	Workspace lock
0 ms	100%	100%	100%	100%	100%	100%
20 ms	100%	100%	100%	100%	100%	68%
100 ms	100%	100%	7%	11%	7%	2%
500 ms	100%	100%	0%	0%	0%	0%

At 20 ms, the workspace lock finished 68% of saves. At 100 ms, the four locking patterns finished 2% to 11%, and at 500 ms none. Last write wins and version check finished 100% at every duration; at a 100 ms call, the median last write wins save took 103 ms.

What about reads?

500 timer-page reads per second (using an indexed lookup of the user's running entry), running next to the saves of 360k users.

Data

	99th percentile read time
Last write wins	15 ms
Version check	177 ms
Row lock	263 ms
Serializable	401 ms
Workspace lock	581 ms
Time entry lock	736 ms

The 99th percentile read took 15 ms with the last write wins pattern and 177 to 736 ms with the other patterns, 12 to 49 times longer, although the reads take no lock.

Design implications

Use last write wins as the default if you care about scale, and prefer to massage your interfaces to gracefully handle eventual consistency. If you can't afford to silently lose an edit, use version check, or similar patterns.

Avoid slow or remote calls inside your transactions at all costs, especially if you have to use locks (and try not to use explicit locks at all). This is especially important if you have no control over the call, such as a remote service (at the very least, add a reasonable timeout).

Make writes idempotent, sending the same thing twice should produce the same result. This allows retries and duplicate deliveries to happen safely.

For rows written at sustained high rates, insert new rows (append-only) or use writes whose order doesn't matter, such as count = count + 1. Last write wins on a single row is still limited by how fast the database can flush each commit to disk.

Where a lock is absolutely required (monetary movements, inventory, invoice numbering, etc.), keep the transaction short, and lock only what you need (not the entire workspace -.-).

Back to LLM code reviews

LLM-generated code introduces these patterns by default, so take special care when reviewing any FOR UPDATE, FOR SHARE, advisory lock or isolation level changes. They may be needed, but often they are not. Of course that may imply larger scope to handle consistency upstream.

Transactions must not include network calls, including authorization checks - solve these before.

These rules can be added to AI-powered review bots, or part of repository rules. That will keep things sound for a while, but in my experience, they start to drift at some point - especially if you need to have exceptions to the rule.

Anyway, conclusion is

For database health, last write wins pattern… wins. In our little showcase it served 720k users making one edit per minute, 1.5 to 3 times more than the locking patterns. This matters a lot for production-grade applications serving millions of users with many requests per second. That’s not to say that you need it everywhere, it may not be worth the headache in your case, as with everything, viewer discretion is advised. With experience, you learn where to look, and how to scan for these things.

Unless you tell them to, LLMs usually don’t account for these effects, until they hit you in the face. Locks should be an explicit, reviewed exception; last write wins, idempotent writes and append-only writes should be the default. Again, if you care about scale.

At Toggl we sure do, and now I have data to point to.