Azure Cosmos DB for Apache Cassandra Lightweight Transactions with Conditions

APPLIES TO: Cassandra

Apache Cassandra as most NoSQL database platforms gives precedence to availability and partition-tolerance over consistency, as it does not support ACID transactions as in relational database. For details on how consistency level works with LWT see Azure Cosmos DB for Apache Cassandra consistency levels. Cassandra supports lightweight transactions(LWT) which borders on ACID. It helps perform a read before write, for operations that require the data insert or update must be unique.

To use LWT within Azure Cosmos DB for Apache Cassandra, we advise that the following flags are set at the create table level.

with cosmosdb_cell_level_timestamp=true and cosmosdb_cell_level_timestamp_tombstones=true and cosmosdb_cell_level_timetolive=true

You might experience reduced performance on full row inserts compared to not using the flags.

CREATE KEYSPACE IF NOT EXISTS lwttesting WITH REPLICATION= {'class': 'org.apache.cassandra.locator.SimpleStrategy', 'replication_factor' : '1'};

CREATE TABLE IF NOT EXISTS lwttesting.users (
  name text,
  userID int,
  address text,
  phoneCode int,
  vendorName text STATIC,
  PRIMARY KEY ((name), userID)) with cosmosdb_cell_level_timestamp=true and cosmosdb_cell_level_timestamp_tombstones=true and cosmosdb_cell_level_timetolive=true; 

This query below returns TRUE.

INSERT INTO lwttesting.users(name, userID, phoneCode, vendorName)
VALUES('Sara', 103, 832, 'vendor21') IF NOT EXISTS; 

There are some known limitations with flag enabled.

• If a row has been inserted into the table, an attempt to insert a static row returns FALSE.

  INSERT INTO lwttesting.users (userID, vendorName)
  VALUES (104, 'staticVendor') IF NOT EXISTS;
  

  The above query currently returns FALSE but should be TRUE.

• Attempting to insert another row after TTL is expired, returns false.

  CREATE TABLE IF NOT EXISTS lwttesting.customers (
    customer text PRIMARY KEY, 
    user text, entry timestamp) with cosmosdb_cell_level_timestamp=true and cosmosdb_cell_level_timestamp_tombstones=true and cosmosdb_cell_level_timetolive=true;
  
  INSERT INTO lwttesting.customers (customer, user, entry) 
  VALUES ('vendor', 'Sara', '2023-10-10 15:00:00.000000+0000') IF NOT EXISTS USING TTL 30;
  

  This query returns TRUE. However attempting another insert returns FALSE.

Row delete combined with IF condition is not supported if the flags are not enabled.

CREATE TABLE IF NOT EXISTS lwttesting.vendor_users (
  name text,
  userID int,
  areaCode int,
  vendor text STATIC,
  PRIMARY KEY ((userID), name)
);

DELETE FROM lwttesting.vendor_users 
WHERE userID =103 AND name = 'Sara' 
IF areaCode = 832;

An error message: Conditional delete of an entire row is not supported.

Any request containing assignment and condition combination of a static and regular column is unsupported with the IF condition. This query does not return an error message as both columns are regular.

DELETE areaCode 
FROM lwttesting.vendor_users 
WHERE name= 'Sara' 
AND userID = 103 IF areaCode = 832;   

However, the query below returns an error message Conditions and assignments containing combination of Static and Regular columns are not supported.

DELETE areaCode 
FROM lwttesting.vendor_users 
WHERE name= 'Sara' AND userID = 103 IF vendor = 'vendor21';  

In this tutorial, you learnt about Lightweight Transaction works within Azure Cosmos DB for Apache Cassandra. You can proceed to the next article:

• Migrate your data to a API for Cassandra account
• Run Glowroot on Azure Cosmos DB for Apache Cassandra