When to Use Cassandra Lightweight Transactions for Compare‑and‑Set

The problem: racing inserts that must be unique

Imagine a service that lets users pick a username. Two requests arrive at almost the same time, both trying to insert a row with the same user_id. A regular INSERT would silently overwrite or create duplicate rows, breaking the uniqueness guarantee. You need a compare‑and‑set operation that succeeds only if the row does not already exist.

Thesis: Lightweight Transactions (LWT) give you linearizable compare‑and‑set in Cassandra, but they cost extra round trips and should be reserved for low‑contention paths.

Cassandra’s LWT implementation uses the Paxos consensus protocol to make sure that only one replica can win the race for a given partition. The operation is invoked with an IF NOT EXISTS (or any IF ) clause in CQL.

How an LWT works under the hood

When you execute INSERT INTO users (id, name) VALUES (uuid(), 'alice') IF NOT EXISTS, the coordinator runs four network phases:

1. Prepare/Promise – the coordinator asks a quorum of replicas to promise not to accept any lower‑numbered proposals.
2. Propose/Accept – it sends the actual proposal (the insert) to a quorum; each replica accepts if its promise matches.
3. Commit – once a majority accepts, the coordinator sends a commit to all replicas.
4. Response – the coordinator returns the [applied] flag to the client.

Each phase requires a round trip, so a typical LWT write incurs about four times the latency of a regular write.

Worked example: registering a unique user

Start a local Cassandra node (e.g., docker run -p 9042:9042 cassandra:4) and open cqlsh. Create a keyspace with RF=3:

CREATE KEYSPACE demo WITH replication = {'class':'SimpleStrategy', 'replication_factor':3};

Use the keyspace and create a simple table:

USE demo;
CREATE TABLE users (id UUID PRIMARY KEY, name TEXT);

Now attempt the insert from terminal 1:

INSERT INTO users (id, name) VALUES (uuid(), 'alice') IF NOT EXISTS;

You should see a result like:

 [applied]
-----------
     True

Repeat the exact same statement in terminal 2 (without changing the id value). Because the row already exists, the second attempt returns:

 [applied]
-----------
     False
 [id]                             | [name]
----------------------------------+--------
  a1b2c3d4-...                    | alice

The [applied] flag tells you whether the compare‑and‑set succeeded. This pattern guarantees that only one client can win the race, even under concurrent requests.

Trade‑offs and limitations

• Latency: each LWT write adds roughly three extra round trips compared to a plain write. In a LAN with 1 ms round‑trip time, a regular write might take ~1 ms, while an LWT could be ~4 ms.
• Throughput: because Paxos requires a quorum of replicas to participate, the effective write throughput can drop by an order of magnitude under load.
• Replication factor impact: higher RF means more nodes must respond in each phase, increasing both latency and the chance of failure during a network partition.
• When to avoid: high‑frequency write paths such as telemetry ingestion, logging, or any workload that needs sub‑millisecond write times should stick to regular writes and handle uniqueness at the application level (e.g., using UUIDs).

LWTs shine for low‑contention scenarios like:

• User registration or profile creation where a natural key must be unique.
• Feature‑flag toggles that are changed infrequently.
• Idempotent service initialization steps.

Actionable checklist

1. Identify whether the operation truly needs compare‑and‑set semantics.
2. Measure baseline write latency with regular INSERT using cqlsh -e "TIMING ON; INSERT …".
3. Run the same statement with IF NOT EXISTS and observe the extra latency.
4. If latency is acceptable and contention is low, adopt LWT; otherwise, redesign to avoid the need for coordination (e.g., use time‑uuid columns or application‑generated unique IDs).
5. Monitor system tables (system.paxos) for failed Paxos rounds if you see spikes in latency.

By reserving Lightweight Transactions for the few places where you truly need linearizable compare‑and‑set, you keep Cassandra’s write path fast while still guaranteeing correctness where it matters.