Lightweight transaction

A lightweight transaction (LWT) conditionally changes a ScyllaDB row according to its current state. Instead of reading a row, deciding in application code, and writing it back while another request may intervene, put the decision in the write itself. ScyllaDB evaluates the condition and applies the update only when it is true. section 6.5 section 6.4.2

LWTs are useful when an ordinary last-write-wins update could create an invalid result. For example, one user might read an author, another might delete that author, and the first might then update the bio. A normal update can recreate a partial row containing only the ID and bio. A conditional update can require that the row, or a defining value on it, still exists before changing it. section 6.5 section 6.5.2

An IF clause makes a CQL statement an LWT. Its condition can be:

• IF EXISTS, to apply a mutation only when the target row is present. section 6.5.1
• IF NOT EXISTS, to apply a mutation only when the target row is absent. section 6.5.1
• One or more column-value tests, such as IF name = 'Alice Author', to require current values to match. section 6.5.1

cqlsh:reviews> UPDATE authors
    SET bio = 'Alice loves writing about food and drink'
    WHERE id = eff9bd01-4a73-11ee-8863-c02b3f99d2b2
    IF name = 'Alice Author';

@ Row 1
-----------+--------------
 [applied] | True
 name      | Alice Author

This successful UPDATE shows the IF clause and the [applied] = True response returned when the current name matches. section 6.5.1

cqlsh:reviews> UPDATE authors
    SET bio = 'Alice loves writing about food and drink'
    WHERE id = eff9bd01-4a73-11ee-8863-c02b3f99d2b2
    IF name = 'Bob Bookwriter';

@ Row 1
-----------+--------------
 [applied] | False
 name      | Alice Author

This failed UPDATE shows that a mismatched condition leaves the mutation unapplied and returns the actual name value. section 6.5.1

“Lightweight” describes the query interface, not its operational cost. Ordinary ScyllaDB writes fit eventual consistency and resolve conflicts with last-write-wins. An LWT instead needs a consistent view while checking and changing a row. ScyllaDB uses Paxos distributed consensus, requiring three node round-trips, and locks the rows being updated so concurrent requests wait during coordination. Use an LWT when that guarantee is needed, not as the default form of every write. section 6.5.2 section 6.6

An IF clause inside a BATCH creates a conditional batch. Every condition in every statement must be true for the batch to succeed. This can coordinate state-dependent changes across multiple rows, but it adds further performance cost and should be monitored carefully. section 6.5.2