Lightweight Transactions: IF NOT EXISTS

You learned that every Cassandra write is an upsert: it never checks if the row exists, and the last write wins. Usually that is what you want. But think of two people who pick the same username at the same time. Both writes "succeed", and the second one silently replaces the first.

For these cases, Cassandra has lightweight transactions (LWTs). An LWT is a write with a condition. Cassandra checks the condition first and only writes if it is true. The check and the write happen as one step, so two lightweight transactions can't both take the same username.

Here is a table of users, with one user already in it:

CREATE TABLE users (
  username text PRIMARY KEY,
  email text
);
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');

Syntax

INSERT INTO table_name (columns)
VALUES (values)
IF NOT EXISTS;

Example: register a new username

INSERT INTO users (username, email)
VALUES ('sam', '[email protected]')
IF NOT EXISTS;

Output:

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

Cassandra answers with a special column called [applied]. True means the condition held and the row was written.

Now someone else tries to take the same username:

INSERT INTO users (username, email)
VALUES ('sam', '[email protected]')
IF NOT EXISTS;

Output:

 [applied] | username | email
-----------+----------+-----------------
     False |      sam | [email protected]

False means nothing was written. Cassandra also shows the row that already exists. Your app reads [applied] and can tell the user "this username is taken".

Example: update only if a value matches

An UPDATE can check the current value of a column with IF:

UPDATE users SET email = '[email protected]'
WHERE username = 'sam'
IF email = '[email protected]';

Output:

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

Sam's email was [email protected], so the update ran. Run the same update again, and this time it is not applied, because the email has changed:

UPDATE users SET email = '[email protected]'
WHERE username = 'sam'
IF email = '[email protected]';

Output:

 [applied] | email
-----------+--------------
     False | [email protected]

There is also DELETE ... IF EXISTS, which deletes a row only if it is there.

Use them only when needed

A normal write is fast. An LWT is slower, because the nodes that hold the row must first agree that the condition is true. They do this with a protocol called Paxos, which needs several extra round trips between the nodes.

So don't add IF NOT EXISTS to every write "just to be safe". Use it only when two writes at the same time would cause a real problem, like two users with the same username. For all other writes, the normal upsert is the right choice.

In the exercise, register the username dave, but only if nobody has taken it yet.