CosmosDB (mongo api) Transaction

CosmosDB, like MongoDB is essentially a NoSQL database - meant for horizontal scaling, flexible schema, and transactions at a single document level only. In Mongo 4.0, they have supported transactions ( on un-sharded collections ). Now you can do transactions in cosmosdb mongo api too. In this post I am sharing a simple set up to demonstrate transaction behavior in cosmosdb and mongodb separately

Transactions have been the bane of relational database systems with its ACID properties

A - Atomic - a transaction cannot be broken down - it will either succeed or fail completely

C - Consistency - database state moves from one consistent state to another

I - Isolation - each transaction takes place in its own, isolated environment

D - Durable - once committed - the change is permanent

Among all of the ACID behaviors above, isolation can be shown easily.

First we will need to create our MongoDB and CosmosDB resources. You can create MongoDB anywhere. In my case, I used a docker image.

$ docker run -d --name mongo -p 27017:27017 mongo

Next we will create a CosmosDB resource in Azure. When creating the resource, be sure to choose mongo api and version 4.0

A. Behavior in MongoDB

Now we are ready to roll. We will open up two terminal sessions. One will write to the database and another will read the database.

A1 Transaction Reader Script

In the reader session, we will query a collection in a loop every second. Here is a bash script that does that. Simply put it in a file and execute it and watch.

#!/usr/bin/bash


echo -e "\n Reading the accounts collection every second in read isolated mode while another session is updating the collection \n control-c to quit \n"

while true

do

  mongo --quiet --eval 'let curdate = new Date() ; printjson ( "Current time : " + curdate ) ; db.getMongo().getDB("transactions").accounts.find().forEach(printjson);'

  sleep 1

done

Run the script and let it print current time every second.Since initially the collection is empty it has no data, and hence only prints timestamp.

A2 Transaction Writer Script

Now open up another session connected to the database. Here we will first add two documents of bank account deposit for Lisa and John. They will both have initial deposit of $500. This initial deposit will be done outside of a transaction that commits immediately. Then we open up a transaction block and transfer $100 from John to Lisa. We will wait for 5 seconds and rollback the transaction. The writer session in an isolated mode ( "I" of ACID ) sees the temporary data transfer in that 5 seconds timeframe. But the reader never sees it.

Finally when the transaction rollback is complete, both the writer and the reader agrees that no transfer was made.

 cat demo_transaction_writer.js
// Test of transaction for cosmosdb mongo api. Need mongo 4.0 both on the server and and the client end.


// insert data into accounts collection. Remove the collection to repeat this test


db.getMongo().getDB("transactions").accounts.insert({_id:1, name:"Lisa", balance: 500})
db.getMongo().getDB("transactions").accounts.insert({_id:2, name:"John", balance: 500})


// Two documents inserted above - outside of a transaction - they are committed immediately and visible to another session immediately


printjson ( "Two docuemts inserted outside of a transaction so they are committed immediately and visible to reader session")


db.getMongo().getDB("transactions").accounts.find().forEach(printjson);




// start transaction


var session = db.getMongo().startSession();
var accountsCollection = session.getDatabase("transactions").accounts;
session.startTransaction() ;


// Transfer 100 from John to Lisa. On a different session connected to this database, query repeatedly - you will not see the update because
// the transaction runs in isolated transaction mode and it is rolled back. Meanwhile this session will see the update - before it is rolled back.


try {


    accountsCollection.updateOne({ name: "Lisa" },  { $inc: { balance: 100  } } );
    accountsCollection.updateOne({ name: "John" }, { $inc: { balance: -100 } } );


    printjson ( "Updated two documents, uncommitted transaction. Update is seen only by writer session, not the reader ") ;


    let curdate = new Date() ; printjson ( "Current time : " + curdate ) ;
    printjson ( curdate ) ;
    accountsCollection.find().forEach(printjson);


    sleep ( 5000 ) ;


    printjson ( "Roll back transaction and query and drop the collection") ;


    session.abortTransaction();


    accountsCollection.find().forEach(printjson);


    accountsCollection.drop()


// cosmosdb mongo api transaction is not active if you sleep for more than 5 seconds..


} catch (error) {


// abort transaction on error


    session.abortTransaction();
    throw error;
}

Please note that the transaction support of mongodb requires mongo version 4.0 and above for the database server as well as the client. In my machine the mongo client is 3.6.8 but the docker had version 4.0. Thus I wanted to connect to docker to execute the script. If you have mongo 4.0 or higher client installed you will not need this step.

$ docker cp demo_transaction_writer.js gutlo-mongo:/
$ docker exec -it gutlo-mongo bash
# mongo --quiet demo_transaction_writer.js

"Two docuemts inserted outside of a transaction so they are committed immediately and visible to reader session"
{ "_id" : 1, "name" : "Lisa", "balance" : 500 }
{ "_id" : 2, "name" : "John", "balance" : 500 }
"Updated two documents, uncommitted transaction. Update is seen only by writer session, not the reader "
"Current time : Fri May 28 2021 03:02:14 GMT+0000 (UTC)"
ISODate("2021-05-28T03:02:14.564Z")
{ "_id" : 1, "name" : "Lisa", "balance" : 600 }
{ "_id" : 2, "name" : "John", "balance" : 400 }
"Roll back transaction and query and drop the collection"
{ "_id" : 1, "name" : "Lisa", "balance" : 500 }
{ "_id" : 2, "name" : "John", "balance" : 500 }

Above, you can see that the writer session sees the initial deposit, its own isolated update, and rollback. Now look at the reader session and it never saw the 600, 400 amount.

{ "_id" : 1, "name" : "Lisa", "balance" : 500 }
{ "_id" : 2, "name" : "John", "balance" : 500 }
"Current time : Thu May 27 2021 23:27:04 GMT-0400 (EDT)"
{ "_id" : 1, "name" : "Lisa", "balance" : 500 }
{ "_id" : 2, "name" : "John", "balance" : 500 }

A. Behavior in CosmosDB (Mongo Api, version 4.0)

Now we will repeat the same test - except instead of mongodb we will connect to cosmosdb. For the purpose of this test we allow cosmosdb to be accessible from all networks. We need to find out the connection string for cosmosdb

So instead of mongo --quiet <reader_or_writer_script> you say

# mongo --quiet --host <host>:10255 -u <username> -p <password> --tls --tlsAllowInvalidCertificates <reader_or_writer_script>

Here you will see the same data changes from reader and writer sessions in CosmosDB. This shows that CosmosDB supports the mongo 4.0 transaction behavior in an identical fashion.

To learn more about how you can move your existing mongodb application to cosmosdb look up the documentation for reference