2

I have two queries which I wish to execute as a batch to maintain atomicity. The first query is a delete query and the second query is an insert query, both operating on the same table. While it is important for me to ensure atomicity, it is also crucial to maintain the order of the queries (since I do not want to delete the recently inserted row if in case both queries are executed simultaneously and the ordering gets messed up). Thus, I am using the USING TIMESTAMP keyword to define a client-supplied timestamp to achieve the particular order.

My problem is that when I try executing the batch query containing USING TIMESTAMP to provide an order of execution, some of my tests fail on it while other pass. On the other hand, when I completely remove the batch statement and try to execute the two queries sequentially, they all pass. Hence, I believe that strict order of query execution is not being maintained even after using the TIMESTAMP value.

There are two ways I tried to execute my query in Java.

WAY #1

final Session cassandraSession = CassandraSessionManager.getSession();
Batch batch = QueryBuilder.batch();

Delete.Where delete = QueryBuilder.delete().from(CassandraMetadata.KeySpace,CassandraMetadata.Tables.AUTHOPDATA.toString())
                     .using(timestamp(System.nanoTime()))
                     .where(QueryBuilder.eq(CassandraMetadata.AuthOpDataColumns.ECID.toString(), ecId));
batch.add(delete);

Insert insertAuthOp = QueryBuilder.insertInto(CassandraMetadata.KeySpace, CassandraMetadata.Tables.AUTHOPDATA.toString())
                    .using(timestamp(System.nanoTime()))
                    .value(CassandraMetadata.AuthOpDataColumns.ECID.toString(), ecId)
                    .value(CassandraMetadata.AuthOpDataColumns.OPERATION.toString(), operation)
                    .value(CassandraMetadata.AuthOpDataColumns.STATUS.toString(), status);

batch.add(insertAuthOp);
batch.setConsistencyLevel(DbProps.CassandraWriteConsistencyLevel.getValue());
cassandraSession.execute(batch);

WAY #2

final Session cassandraSession = CassandraSessionManager.getSession();
Batch batch = QueryBuilder.batch();

Delete.Where delete = QueryBuilder.delete().from(CassandraMetadata.KeySpace,CassandraMetadata.Tables.AUTHOPDATA.toString())
                     .using(timestamp(1L))
                     .where(QueryBuilder.eq(CassandraMetadata.AuthOpDataColumns.ECID.toString(), ecId));
batch.add(delete);

Insert insertAuthOp = QueryBuilder.insertInto(CassandraMetadata.KeySpace, CassandraMetadata.Tables.AUTHOPDATA.toString())
                    .using(timestamp(2L))
                    .value(CassandraMetadata.AuthOpDataColumns.ECID.toString(), ecId)
                    .value(CassandraMetadata.AuthOpDataColumns.OPERATION.toString(), operation)
                    .value(CassandraMetadata.AuthOpDataColumns.STATUS.toString(), status);

batch.add(insertAuthOp);
batch.setConsistencyLevel(DbProps.CassandraWriteConsistencyLevel.getValue());
cassandraSession.execute(batch);

The output is same for both the executions. The same tests fail when I execute both the queries because they do not find the expected data in the table (while other tests using the same method pass). I wanted to know if there is a way to implement strict sequentiality in executing batch queries, and if this is the only method to ensure that, is there something I might be missing in my execution.

scottstots
  • 155
  • 2
  • 17
  • As I understand, you want to delete than insert a row with the same primary key. If my understanding is good, you don't need to delete the row before insert it because Cassandra manage insert as upsert (insert or update if the primary key already exists). – Fabien MIFSUD Nov 30 '20 at 12:24
  • @FabienMIFSUD sorry that doesn't work for me because there are a large number of columns in the table. So when I delete, it deletes the entries for all the columns for the particular ecid, and with insert I only add values for a few columns now. – scottstots Nov 30 '20 at 19:01
  • Ok, in that case, you can force all values to null in your insert values. Be careful, it will add many tombstones. – Fabien MIFSUD Dec 01 '20 at 10:27
  • But why does batching like this not work in such a case while it works without batching? – scottstots Dec 02 '20 at 08:34
  • Which consistency level do you use ? Try to force it to LOCAL_QUORUM in order to force Cassandra to be the most consistent possible. – Fabien MIFSUD Dec 02 '20 at 10:40
  • how many nodes in the cluster & what consistency level is used? – Alex Ott Dec 24 '20 at 12:58
  • There are 5 nodes in a cluster and consistency being set to EACH_QUORUM. – scottstots Dec 26 '20 at 08:20

0 Answers0