Lightweight Transactions (LWT)

Lightweight Transactions (LWT) provide conditional updates using the Paxos consensus protocol, enabling compare-and-set operations with linearizable consistency.

What are Lightweight Transactions?

LWT allows you to make conditional modifications to data using IF clauses:

INSERT IF NOT EXISTS: Insert only if the row doesn’t exist
UPDATE IF: Update only if conditions are met
DELETE IF: Delete only if conditions are met

These operations are atomic and guarantee that only one client’s conditional update succeeds when multiple clients compete.

-- Insert only if not exists
INSERT INTO users (id, name) VALUES (1, 'Alice') IF NOT EXISTS;

-- Update only if current value matches
UPDATE users SET name = 'Bob' WHERE id = 1 IF name = 'Alice';

-- Delete only if condition is met  
DELETE FROM users WHERE id = 1 IF name = 'Alice';

Serial Consistency

LWT operations require setting a serial consistency level that determines how many replicas must participate in the Paxos round:

use scylla::statement::SerialConsistency;

// SERIAL - requires quorum across all datacenters
let serial = SerialConsistency::Serial;

// LOCAL_SERIAL - requires quorum in local datacenter only (recommended)
let local_serial = SerialConsistency::LocalSerial;

LocalSerial is recommended for most use cases as it provides lower latency while still ensuring linearizability within a datacenter.

Using LWT with Unprepared Statements

INSERT IF NOT EXISTS

use scylla::statement::unprepared::Statement;
use scylla::statement::SerialConsistency;

let mut statement = Statement::new(
    "INSERT INTO ks.users (id, name, email) VALUES (?, ?, ?) IF NOT EXISTS"
);
statement.set_serial_consistency(Some(SerialConsistency::LocalSerial));

let result = session
    .query_unpaged(&statement, (1_i32, "Alice", "[email protected]"))
    .await?;

// Check if the insert was applied
let rows_result = result.into_rows_result()?;
let (applied,): (bool,) = rows_result.first_row()?;

if applied {
    println!("User created successfully");
} else {
    println!("User already exists");
}

UPDATE IF

let mut statement = Statement::new(
    "UPDATE ks.accounts SET balance = balance - ? WHERE id = ? IF balance >= ?"
);
statement.set_serial_consistency(Some(SerialConsistency::LocalSerial));

let amount = 100;
let account_id = 1;

let result = session
    .query_unpaged(&statement, (amount, account_id, amount))
    .await?;

let rows_result = result.into_rows_result()?;
let (applied,): (bool,) = rows_result.first_row()?;

if applied {
    println!("Withdrawal successful");
} else {
    println!("Insufficient balance");
}

DELETE IF

let mut statement = Statement::new(
    "DELETE FROM ks.users WHERE id = ? IF name = ?"
);
statement.set_serial_consistency(Some(SerialConsistency::LocalSerial));

let result = session
    .query_unpaged(&statement, (1_i32, "Alice"))
    .await?;

let rows_result = result.into_rows_result()?;
let (applied,): (bool,) = rows_result.first_row()?;

if applied {
    println!("User deleted");
} else {
    println!("User not found or name mismatch");
}

Using LWT with Prepared Statements

Prepared statements offer better performance for LWT operations:

use scylla::statement::prepared::PreparedStatement;
use scylla::statement::SerialConsistency;

// Prepare the LWT statement
let mut prepared = session
    .prepare("INSERT INTO ks.users (id, name, email) VALUES (?, ?, ?) IF NOT EXISTS")
    .await?;

// Set serial consistency
prepared.set_serial_consistency(Some(SerialConsistency::LocalSerial));

// Execute multiple times with different values
for i in 1..=10 {
    let result = session
        .execute_unpaged(
            &prepared,
            (i, format!("user_{}", i), format!("user{}@example.com", i)),
        )
        .await?;

    let rows_result = result.into_rows_result()?;
    let (applied,): (bool,) = rows_result.first_row()?;
    
    if applied {
        println!("Created user {}", i);
    } else {
        println!("User {} already exists", i);
    }
}

ScyllaDB-Specific LWT Optimization

ScyllaDB provides optimized routing for confirmed LWT queries:

let prepared = session
    .prepare("UPDATE ks.accounts SET balance = ? WHERE id = ? IF balance >= ?")
    .await?;

// Check if this is a confirmed LWT (ScyllaDB-specific)
if prepared.is_confirmed_lwt() {
    println!("This LWT will use optimized routing");
}

ScyllaDB can detect LWT statements and apply optimizations like deterministic replica ordering for better performance. This feature is not available on Cassandra.

Processing LWT Results

LWT operations always return a result set with at least one row:

Simple Applied Check

let result = session
    .execute_unpaged(&lwt_prepared, values)
    .await?
    .into_rows_result()?;

let (applied,): (bool,) = result.first_row()?;

if applied {
    println!("Operation succeeded");
} else {
    println!("Operation failed due to condition");
}

Getting Current Values on Failure

When an LWT fails, the result includes the current column values:

let result = session
    .execute_unpaged(
        &prepared,
        (new_value, key, expected_old_value),
    )
    .await?
    .into_rows_result()?;

// Result has: [applied] + [columns used in IF condition]
let (applied, current_value): (bool, i32) = result.first_row()?;

if applied {
    println!("Update succeeded");
} else {
    println!("Update failed. Current value is: {}", current_value);
}

Full Row on IF NOT EXISTS Failure

#[derive(Debug, scylla::DeserializeRow)]
struct LwtResult {
    applied: bool,
    id: Option<i32>,
    name: Option<String>,
    email: Option<String>,
}

let result = session
    .execute_unpaged(&insert_if_not_exists_prepared, (1, "Alice", "[email protected]"))
    .await?
    .into_rows_result()?;

let lwt_result: LwtResult = result.first_row()?;

if lwt_result.applied {
    println!("User created");
} else {
    println!(
        "User already exists: {:?}, {:?}, {:?}",
        lwt_result.id, lwt_result.name, lwt_result.email
    );
}

Consistency Levels

LWT operations use two consistency levels:

Serial Consistency: For the Paxos round (use set_serial_consistency())
Regular Consistency: For the actual write/read after consensus (use set_consistency())

use scylla::statement::Consistency;
use scylla::statement::SerialConsistency;

let mut prepared = session
    .prepare("UPDATE ks.table SET value = ? WHERE id = ? IF value = ?")
    .await?;

// Paxos round consistency
prepared.set_serial_consistency(Some(SerialConsistency::LocalSerial));

// Final write/read consistency
prepared.set_consistency(Consistency::Quorum);

Using LWT in Batches

You can include LWT statements in batches, but all statements must be conditional:

use scylla::statement::batch::{Batch, BatchType};
use scylla::statement::SerialConsistency;

let mut batch = Batch::new(BatchType::Logged);
batch.set_serial_consistency(Some(SerialConsistency::LocalSerial));

// All statements must be LWT
batch.append_statement(
    "INSERT INTO ks.users (id, name) VALUES (?, ?) IF NOT EXISTS"
);
batch.append_statement(
    "UPDATE ks.accounts SET balance = balance - ? WHERE id = ? IF balance >= ?"
);

let batch_values = (
    (1_i32, "Alice"),
    (100, 1_i32, 100),
);

let result = session.batch(&batch, batch_values).await?;

Batch LWT operations are expensive. They require multiple Paxos rounds and should be used sparingly. Consider alternative designs if you need frequent batch LWT operations.

Complete Example

use scylla::client::session::{Session, SessionBuilder};
use scylla::statement::prepared::PreparedStatement;
use scylla::statement::SerialConsistency;
use scylla::DeserializeRow;

#[derive(Debug, DeserializeRow)]
struct Account {
    id: i32,
    balance: i32,
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let session: Session = SessionBuilder::new()
        .known_node("127.0.0.1:9042")
        .build()
        .await?;

    session
        .query_unpaged(
            "CREATE KEYSPACE IF NOT EXISTS examples_ks \
             WITH REPLICATION = {'class': 'NetworkTopologyStrategy', 'replication_factor': 1}",
            &[],
        )
        .await?;

    session
        .query_unpaged(
            "CREATE TABLE IF NOT EXISTS examples_ks.accounts \
             (id int PRIMARY KEY, balance int)",
            &[],
        )
        .await?;

    // Prepare LWT statements
    let mut create_account = session
        .prepare("INSERT INTO examples_ks.accounts (id, balance) VALUES (?, ?) IF NOT EXISTS")
        .await?;
    create_account.set_serial_consistency(Some(SerialConsistency::LocalSerial));

    let mut withdraw = session
        .prepare(
            "UPDATE examples_ks.accounts SET balance = balance - ? \
             WHERE id = ? IF balance >= ?"
        )
        .await?;
    withdraw.set_serial_consistency(Some(SerialConsistency::LocalSerial));

    // Create account with initial balance
    let result = session
        .execute_unpaged(&create_account, (1_i32, 1000_i32))
        .await?
        .into_rows_result()?;

    let (applied,): (bool,) = result.first_row()?;
    println!("Account created: {}", applied);

    // Try to withdraw funds
    let amount = 100;
    let result = session
        .execute_unpaged(&withdraw, (amount, 1_i32, amount))
        .await?
        .into_rows_result()?;

    let (applied, current_balance): (bool, Option<i32>) = result.first_row()?;
    
    if applied {
        println!("Withdrawal of {} successful", amount);
    } else {
        println!(
            "Withdrawal failed. Current balance: {:?}",
            current_balance
        );
    }

    // Try to withdraw more than balance
    let large_amount = 2000;
    let result = session
        .execute_unpaged(&withdraw, (large_amount, 1_i32, large_amount))
        .await?
        .into_rows_result()?;

    let (applied, current_balance): (bool, Option<i32>) = result.first_row()?;
    
    if applied {
        println!("Large withdrawal successful");
    } else {
        println!(
            "Large withdrawal failed. Current balance: {:?}",
            current_balance
        );
    }

    Ok(())
}

Performance Considerations

Understand the Cost

LWT operations require 4 round trips (Paxos protocol) compared to 1 for regular writes. Use them only when necessary.

Use LocalSerial

Prefer SerialConsistency::LocalSerial over Serial for better latency in multi-datacenter deployments.

Minimize Contentious Updates

High contention on the same partition key can severely impact LWT performance. Design your schema to minimize conflicts.

Prepare Statements

Always use prepared statements for LWT operations to avoid additional parsing overhead.

Common Use Cases

Unique constraints: INSERT ... IF NOT EXISTS for enforcing uniqueness
Optimistic locking: UPDATE ... IF version = ? for version-based concurrency control
Account balances: UPDATE ... IF balance >= ? for safe balance deductions
Distributed locks: UPDATE ... IF locked = false for lock acquisition
State machines: UPDATE ... IF state = ? for controlled state transitions

Getting Started

Core Concepts

Executing Queries

Data Types

Advanced Features

Migration Guides

What are Lightweight Transactions?

Serial Consistency

Using LWT with Unprepared Statements

INSERT IF NOT EXISTS

UPDATE IF

DELETE IF

Using LWT with Prepared Statements

ScyllaDB-Specific LWT Optimization

Processing LWT Results

Simple Applied Check

Getting Current Values on Failure

Full Row on IF NOT EXISTS Failure

Consistency Levels

Using LWT in Batches

Complete Example

Performance Considerations

Common Use Cases

See Also

Build docs developers (and LLMs) love

Getting Started

Core Concepts

Executing Queries

Data Types

Advanced Features

Migration Guides

​What are Lightweight Transactions?

​Serial Consistency

​Using LWT with Unprepared Statements

​INSERT IF NOT EXISTS

​UPDATE IF

​DELETE IF

​Using LWT with Prepared Statements

​ScyllaDB-Specific LWT Optimization

​Processing LWT Results

​Simple Applied Check

​Getting Current Values on Failure

​Full Row on IF NOT EXISTS Failure

​Consistency Levels

​Using LWT in Batches

​Complete Example

​Performance Considerations

​Common Use Cases

​See Also

Build docs developers (and LLMs) love

What are Lightweight Transactions?

Serial Consistency

Using LWT with Unprepared Statements

INSERT IF NOT EXISTS

UPDATE IF

DELETE IF

Using LWT with Prepared Statements

ScyllaDB-Specific LWT Optimization

Processing LWT Results

Simple Applied Check

Getting Current Values on Failure

Full Row on IF NOT EXISTS Failure

Consistency Levels

Using LWT in Batches

Complete Example

Performance Considerations

Common Use Cases

See Also