Stop Duplicate Writes in Cassandra with Lightweight Transactions

The sign-up race that creates duplicate accounts

You deploy a sign-up form. The application checks whether an email address already exists, then inserts the user only when it does not. Under load, two requests pass that check at almost the same time, and both insert the same email. You end up with duplicate accounts for one person.

This happens because a normal Cassandra INSERT is blind. It does not know whether the row already existed; it simply applies the mutation. Cassandra lightweight transactions (LWT) solve this by making the write conditional on the current state of the row.

The practical takeaway: use INSERT ... IF NOT EXISTS with SERIAL consistency when you need single-partition uniqueness. It prevents duplicates without an external lock, but it adds round trips and is not meant for high-throughput ingestion.

What a lightweight transaction actually does

An LWT coordinates a Paxos round among the replicas for a partition before applying the mutation. Paxos is a consensus protocol that lets replicas agree on whether a proposed write should proceed. The client sets CONSISTENCY SERIAL for the write, which tells Cassandra to run that agreement phase instead of a normal write path.

Cassandra returns an [applied] boolean column in the result. true means the condition was met and the row was written. false means another writer won the race or the row already existed, so nothing changed.

In multi-datacenter clusters, LOCAL_SERIAL limits the Paxos round to replicas in the local datacenter. Mixing SERIAL and LOCAL_SERIAL across clients can produce surprising read-your-writes behavior, so pick one convention per application.

Worked example: register an email only once

Assume a table in keyspace app that stores users by email. Run these statements in cqlsh against a running Cassandra 3.11 or 4.x cluster. You need INSERT permission on the table.

CREATE TABLE IF NOT EXISTS app.users_by_email (
  email text PRIMARY KEY,
  name text,
  created_at timestamp
);

INSERT INTO app.users_by_email (email, name, created_at)
VALUES ('', '', toTimestamp(now()))
IF NOT EXISTS
USING CONSISTENCY SERIAL;

Replace <user-email> and <user-name> with real values. The result set includes an [applied] column. If it is true, the insert succeeded. If it is false, the email already existed or a concurrent conditional insert won.

To see the Paxos phases, enable tracing in the same cqlsh session before running the insert:

TRACING ON;

Look for prepare, propose, and commit messages in the trace output. This is useful for debugging, not for production monitoring.

Risks: LWT typically requires two extra round trips compared with a normal write. Under heavy contention on the same partition key, proposals collide, aborts increase, and latency rises. A failed LWT still generates read repair and hinted handoff traffic, so watch timeout and abort rates.

Trade-offs: latency, contention, and scope

Factor	Normal QUORUM insert	LWT insert with SERIAL
Consistency	Eventual/tunable quorum	Linearizable conditional write
Round trips	One write phase	Paxos prepare/propose/commit
Best for	Event ingestion, high throughput	Low-contention uniqueness or state changes
Scope	Any partition	Single partition key only

LWT does not replace transactions across multiple partitions. It is scoped to one partition key, so you cannot atomically insert a user and a related profile row in different partitions. Do not use LWT for high-throughput event streams; normal QUORUM writes are significantly faster because they skip Paxos.

Verify before you rely on it

Check that your endpoint snitch and replication factor support a live quorum in the target datacenter, because SERIAL consistency requires replicas to participate in Paxos. Compare write latency for normal inserts versus LWT inserts on the same table using client metrics or cassandra-stress. If abort rates climb under load, reduce contention on the partition key or move the uniqueness check to the application layer with idempotent rewrites.

Use LWT for uniqueness, not for ingestion

Cassandra lightweight transactions are the right tool when correctness matters more than write speed and the conflict is on a single partition. Start with IF NOT EXISTS and SERIAL, inspect the [applied] column, and reserve normal QUORUM writes for the hot path.