Why are my lightweight transactions (LWTs) not working?

ScyllaDB
, ,
1

Hello all!

Lightweight transactions do not produce the expected changes in my ScyllaDB instance, and I’m at a loss as to why. The operations seem to complete successfully, as implied by the value of the [applied] column in the result set each of them returns. However, the contents of the singular row each such operation targets remains unchanged.

Here is the schema for the table which the queries target:

"CREATE TABLE IF NOT EXISTS my_keyspace.my_table (
    my_uuidfield uuid,
    my_timeuuidfield timeuuid,
    my_textfield text,
    my_intfield bigint,
    PRIMARY KEY (my_uuidfield, my_timeuuidfield)
)
WITH CLUSTERING ORDER BY (my_timeuuidfield DESC)
AND ID = '9577271A-BBC0-494F-9D8E-19693A32BD02';"

Here is the target row in the table (CSV):

230f6520-5b76-4bba-ab85-8840534cd7a8,358ba360-f7f3-1004-be52-65e70357ad91,,1

Here is the LWT query:

UPDATE my_keyspace.my_table
SET my_textfield = '{}', my_intfield = 2
WHERE
    my_uuidfield = 230f6520-5b76-4bba-ab85-8840534cd7a8
    AND my_timeuuidfield = 358ba360-f7f3-1004-be52-65e70357ad91
IF my_textfield = NULL AND my_intfield = 1;

And here is the result of the LWT query:

true,,1

Subsequent SELECT queries targeting the same row do not reflect the changes contained in the LWT.

What could the issue be? I consulted Gemini on the matter, and it gave me the following possibilities:

  • Known bugs in older versions of ScyllaDB: This does not seem to be the case, since I believe the version of the database I’m using (see end of post) is newer than those Gemini claimed the issues were fixed in (4.2, 4.3.rc1, and 5.2.0)
  • Keyspace could be configured to use tablets, which do not currently support LWTs: This does not seem to be the case given the output of a DESCRIBE KEYSPACE my_keyspace; command I ran, as well as an absence of entries in the system.tablets table for the keyspace and table. Even if the keyspace was configured in such a way, would the operation fail silently like this?
  • Read consistency level: This does not seem to be the case, since executing the statement after executing CONSISTENCY SERIAL; yields the same results.
  • Row cache: This does not seem to be the case, given that SELECT queries for the rows of interest with the BYPASS CACHE directive yield the same results.
  • Paxos table backlog: This *could* possibly be the issue. Right now there are 8 entries in the table, which have been there for quite some time (on the order of hours).

Installation details
#ScyllaDB version: 2025.1.2-0.20250422.502c62d91d48
#Cluster size: 1 node
os (RHEL/CentOS/Ubuntu/AWS AMI): MacOS (database is run through Docker Desktop)

2

The output actually shows that the LWT succeeded: ScyllaDB returns [applied] plus the pre-update values of the IF columns, so true,,1 means the condition matched.

Based on the information provided (a local setup in Docker), I suspect the write loses the last-write-wins comparison to a cell with a higher timestamp. LWT is exposed to this because its timestamp comes from the Paxos ballot (the server’s clock), while normal writes use the timestamp your driver sends, so in unstable environment the two can disagree.

Two likely scenarios:

  • The row was written by a client whose clock runs ahead of the container’s.
  • The app built a timestamp from a naive local datetime treated as UTC, putting the data ahead by exactly your UTC offset.

You can compare WRITETIME(my_intfield) against the server’s own clock. If the row is ahead, that’s it.