Lightweight Transactions (LWT) in Cassandra

What Are Lightweight Transactions?

Cassandra is an eventually consistent database, meaning concurrent writes to the same row can lead to last-write-wins conflicts. Lightweight Transactions (LWT) provide linearizable consistency for individual operations using the Paxos consensus protocol. They enable compare-and-set (CAS) semantics, allowing you to conditionally insert, update, or delete data only if certain conditions are met.

Conditional INSERT with IF NOT EXISTS

The most common LWT use case is preventing duplicate inserts. The operation checks if a row exists before inserting.

-- Register a username only if not taken
INSERT INTO user_accounts (username, email, created_at)
VALUES ('alice_dev', 'alice@example.com', toTimestamp(now()))
IF NOT EXISTS;

-- Result when successful:
-- [applied] | username  | email              | created_at
-- True      | null      | null               | null

-- Result when username already exists:
-- [applied] | username  | email              | created_at
-- False     | alice_dev | alice@example.com  | 2025-01-15...

Conditional UPDATE with IF

Use IF conditions on UPDATE to implement optimistic locking. The update only applies if the current values match your expectations.

-- Update inventory only if enough stock exists
UPDATE products
SET stock = 8
WHERE product_id = 'prod-001'
IF stock = 10;

-- Transfer balance: debit only if sufficient funds
UPDATE accounts
SET balance = 450.00
WHERE account_id = 'acc-123'
IF balance = 500.00;

-- Multiple conditions
UPDATE orders
SET status = 'shipped', shipped_at = toTimestamp(now())
WHERE order_id = 550e8400-e29b-41d4-a716-446655440000
IF status = 'processing' AND assigned_to = 'warehouse-1';

Conditional DELETE with IF EXISTS

Use IF EXISTS to delete only if the row is present, or IF with specific conditions to guard against concurrent modifications.

-- Delete only if the row exists
DELETE FROM sessions
WHERE session_id = 550e8400-e29b-41d4-a716-446655440000
IF EXISTS;

-- Delete with a condition
DELETE FROM locks
WHERE resource_id = 'report-gen'
IF owner = 'worker-5';

How Paxos Works Under the Hood

LWT uses a four-round Paxos protocol: Prepare, Promise, Propose, and Commit. This means each LWT requires multiple network round trips between replicas, making it significantly slower than regular writes (roughly 4x latency). The coordinator proposes the operation, replicas vote, and only if a quorum agrees does the write proceed.

Because of this overhead, LWT throughput is substantially lower than regular writes. On a typical cluster, expect LWT operations to take 10-30ms compared to 1-5ms for normal writes.

When to Use LWT

• Unique constraints: Enforcing uniqueness (usernames, email registration).
• Optimistic locking: Preventing lost updates on shared resources.
• Distributed locks: Implementing simple lock/unlock patterns.
• Idempotent processing: Ensuring an event is processed exactly once.

When to Avoid LWT

• High-throughput paths: Do not use LWT for every write in a hot path.
• Counters: Use Cassandra's native counter type instead of LWT for increments.
• When eventual consistency is acceptable: Most use cases do not need linearizable writes.

Handling LWT Results

-- Always check the [applied] column in your application
-- Pseudocode:
-- result = session.execute(
--   "UPDATE products SET stock = 8
--    WHERE product_id = 'prod-001' IF stock = 10"
-- )
-- if result[0].applied:
--     print("Update succeeded")
-- else:
--     current_stock = result[0].stock
--     print(f"Conflict: current stock is {current_stock}")
--     # Retry with the current value

Key Takeaways

• LWT provides linearizable consistency via Paxos, enabling compare-and-set operations.
• They are 4x slower than regular writes -- use them sparingly and only when needed.
• Always check the [applied] column in your application to handle conflicts.
• Do not mix LWT and non-LWT writes on the same partition; it can lead to unexpected behavior.