Cassandra atomicity with delete partition key which belong to different tables

Question

I have a use case where i need to perform delete from three tables in cassandra . The partitioning key is same for all the tables example :-

Db_name/table1/1111 Db_name/table2/1111 Db_name/table3/1111

Which operation i shall use Put/batch in order to maintain the atomicity. I want either all keys to be delete in one go or none is deleted

I need to delete huge no of such keys ..i mean lets say there are 10k such keys which i want to delete from all three tables . It would be something like

Loop over all the keys ..then delete key one by one from three table in one go

Answer 1

You need to use CQL batches to group updates to denormalised tables so they are executed as an atomic operation.

In cqlsh, the batched deletes would look like:

BEGIN BATCH
   DELETE FROM table1 WHERE pk = 1111;
   DELETE FROM table2 WHERE pk = 1111;
   DELETE FROM table3 WHERE pk = 1111;
APPLY BATCH;

You'll need one batch statement for each partition key you are deleting. It's important that you don't group together unrelated partitions in a single batch since CQL batches are NOT an optimisation like it is in RDBMS.

I've explained this in a bit more detail in this article -- How to keep data in denormalized tables in sync. Cheers!