INSERT, UPDATE, DELETE, SELECT, Upserts, Timestamps, TTL, and Last-Write-Wins Cells

Learning outcomes

AtlasMart has an inventory row for a product/location pair. Engineers coming from SQL expect a second INSERT to fail on a duplicate key and expect “the last request to arrive” to be the winner. Cassandra does neither. This lesson makes upsert behavior, per-cell mutation timestamps, TTL, and delete semantics observable.

1. Start from the row identity, then reason about cells

A Cassandra row is identified by its complete primary key. An ordinary INSERT does not first check whether that row exists. If the key is new, values create cells; if the key already exists, the supplied cells become new mutations for that row. That is an upsert. Conditional IF NOT EXISTS exists, but it invokes Paxos/lightweight-transaction machinery and belongs to a different performance/correctness category taught later.

Cassandra assigns a mutation timestamp to each written cell. Without a client-supplied timestamp, the coordinator supplies the current time in microseconds at statement execution. Conflicting versions resolve by timestamp: the greater timestamp wins. This is why clock discipline matters and why “request B arrived after request A” does not necessarily mean B wins.

2. Reuse the chapter schema and seed one deterministic row

# Disposable Chapter 05 cluster: three nodes, three racks, one DC, 16 vnodes/node# If these objects already exist from Chapters 01–04, reuse them instead of recreating them.docker network create atlasmart-cassandradocker volume create atlasmart-cass-1-datadocker volume create atlasmart-cass-2-datadocker volume create atlasmart-cass-3-datadocker run -d --name atlasmart-cass-1 --hostname atlasmart-cass-1 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-1-data:/var/lib/cassandra cassandra:5.0.9# Wait for node 1 to accept CQL, then start the two peers.docker run -d --name atlasmart-cass-2 --hostname atlasmart-cass-2 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_SEEDS=atlasmart-cass-1   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack2   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-2-data:/var/lib/cassandra cassandra:5.0.9docker run -d --name atlasmart-cass-3 --hostname atlasmart-cass-3 --network atlasmart-cassandra   -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_SEEDS=atlasmart-cass-1   -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack3   -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch   -e CASSANDRA_NUM_TOKENS=16   -v atlasmart-cass-3-data:/var/lib/cassandra cassandra:5.0.9docker exec atlasmart-cass-1 nodetool statusdocker exec atlasmart-cass-1 nodetool versiondocker exec atlasmart-cass-1 java -version

CREATE KEYSPACE IF NOT EXISTS atlasmart_cqlWITH replication = {'class': 'NetworkTopologyStrategy', 'dc1': 3}AND durable_writes = true;CONSISTENCY QUORUM;CREATE TABLE IF NOT EXISTS atlasmart_cql.products_by_id (    product_id text PRIMARY KEY,    name text,    category text,    price_cents bigint,    status text,    description text,    updated_at timestamp);CREATE TABLE IF NOT EXISTS atlasmart_cql.products_by_category (    category text,    bucket tinyint,    product_id text,    name text,    status text,    price_cents bigint,    PRIMARY KEY ((category, bucket), product_id));CREATE TABLE IF NOT EXISTS atlasmart_cql.inventory_by_product (    product_id text,    location_id text,    quantity int,    status text,    promo_note text,    PRIMARY KEY ((product_id), location_id));

INSERT INTO atlasmart_cql.inventory_by_product(product_id, location_id, quantity, status)VALUES ('sku-100', 'wh-tehran', 8, 'available');SELECT product_id, location_id, quantity, status,       WRITETIME(quantity), WRITETIME(status)FROM atlasmart_cql.inventory_by_productWHERE product_id='sku-100';-- Same complete primary key: this is an upsert, not a duplicate-key failure.INSERT INTO atlasmart_cql.inventory_by_product(product_id, location_id, quantity)VALUES ('sku-100', 'wh-tehran', 11);SELECT product_id, location_id, quantity, status,       WRITETIME(quantity), WRITETIME(status)FROM atlasmart_cql.inventory_by_productWHERE product_id='sku-100';

The second INSERT updates quantity and leaves status intact because status was not supplied. The two cells can therefore show different write timestamps. Record the exact microsecond values; do not copy a sample timestamp from a tutorial into an application.

3. Prove last-write-wins rather than describing it vaguely

The safest classroom proof uses a brand-new row and artificial timestamps that are only meaningful inside the disposable table. The third mutation below is issued last but carries an older timestamp, so it must not replace the value written with timestamp 3,000,000.

INSERT INTO atlasmart_cql.inventory_by_product(product_id, location_id, quantity)VALUES ('sku-clock', 'lab', 10)USING TIMESTAMP 1000000;UPDATE atlasmart_cql.inventory_by_product USING TIMESTAMP 3000000SET quantity=30WHERE product_id='sku-clock' AND location_id='lab';-- Deliberately sent later with an older timestamp.UPDATE atlasmart_cql.inventory_by_product USING TIMESTAMP 2000000SET quantity=20WHERE product_id='sku-clock' AND location_id='lab';SELECT quantity, WRITETIME(quantity)FROM atlasmart_cql.inventory_by_productWHERE product_id='sku-clock' AND location_id='lab';

The expected winning pair is quantity 30 with write time 3000000. That proves timestamp ordering, not wall-clock arrival ordering. Never reuse these toy timestamps for real data: once an accidentally far-future timestamp exists, normal coordinator timestamps may lose to it for a long time.

4. TTL is cell lifecycle metadata, not a cron scheduler

Time To Live (TTL) is expressed in seconds. An INSERT or UPDATE can attach TTL to written non-primary-key values. TTL(column) reports the remaining TTL for a selected value, while WRITETIME(column) reports its mutation timestamp. Rewriting that column can replace/reset its TTL according to the new mutation.

UPDATE atlasmart_cql.inventory_by_product USING TTL 120SET promo_note='flash-sale'WHERE product_id='sku-100' AND location_id='wh-tehran';SELECT promo_note, TTL(promo_note), WRITETIME(promo_note)FROM atlasmart_cql.inventory_by_productWHERE product_id='sku-100' AND location_id='wh-tehran';-- Re-run after some time. TTL should decrease; exact remaining seconds depend on timing.SELECT promo_note, TTL(promo_note)FROM atlasmart_cql.inventory_by_productWHERE product_id='sku-100' AND location_id='wh-tehran';

The exact TTL value is intentionally not hard-coded as an expected result because time passes between commands. A null TTL means the current value has no expiration. Later storage/tombstone chapters explain how expiration is represented and eventually purged.

5. DELETE participates in timestamp reconciliation

A delete is not a special escape from Cassandra versioning. It is a mutation that must prevent an older replica value from reappearing during reconciliation. Cassandra therefore records deletion information (a tombstone) until it is safe to purge under the table’s repair/garbage-collection assumptions.

DELETE promo_noteFROM atlasmart_cql.inventory_by_productWHERE product_id='sku-100' AND location_id='wh-tehran';SELECT * FROM atlasmart_cql.inventory_by_productWHERE product_id='sku-100';DELETE FROM atlasmart_cql.inventory_by_productWHERE product_id='sku-clock' AND location_id='lab';SELECT * FROM atlasmart_cql.inventory_by_productWHERE product_id='sku-clock';

The read result proves logical visibility, not immediate byte removal from every SSTable. Compaction, repair, tombstones and gc_grace_seconds are intentionally deferred to later chapters so this lesson does not pretend a SELECT is a storage-forensics tool.

6. Wrong approaches and production judgment

Wrong approach 1: generate timestamps independently on loosely synchronized application hosts and assume retry order is enough. Repair: default to coordinator timestamps unless application semantics genuinely require explicit timestamps, then define one monotonic/conflict-safe policy and test skew.

Wrong approach 2: use IF NOT EXISTS merely to imitate SQL duplicate-key behavior. Repair: decide whether uniqueness is a real invariant worth lightweight-transaction cost; otherwise embrace idempotent upsert semantics.

Wrong approach 3: attach TTL to business facts without planning for expiry/reconciliation. Repair: define retention, late writes, repair interval, tombstone load and query behavior as one lifecycle design.

Verification checklist

• A second ordinary INSERT on the same primary key succeeds and changes only supplied cells.
• WRITETIME shows cell-level timestamps on legal scalar selections.
• The deliberately older timestamp loses even though its request is issued later.
• TTL(promo_note) is observable and decreases over time.
• You can state why DELETE/expiry are not immediate physical erasure.

Summary and next bridge

CQL mutations are timestamped cell operations, not SQL row replacements with arrival-order conflict resolution. Lesson 3 turns to reads: why Cassandra accepts some WHERE clauses, rejects others, and makes you explicitly opt into unpredictable filtering.

# Chapter-only reset or deliberate preservation# Keep atlasmart_cql if you are continuing directly to the next lesson.# Full reset (deletes only the dedicated course containers/volumes/network)docker rm -f atlasmart-cass-1 atlasmart-cass-2 atlasmart-cass-3docker volume rm atlasmart-cass-1-data atlasmart-cass-2-data atlasmart-cass-3-datadocker network rm atlasmart-cassandra

Authoritative references

• Apache Cassandra downloads — Current Cassandra and ASF Java-driver release baseline.
• CQL data definition — Official keyspace/table DDL and schema options.
• CQL data manipulation — Official INSERT/UPDATE/DELETE/SELECT, timestamps, TTL, WRITETIME and filtering semantics.
• cqlsh documentation — Official shell commands, tracing, paging, SOURCE/CAPTURE/COPY behavior and compatibility boundary.
• Native protocol — PREPARE/EXECUTE protocol semantics and version framing.
• Apache Cassandra Java Driver prepared statements — Prepared/bound statement behavior, caching and routing metadata.