11. Lightweight transactions, batches, and counters

Cassandra has no transactions. It has three features that people reach for when they want transactions, all three of which do something narrower than they appear to, and all three of which are commonly misused.

Lightweight transactions

An LWT is a compare-and-set on a single partition, implemented with the Paxos consensus protocol.

-- Insert only if the row does not exist
INSERT INTO users (username, email) VALUES ('alice', 'a@b.com')
IF NOT EXISTS;

-- Update only if the current value matches
UPDATE users SET email = 'new@b.com'
WHERE username = 'alice'
IF email = 'old@b.com';

-- Delete only if it exists
DELETE FROM users WHERE username = 'alice' IF EXISTS;

The result set tells you whether it applied:

 [applied] | email
-----------+-------------
     False | current@b.com

[applied] = False comes with the current values of the columns you tested, so you can decide what to do without a second read.

Why it is expensive

Every other write in Cassandra is fire-and-forget to N replicas. An LWT needs the replicas to agree, which needs consensus, which needs round trips.

   ① PREPARE   coordinator proposes a ballot number to the replicas
               replicas promise not to accept anything older
                              ▼
   ② READ      coordinator reads the current value at SERIAL
               (needed to evaluate the IF condition)
                              ▼
   ③ PROPOSE   coordinator proposes the new value
               replicas accept it
                              ▼
   ④ COMMIT    coordinator tells replicas to make it visible

Four phases, each a round trip to a quorum. An LWT costs roughly 4× a normal write in round trips and, in practice, often 10–20× in latency. Cassandra 4.1 introduced Paxos v2, which cuts the common case to fewer round trips, but the order-of-magnitude framing still holds.

There is also a durable side effect: Paxos state is written to the system.paxos table, which generates its own writes and tombstones.

Contention makes it much worse

Paxos is a consensus protocol, and consensus under contention degrades badly. If several clients run LWTs against the same partition simultaneously, ballots compete, proposals get superseded, and clients retry.

WriteTimeoutException: Operation timed out - received only 0 responses
                       (writeType=CAS)

Under heavy same-partition contention, throughput can collapse to a handful of operations per second. LWTs on a hot partition are one of the reliable ways to make a Cassandra cluster look broken.

SERIAL consistency

LWTs use a separate consistency level for the Paxos phases:

CONSISTENCY QUORUM;          -- for the commit
SERIAL CONSISTENCY LOCAL_SERIAL;  -- for the Paxos phases

• SERIAL — a quorum across all data centres.
• LOCAL_SERIAL — a quorum within the local data centre only.

Use LOCAL_SERIAL in a multi-DC cluster, for exactly the reasons in chapter 9 — SERIAL with two data centres means every Paxos phase crosses the WAN, and four WAN round trips per operation is not viable.

The cost of LOCAL_SERIAL is that the linearizability guarantee is local. Two data centres running LWTs on the same partition at LOCAL_SERIAL can both succeed and produce a conflict. If you need global uniqueness, you need SERIAL and its latency.

Reading LWT-written data

A normal SELECT at QUORUM may miss an in-flight Paxos operation. To read with linearizable guarantees:

SELECT * FROM users WHERE username = 'alice';   -- with CL = SERIAL

This makes the read participate in Paxos, completing any in-progress operation it finds. It is expensive, and it is the only way to get a truly linearizable read.

When to use one

Good uses, all characterised by being rare and low-contention:

• Unique username or email registration.
• Claiming a resource exactly once (“this coupon code has now been used”).
• A state machine transition that must not be applied twice.
• Distributed lock acquisition, carefully.

Bad uses:

• Anything on a hot partition.
• Anything in a high-throughput path.
• As a general replacement for transactions — it is one partition, one statement.
• Counters. Use the counter type.

The honest guidance: if more than a small percentage of your writes are LWTs, Cassandra is probably the wrong database for this workload. LWTs are an escape hatch for the rare case where you genuinely need consensus, not a foundation to build on.

Accord is coming

CEP-15 (“Accord”) is a leaderless consensus protocol designed to give Cassandra general-purpose, multi-partition transactions at roughly the cost of a single round trip — a substantial improvement on Paxos. It has been designed and is being implemented, but it is not a released feature. Worth watching; not yet worth planning around.

Batches

BATCH is the most misunderstood feature in Cassandra, because in SQL a batch is a performance optimisation and here it is usually the opposite.

BEGIN BATCH
  INSERT INTO messages_by_room   (room_id, ...) VALUES (...);
  INSERT INTO messages_by_sender (sender,  ...) VALUES (...);
  INSERT INTO messages_by_id     (message_id, ...) VALUES (...);
APPLY BATCH;

What a logged batch actually guarantees

Atomicity, in a narrow sense: if the batch is accepted, all of its statements will eventually be applied. The coordinator writes the batch to a batchlog on two other nodes before executing anything. If the coordinator dies partway through, another node replays the batchlog and completes the remaining statements.

What it does not give you:

• No isolation. Other clients can see some statements applied and others not, for as long as the batch takes.
• No rollback. There is no way to undo. It only ever rolls forward.
• No ordering. Statements may be applied in any order.
• No conditionality. One statement failing does not prevent the others.

So it is “all of these will happen, eventually, in some order, visibly interleaved.” That is a genuinely useful guarantee for keeping denormalised tables in sync, and it is not a transaction.

The cost

A logged batch writes to the batchlog on two nodes first, then does the actual writes. That is roughly 3× the write volume of doing the statements individually.

The anti-pattern

This is the mistake, and it is extremely common:

-- WRONG: 1,000 rows across 1,000 different partitions
BEGIN BATCH
  INSERT INTO events (id, ...) VALUES (uuid_1, ...);
  INSERT INTO events (id, ...) VALUES (uuid_2, ...);
  ... 998 more ...
APPLY BATCH;

People do this expecting the batching efficiency of an SQL bulk insert. What actually happens:

   ONE coordinator becomes responsible for 1,000 partitions
   scattered across the entire cluster.

   It writes 1,000 statements to the batchlog on 2 nodes.
   It sends 1,000 mutations to ~1,000 different replica sets.
   It waits for ALL of them.
   It holds all that state in heap the whole time.

   One slow replica stalls the entire batch.
   If the coordinator OOMs, everything retries.

   Meanwhile every other node in the cluster is idle.

The correct version is 1,000 individual asynchronous writes. The driver spreads them across coordinators, each is token-routed to its own replicas, and a slow node affects one write rather than all of them. It is consistently faster and far more resilient.

futures = [session.execute_async(stmt, params) for params in rows]
for f in futures:
    f.result()

Cassandra will warn you, and can be made to refuse:

batch_size_warn_threshold: 5KiB
batch_size_fail_threshold: 50KiB

WARN  Batch for [ks.events] is of size 128KiB, exceeding specified
      threshold of 5KiB by 123KiB

When a batch is right

Single-partition batches. All statements target the same partition key. This is genuinely efficient — the whole batch becomes one mutation to one replica set, applied atomically and in isolation, with no batchlog needed. The coordinator even recognises this and skips the batchlog.

-- GOOD: one partition
BEGIN BATCH
  INSERT INTO messages (room_id, bucket, sent_at, message_id, body)
    VALUES (?, ?, ?, ?, ?);
  UPDATE room_stats SET last_message_at = ? WHERE room_id = ? AND bucket = ?;
APPLY BATCH;

Small multi-table batches for denormalisation. Writing one logical fact into three denormalised tables (chapter 4). Two to five statements. The atomicity is exactly what you want, and the batchlog overhead is worth it.

   Use BEGIN BATCH when:
     ✓ all statements hit the SAME partition, or
     ✓ you need "all of these eventually", and there are only a few

   Do NOT use it:
     ✗ to bulk-load many rows
     ✗ across many partitions
     ✗ expecting a transaction

UNLOGGED BATCH skips the batchlog and drops the atomicity guarantee. Its only legitimate use is multiple statements against a single partition, where it is a genuine optimisation. Across partitions it is simply a slower way to issue parallel writes.

Counters

Counters exist because UPDATE x SET n = n + 1 cannot work under last-write-wins — two concurrent increments would both write the same value and one would be lost.

CREATE TABLE page_views (
  page_id text,
  day     date,
  views   counter,
  PRIMARY KEY ((page_id), day)
);

UPDATE page_views SET views = views + 1 WHERE page_id = ? AND day = ?;

Counters live in a parallel universe with their own rules:

A table has counter columns or normal columns, never both. Only primary key columns may accompany a counter.

You cannot INSERT a counter, only UPDATE it. An unset counter reads as 0.

Counters are not idempotent. This is the big one. The write path does a read-modify-write internally, so replaying a counter update applies it twice. If a counter update times out, you do not know whether it applied — and you cannot safely retry.

   UPDATE ... SET views = views + 1
        │
        └──▶ WriteTimeoutException
                  │
        Did it apply?  Unknown.
        Retry?  You might double-count.
        Don't retry?  You might undercount.

Drivers default to not retrying counter writes, which is correct.

Counters are slower. The internal read-before-write means they do not get the cheap append-only write path.

Deleting a counter is permanent-ish. After DELETE, you must not increment that counter again — the deletion timestamp interacts badly with subsequent updates and produces wrong values. There is no supported way to reset a counter to zero and reuse it.

Counters cannot have a TTL.

When to use them, and what to use instead

Counters are fine for approximate, high-volume counting where being off by a small amount does not matter: page views, likes, impressions, rough metrics.

Do not use them for anything that must be exact. Account balances, inventory, billing. Not because they are broken, but because the non-idempotency means a network timeout produces an unknowable result, and you cannot audit or reconstruct it.

The alternative that experienced teams use: write immutable events and aggregate.

CREATE TABLE view_events (
  page_id text,
  day     date,
  event_id timeuuid,
  PRIMARY KEY ((page_id, day), event_id)
);
-- count with a scan of one partition, or roll up periodically into a
-- normal (non-counter) column

More storage, more read work, but idempotent, auditable, retry-safe, and reconstructible. For anything where the number matters, that trade is right.

Summary of what each gives you

	Atomic	Isolated	Rollback	Cost	Use when
Normal write	per partition	no	no	1×	almost always
Single-partition batch	yes	yes	no	~1×	several statements, one partition
Logged batch	yes (eventually)	no	no	~3×	a few statements keeping denormalised tables in sync
Unlogged batch	no	no	no	1×	single partition only
LWT	yes, one partition	yes	n/a	~10–20×	rare compare-and-set
Counter	yes	no	no	~2×	approximate counting only

What to take from this chapter

• LWTs are Paxos-backed compare-and-set on one partition. Roughly 4× the round trips and often 10–20× the latency. Use LOCAL_SERIAL in multi-DC. They degrade badly under same-partition contention. Use them rarely.
• BATCH is not a transaction. It gives “all of these eventually,” with no isolation, no ordering, and no rollback.
• A multi-partition batch is an anti-pattern. Issue parallel async writes instead. Batches are for a single partition, or a few statements keeping denormalised tables in sync.
• Counters are not idempotent and cannot be safely retried. Use them for approximate counting only; use immutable events plus aggregation for anything that must be exact.
• If a large share of your writes need LWTs or real transactions, question whether Cassandra fits this workload.

Next: multiple data centres, and what happens when a whole region disappears.