There is a scenario where I need to update multiple partitions (of the same or different table) together. Consider an example of the orders here:
create table test.orders_by_id(
order_email text,
order_id timeuuid,
order_name text,
order_status int,
order_note text, //1 - Pending, 2 - In Progress, 3 - On Hold, 4 - Confirmed, 5 - Cancelled
order_number text,
PRIMARY KEY (order_id)
);
create table test.orders_by_number(
order_email text,
order_id text,
order_name text,
order_status int,
order_note text,
order_number text,
PRIMARY KEY (order_number)
);
create table test.work_audit_orders (
order_id text,
log_id timeuuid,
log_audit text,
PRIMARY KEY(order_id,work_log_id));
Insertion So in the above case, adding a new order would require for me to use the batch as that would affect orders_* and work_* tables and should happen together. That is something which is do-able in a batch.
begin batch
insert into test.orders_by_id(order_email,order_id,order_name,order_status,order_number) values ('[email protected]',d1918050-d310-11e6-946e-d368aab1da02,'ORDER_1023',1,'1235');
insert into test.orders_by_number(order_email,order_id,order_name,order_status,order_number) values ('[email protected]','d1918050-d310-11e6-946e-d368aab1da02','ORDER_1023',1,'1235');
insert into test.work_audit_orders(order_id,log_id,log_audit) values ('d1918050-d310-11e6-946e-d368aab1da02',now(),'New Order Created: order 1235');
apply batch;
Conditional Update Issue: However, lets take a case when there needs to be compare and set and based on that we need to change orders_* table as well as work_* tables. For example, based on a flow the order can only be Cancelled when in the Pending State only. Here the update statements would need LWT to make it completely serialized and avoid any race condition. however as I understand that if we update that in the batch that should only be associated with one parition as the Paxos Implementation works at the granularity of the Partition, hence making batch oriented towards the condition, which makes it difficult to put all the statements in the same batch:
begin batch
update orders_by_id set order_status = 5 where order_id = d1918050-d310-11e6-946e-d368aab1da02 if order_status = 1;
update orders_by_number set order_status = 5 where order_number='1235' if order_status = 1;
insert into work_audit_orders (order_id, log_id, log_audit) values ('d1918050-d310-11e6-946e-d368aab1da02', now(),'Order 1235 Cancelled');
apply batch;
Partly I believe that materialized views can take care of the problem with order_* and batch, but how do I take care of the work_* statements that must reflect in batch to ensure that its properly logged and executed when submitted.
I think what I am trying to achieve is Atomicity with partly serialized isolation here, which may not be possible here.
The LWT cannot span multiple partitions (hence multiple tables) so you are out of luck.
This seems to me a similar case of this SO question: Cassandra - Batch too large. Have a look at this answer of mine and check if it fits your use case.