Choose a concurrency scheme
Many transactions write to the same data at once, say checkouts reserving seats during a flash sale. Two of them can touch the same row. Every scheme keeps the data correct; they differ in what a conflict costs: locking makes transactions wait, optimistic schemes throw finished work away, and weaker guarantees push the repair onto the application. Which cost should your workload pay?
You will end with one concurrency scheme, the conflict rate that justifies it, and what to watch once it runs.
Before you choose
These three apply whatever you end up with.
Know how often transactions conflict
The number that decides this page is the conflict rate: how often two transactions in flight touch the same key. Estimate it, or measure the abort rate under real or replayed load.
To estimate: transactions in flight = arrival rate × how long each lasts (10,000 per second lasting 5 ms each is 50). Then expected conflicts ≈ (in flight)² × (keys each touches)² ÷ (2 × keys that actually receive writes). Use the keys that actually get traffic, not the row count: skewed traffic makes that number 100 to 1,000 times smaller.
Why
Conflict grows with the square of transactions in flight: ten times the throughput buys a hundred times the conflict. Every scheme behaves acceptably when access is uniform; they differ almost entirely under skew, which is why the estimate must use the keys that really receive writes.
Shorten transactions first
Move validation and calls to other services out of the transaction, avoid round trips to the client inside it, and shorten the commit path. It beats every scheme change and is almost always available.
For example: a transaction holds its locks for 20 ms because it calls a pricing service in the middle. Moving that call before BEGIN cuts it to 5 ms: four times fewer transactions in flight, so 16 times less conflict.
Why
Transactions in flight grow with their duration, and conflict grows with the square of that, so halving the duration cuts conflict to a quarter. It is the only escape with no structural cost.
Long reads alongside writes need multiversioning
If long-running reads (reports, exports) run while others write, use a multiversion engine (MVCC) underneath whichever scheme you pick, so readers see a snapshot instead of blocking writers. Then decide who pays for old versions before you deploy: table bloat in the PostgreSQL family, undo retention in the InnoDB and Oracle family.
Why
Every old version must be kept until the oldest reader that might need it finishes. One long reader can therefore hold back cleanup for the whole table.
Decide
Do many transactions write to more than one partition or machine?
Why
Once one transaction must commit on several machines, the cost is no longer the concurrency scheme but agreement between machines, and that has its own arithmetic: availability multiplies across participants.
Are each transaction's reads and writes known before it starts, with no user interaction mid-way?
Why
If transactions run in one agreed order, no conflict has to be detected at all, so its cost does not grow with conflict. It needs the access sets up front.
Is the contention concentrated on a few hot keys?
For example: most buyers want the same 200 seats, or every transaction updates one counter.
Why
On a hot key, locking makes transactions queue and optimistic schemes make them abort and retry; neither removes the conflict. Only changing the operation or the layout does.
Can the operation be rewritten so the order of updates does not matter?
Adding to a counter can be; 'set the balance to what I read plus 10' cannot.
Why
If updates commute, they no longer conflict, so there is nothing to wait for or abort. That removes the problem instead of choosing who pays for it, and it is usually cheaper than it looks.
What is the conflict rate, with transactions kept short?
< 1–2%Optimistic
> 10%Locking
BetweenMeasure both under replayed load and keep the one with more completed work
Why
When conflicts are rare, locking is a cost paid on every operation against something that almost never happens. When they are common, optimism does not degrade gracefully: it throws away more and more finished work.
Where you can land
Optimistic (OCC, serializable snapshot isolation, compare-and-swap)
Use it when conflicts are rare and transactions are short.
Transactions run without locks and check for conflicts at commit; the loser aborts and retries.
Before you build it
Watch completed work, not throughput
Under rising conflict, optimistic schemes collapse into burning: every core at 100%, almost nothing committing. The tell is high CPU with a falling commit rate. Track committed transactions, not attempts.
Protect long transactions
A long transaction has the longest window in which to be invalidated, so under contention it may never commit. Fall back to locking after a set number of aborts.
Locking (two-phase locking)
Use it when conflicts are common.
Transactions take locks as they go and hold them until commit; a conflicting transaction waits.
Before you build it
Watch for thrashing
Blocked transactions stay open longer, which raises transactions in flight, which raises conflict, which blocks more. Throughput rises, peaks, then falls; models put the knee at roughly a third of transactions blocked (an order of magnitude, not a threshold to tune against). Once in that loop, a restart does not help if the backlog recreates it.
Commutative operations
Use it when contention is on a few keys and the operation's order does not matter.
Rewrite updates so they combine in any order, such as increments instead of overwrites.
What it costs: works only where the operation has that algebra.
Partition the hot keys
Use it when contention is on a few keys and the operation cannot be made commutative.
Spread the hot data across partitions, each serialized on its own.
What it costs: transactions that span partitions become a cliff, not a slope; they turn into a distributed commit problem.
Deterministic execution
Use it when every transaction's reads and writes are known in advance, and none is interactive.
A sequencer fixes the order of transactions; every replica runs them in that order, so nothing has to be detected or voted on.
What it costs: no interactive transactions, access sets declared up front, and a global sequencer. Worth it for a narrow class of workloads.