Debugging Transaction Contention in CockroachDB

Transaction contention is usually invisible until it isn’t — throughput looks fine, then a handful of hot rows turn into a queue of blocked transactions and your p99 latency falls off a cliff. CockroachDB ships good tooling for this, but it’s easy to miss if you don’t know where to look.

Start with the contention views

CockroachDB exposes contention data directly in SQL:

1
2
3
4

SELECT *
FROM crdb_internal.transaction_contention_events
ORDER BY collection_ts DESC
LIMIT 20;

This gives you the blocking and waiting transaction fingerprints, the table and index involved, and how long the wait lasted. For a higher-level view, crdb_internal.cluster_contended_tables aggregates by table so you can quickly spot the hot spot:

1
2
3

SELECT database_name, schema_name, table_name, num_contention_events
FROM crdb_internal.cluster_contended_tables
ORDER BY num_contention_events DESC;

The usual suspects

In practice, most contention I’ve debugged falls into one of three patterns:

1. A monotonically increasing key. Sequences, now()-derived primary keys, or auto-incrementing IDs all funnel writes onto the same range and the same leaseholder. INSERT INTO events (id, ...) VALUES (gen_random_uuid(), ...) spreads writes far better than a sequential integer.
2. A single “counter” or “status” row. Things like an account balance or a job queue’s status column get updated by every transaction touching that entity, serializing work that didn’t need to be serialized.
3. Long-running transactions holding locks. A transaction that does an API call or heavy computation between its first write and its commit holds locks the whole time. Keep transactions short and push non-database work outside the BEGIN/COMMIT boundary.

A concrete fix: splitting a hot counter

If you have a table like this:

1
2
3
4

CREATE TABLE inventory (
  sku STRING PRIMARY KEY,
  quantity INT NOT NULL
);

and many concurrent workers decrementing quantity for the same popular SKU, consider sharding the counter:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE inventory_shards (
  sku STRING NOT NULL,
  shard INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (sku, shard)
);

-- read the total
SELECT sku, sum(quantity) FROM inventory_shards WHERE sku = 'WIDGET-1' GROUP BY sku;

-- write to a random shard
UPDATE inventory_shards
SET quantity = quantity - 1
WHERE sku = 'WIDGET-1' AND shard = floor(random() * 8)::INT;

This trades a small amount of read complexity for a large reduction in write contention, since decrements are now spread across eight rows instead of one.

Takeaway

Contention debugging in CockroachDB is mostly about asking “what’s the minimum set of rows this transaction actually needs to touch, and for how long?” The built-in crdb_internal views make it easy to confirm a hypothesis — the harder part is usually admitting that a hot row in your schema was inevitable given the access pattern, and redesigning around it.