Explain Lightweight Transactions (LWT) & Compare-and-Set in Databases

27 / 66

Apache Cassandra

Lightweight Transactions (LWT) & Compare-and-Set

Understand how Cassandra achieves linearizable compare-and-set semantics despite its eventually consistent design.

Cassandra writes without checking anything first. That is why writes are fast, and why "insert this username only if nobody has it" is not an ordinary write.

A normal Cassandra write is like shouting an update to everyone in the room without waiting to see if it's already been changed. An LWT is like calling a formal meeting, taking attendance, checking the current state, voting on whether to accept the change, and only then confirming it — much slower, but guarantees no conflicting change slips through.

Key Concepts

Why a read-then-write does not work. The obvious approach:

    SELECT * FROM users WHERE username = 'asha';   -- empty
    INSERT INTO users (username, ...) VALUES ('asha', ...);

Two clients can run the SELECT at the same moment, both see nothing, and both insert. The second write silently overwrites the first. No error, one account lost.

SELECT

Lightweight transactions close that gap. The condition is checked and the write applied as one indivisible step:

    INSERT INTO users (username, email) VALUES ('asha', 'a@x.com') IF NOT EXISTS;
    UPDATE orders SET status = 'SHIPPED' WHERE id = 7 IF status = 'PACKED';

The response tells you whether it applied. Two concurrent attempts: exactly one gets applied = true.

applied = true

How, and why it costs so much. Cassandra runs Paxos between the replicas — a round to propose and gather promises, a round to accept, then the write and a commit round.

    normal write:  coordinator -> replicas          1 round trip
    LWT:           propose / promise
                   read current value
                   accept / accepted
                   commit                           4 round trips

So an LWT is roughly four times the latency of a normal write, and the rounds are sequential. Under contention it is worse: competing proposals interfere and retry, so throughput collapses exactly when several clients want the same row — which is precisely when you reach for it.

One partition only. The consensus is per partition. You cannot make a conditional change across two partitions atomically, so LWTs are not a way to build multi-row transactions.

Where they earn their cost. Uniqueness on a key, a distributed lock, leader election, a state machine that must not skip a step. All are rare writes where being wrong is expensive.

Where they do not. Any normal write with an IF bolted on out of caution. A common and costly mistake is putting IF NOT EXISTS on every insert "to be safe" — that makes every write four times slower to prevent something that was never a problem, since an overwrite with identical data is harmless.

IFIF NOT EXISTS

The conditional row must be the one you are protecting. IF NOT EXISTS on users_by_email guarantees email uniqueness only if email is that table's partition key. Applying it to a table keyed by user_id guarantees nothing about emails at all.

IF NOT EXISTSusers_by_emailemailuser_id

What the interviewer is probing.1. "What does IF NOT EXISTS actually do?" Probing: the mechanism. Stalls: "A check." *Moves up:* runs Paxos across replicas to agree, which is why it is called lightweight relative to a full transaction but is heavy for Cassandra.

2. "What is the performance cost?" Probing: the number worth knowing. Stalls: "Small." Moves up: roughly four round trips instead of one, so it is many times slower — reserve it for genuine uniqueness needs.

3. "What consistency does it use?" Probing: the detail. Stalls: "The usual." Moves up: SERIAL or LOCAL_SERIAL, which is a separate setting from the normal consistency level.

4. "When is LWT the wrong answer?" Probing: the design pressure. Stalls: "Never." *Moves up:* when used for ordinary writes — if your model needs many compare-and-set operations, Cassandra may be the wrong store.