Scylla Lightweight Transactions
Use Scylla lightweight transactions for conditional writes with IF NOT EXISTS and applied result checks.
Scylla lightweight transactions are not SQL transactions in the MySQL or PostgreSQL sense.
They do not create a multi-statement transaction block and they do not behave like BEGIN, COMMIT, or ROLLBACK.
In Scylla, lightweight transactions are conditional CQL operations.
They are commonly used with conditions such as:
IF NOT EXISTSor:
IF column = valueThe operation returns whether the condition was applied.
Basic Idea
A lightweight transaction checks a condition and applies the write only when the condition succeeds.
Example:
INSERT INTO users_by_email (email, id, name, active, created_at)
VALUES (?, ?, ?, ?, ?)
IF NOT EXISTSIf the row does not exist, Scylla applies the insert.
If the row already exists, Scylla does not apply the insert.
The result contains a special column:
[applied]Use this value to check whether the write succeeded.
When to Use Lightweight Transactions
Use lightweight transactions when you need conditional writes.
Typical use cases include:
- insert only if a row does not already exist
- reserve a unique email
- create a record only once
- update only if a value still matches an expected state
- prevent overwriting existing data accidentally
Do not use lightweight transactions as a general replacement for SQL transactions.
They are more expensive than regular writes and should be used only when the condition is needed.
Insert If Not Exists
This example inserts a user by email only if the email does not already exist.
const insertUserByEmailQuery = `
INSERT INTO users_by_email (email, id, name, active, created_at)
VALUES (?, ?, ?, ?, ?)
IF NOT EXISTS
`Execute the query using db.Maps.
rows, err := db.Maps(ctx, conn, insertUserByEmailQuery, email, id, name, active, createdAt)
if err != nil {
return false, err
}Then check the [applied] value.
if len(rows) == 0 {
return false, nil
}
applied, ok := rows[0]["[applied]"].(bool)
if !ok {
return false, nil
}
return applied, nilComplete Example
package main
import (
"context"
"time"
"github.com/gocql/gocql"
"github.com/netlifeguru/db"
)
const insertUserByEmailQuery = `
INSERT INTO users_by_email (email, id, name, active, created_at)
VALUES (?, ?, ?, ?, ?)
IF NOT EXISTS
`
func InsertUserIfEmailAvailable(
ctx context.Context,
conn db.Conn,
name string,
email string,
active bool,
) (bool, string, error) {
id := gocql.TimeUUID()
createdAt := time.Now().UTC()
rows, err := db.Maps(
ctx,
conn,
insertUserByEmailQuery,
email,
id,
name,
active,
createdAt,
)
if err != nil {
return false, "", err
}
if len(rows) == 0 {
return false, "", nil
}
applied, ok := rows[0]["[applied]"].(bool)
if !ok {
return false, "", nil
}
if !applied {
return false, "", nil
}
return true, id.String(), nil
}Usage Example
applied, id, err := InsertUserIfEmailAvailable(
ctx,
conn,
"Jane Doe",
"jane.doe@example.com",
true,
)
if err != nil {
return err
}
if !applied {
fmt.Println("email already exists")
return nil
}
fmt.Printf("created user id=%s\n", id)Map Result
Lightweight transaction results are easiest to inspect as maps.
For an IF NOT EXISTS insert, Scylla returns a row that includes:
[applied]Example applied result:
map[string]any{
"[applied]": true,
}Example not-applied result may include existing column values depending on the CQL statement and driver behavior.
map[string]any{
"[applied]": false,
"email": "jane.doe@example.com",
}Always check [applied] before treating the operation as successful.
Low-Level Variant
You can also use db.Raw and db.MapsQuery.
q, err := db.Raw(
insertUserByEmailQuery,
email,
id,
name,
active,
createdAt,
)
if err != nil {
return false, err
}
rows, err := db.MapsQuery(ctx, conn, q)
if err != nil {
return false, err
}Then inspect [applied] the same way.
applied, ok := rows[0]["[applied]"].(bool)
if !ok || !applied {
return false, nil
}Conditional Update
Lightweight transactions can also be used for conditional updates.
UPDATE users_by_id
SET active = ?
WHERE id = ?
IF active = ?Example:
const updateUserIfActiveQuery = `
UPDATE users_by_id
SET active = ?
WHERE id = ?
IF active = ?
`Execute it and check [applied].
rows, err := db.Maps(ctx, conn, updateUserIfActiveQuery, false, id, true)
if err != nil {
return false, err
}
applied, ok := rows[0]["[applied]"].(bool)
if !ok {
return false, nil
}
return applied, nilLightweight Transactions vs SQL Transactions
| Feature | MySQL/PostgreSQL SQL transactions | Scylla lightweight transactions |
|---|---|---|
| Multi-statement block | yes | no |
BEGIN / COMMIT / ROLLBACK | yes | no |
| Conditional write | possible with SQL logic | primary use case |
| Rollback behavior | callback error rolls back transaction | not applicable |
| Result check | error or commit/rollback | [applied] value |
| Common use | atomic multi-step operations | compare-and-set style writes |
Batches Are Different
Scylla batches are not the same as lightweight transactions.
A batch groups multiple CQL statements into one batch request.
A lightweight transaction checks a condition and applies a write only if the condition succeeds.
Use the Scylla Batches guide for batch examples.
Recommended Usage
Use lightweight transactions only when the condition is part of the data model.
Good examples:
INSERT ... IF NOT EXISTSUPDATE ... IF active = trueAvoid using lightweight transactions for every write.
For normal writes, use db.Insert, db.Update, or db.Delete.
Related Examples
Standalone examples are available in the examples repository: