Azure Cosmos DB for Apache Cassandra Lightweight Transactions with Conditions

Article
12/05/2023

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.

LWT support within Azure Cosmos DB for Apache Cassandra

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.

Note

LWTs are not supported for multi-region write scenarios.

LWT with flags enabled

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;

Limitations

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.

LWT with flags disabled

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.

LWT with flags enabled or disabled

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';

Next steps

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