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)