Transaction Management in Cassandra

As we are all from the Sql Background and its been ages SQL rules the market , so transaction are something favorite to us .
While Cassandra does not support ACID (transaction) properties but it gives you the ‘AID’ among it .
That is Writes to Cassandra are atomic, isolated, and durable in nature. The “C” of ACID—consistency—does not apply to Cassandra, as there is no concept of referential integrity or foreign keys.

Cassandra offers you to tune your Consistency Level as per your needs . You can either have partial or full consistency that is You might want a particular request to complete if just one node responds, or you might want to wait until all nodes respond .

We will talk here about the ways we can implement the so called transaction concept in Cassandra .

Light Weight Transactions
They are also known as compare and set transactions

Lightweight transactions can be used for both INSERT and UPDATE statements, using the new IF clause. Here’s an example of entering a student details :

Scenario

Perhaps the most common situation when such a feature would be required where you want some unique column value to be maintained like in the registration of new users to an application or some userId etc.

Before compare-and-set

TABLE – STRUCTURE
create table student_details(
id text,
stu_name text,
stu_rollNo int
Primary Key(id)
)

Lets write a method to insert student details .

def registerStudent(stuId: String, stuName: String, stuRollNo: Integer) = {
val session = CassandraConnProvider.cassandraConn
val preparedStmt = session.prepare("INSERT INTO student_details(id , stu_name ,stu_rollNo) VALUES(?,?,?)")
session.execute(preparedStmt.bind(stuId , stuName ,stuRollNo))
logger.info("completed Registration!!!")
}

Now lets execute it : –

registerStudent(“1A”, “testUser1”, 1)

Result on querying the DB: –

cqlsh:test_keyspace> select * from student_details ;

id | stu_name  | stu_rollno
----+-----------+------------
1A | testUser1 |          1

(1 rows)

So far good , now lets register another student

registerStudent(“1A”, “testUser2”, 2)

cqlsh:test_keyspace> select * from student_details ;

id | stu_name  | stu_rollno
----+-----------+------------
1A | testUser1 |          2

(1 rows)

Ooops… What happened to testUser1?

Since the student table is defined with id as the rowkey and considering that Cassandra makes no distinction between INSERT and UPDATE, using the same id will be considered as a write operation on the same row and hence will overwrite the previous record.

Using compare-and-set operations

Here is where the new compare-and-set feature comes into the picture. Let’s create a second version of the registerStudent method:

def checkBeforeRegisteringStudent(stuId: String, stuName: String, stuRollNo: Integer) = {
val session = CassandraConnProvider.cassandraConn
val preparedStmt = session.prepare("INSERT INTO student_details(id , stu_name ,stu_rollNo) VALUES(?,?,?) IF NOT EXISTS")
session.execute(preparedStmt.bind(stuId , stuName ,stuRollNo))
logger.info("completed Registration!!!")
}

So the 3 magical words are “IF NOT EXISTS”.

lets see how does this makes the difference truncate the table and try again with new method.

On executing the new checkBeforeRegisteringStudent method : –

checkBeforeRegisteringStudent(“1A”, “testUser1”, 1)
checkBeforeRegisteringStudent(“1A”, “testUser2”, 2)

cqlsh:test_keyspace> select * from student_details ;

id | stu_name  | stu_rollno
----+-----------+------------
1A | testUser1 |          1

(1 rows)

So Least what we expect is that testUser1 details are not lost and if some same id values are not inserted or corrupts the records .

2) Now comes another advantage of using Compare and Set , that is a meaningful response from the driver .

Generally in RDBMS- DB’s while using a transaction, either an exception would be thrown or we would be able to see what is in the database. Either way we would be able to somehow signal the second user trying to insert that something has gone wrong. Fortunately it is possible to do so with Cassandra, too.

You probably have noticed that in Cassandra’s Java driver there’s no distinction between the read and the modification operations. In both cases you simply invoke:

session.execute();

This operation always returns with a com.datastax.driver.core.ResultSet object. In case of a “normal” update/insert command even though the returned ResultSet is not-null, it’ll contain no data.

The compare and set gives us some advantages : –

When you try to print the response back like : –

val rs = session.execute(stmt)
val row = rs.one()
logger.info(row)

Output : –

Row[true]
Row[false, 1A, testUser1, 1]

1- Instead of an empty ResultSet, now we will get some textual info that we can use to check the integrity of insert statement .
2- There is a column labelled [applied] that is not part of the original table
3- When the insert was unsuccessful, because the IF condition(in the query) was evaluated false, we actually get back the row that was preventing the insert from happening

A compare-and-set style insert is different. Let’s slightly modify the code of checkBeforeRegisteringStudent by adding the following lines :

def registerStudent(stuId: String, stuName: String, stuRollNo: Integer) = {
val session = CassandraConnProvider.cassandraConn
val preparedStmt = session.prepare("INSERT INTO student_details(id , stu_name ,stu_rollNo) VALUES(?,?,?) IF NOT EXISTS")
val response = session.execute(preparedStmt.bind(stuId, stuName, stuRollNo))

val row = response.one()
if (row.getBool("[applied]")) {
logger.info("Successfully added details " + stuName)
} else {
logger.info(s"This userid has already been registered by name ${row.getString("stu_name")} having Id : ${row.getString("id")} ")
}
}

When executed against a truncated table, the output shows the second insert failing:

13:56:32.717 [main] INFO  TransactionMgt$ – Successfully added details testUser1

13:56:32.724 [main] INFO  TransactionMgt$ – This userid has already been registered by name testUser1 having Id : 1A

That all how Compare-And-Set help while inserting , lightweight transactions provide developers with the ability to ensure that a given sequence of operations is performed as a unit, without interference from other requests .

Next Blog we will focus on Compare and Set in Update statements .