Can't delete a row previously created with an upsert in Cassandra using Java

Question

the TL;DR is that I am not able to delete a row previously created with an upsert using Java.

Basically I have a table like this:

CREATE TABLE transactions (
key text PRIMARY KEY,
created_at timestamp
);

Then I execute:

String sql = "update transactions set created_at = toTimestamp(now()) where key = 'test' if created_at = null"; 
session.execute(sql)

As expected the row is created:

cqlsh:thingleme> SELECT * FROM  transactions ;

 key  | created_at
------+---------------------------------
 test | 2018-01-30 16:35:16.663000+0000

But (this is what is making me crazy) if I execute:

sql = "delete from transactions where key = 'test'"; 
ResultSet resultSet = session.execute(sql);

Nothing happens. I mean: no exception is thrown and the row is still there!

Some other weird stuff:

• if I replace the upsert with a plain insert, then the delete works
• if I directly run the sql code (update and delete) by using cqlsh, it works
• If I run this code against an EmbeddedCassandraService, it works (this is very bad, because my integration tests are just green!)

My environment:

• cassandra: 3.11.1
• datastax java driver: 3.4.0
• docker image: cassandra:3.11.1

Any idea/suggestion on how to tackle this problem is really appreciated ;-)

Answer 1

I think the issue you are encountering might be explained by the mixing of lightweight transactions (LWTs) (update transactions set created_at = toTimestamp(now()) where key = 'test' if created_at = null) and non-LWTs (delete from transactions where key = 'test').

Cassandra uses timestamps to determine which mutations (deletes, updates) are the most recently applied. When using LWTs, the timestamp assignment is different then when not using LWTs:

Lightweight transactions will block other lightweight transactions from occurring, but will not stop normal read and write operations from occurring. Lightweight transactions use a timestamping mechanism different than for normal operations and mixing LWTs and normal operations can result in errors. If lightweight transactions are used to write to a row within a partition, only lightweight transactions for both read and write operations should be used.

Source: How do I accomplish lightweight transactions with linearizable consistency?

Further complicating things is that by default the java driver uses client timestamps, meaning the write timestamp is determined by the client rather than the coordinating cassandra node. However, when you use LWTs, the client timestamp is bypassed. In your case, unless you disable client timestamps, your non-LWT queries are using client timestamps, where your LWT queries are using a timestamp assigned by the paxos logic in cassandra. In any case, even if the driver wasn't assigning client timestamps this still might be a problem because the timestamp assignment logic is different on the C* side for LWT and non-LWT as well.

To fix this, you could alter your delete statement to include IF EXISTS, i.e.:

delete from transactions where key = 'test' if exists

Similar issue from the java driver mailing list