ACID Database Transactions for NoSQL Applications

ACID database transactions in Couchbase SQL++

Couchbase supports distributed multi-document ACID database transactions at scale without sacrificing performance and high availability. Migrate your relational database applications to Couchbase and achieve ACID compliance while taking advantage of rich SQL support.

What is ACID?

In a database, ACID is an acronym that describes how to process transactions consistently, securely, and reliably:

A	Atomicity	Update multiple documents with all-or-nothing guarantee.
C	Consistency	Automatically maintained consistency for replicas, indexes, and XDCR.
I	Isolation	Read committed isolation for concurrent readers.
D	Durability	Data protection under failures with tunable durability levels.

Transactions code samples

Here are three code samples of common transaction scenarios. The first is a debit/credit transaction to ensure both accounts are modified or neither is modified. The second is a bulk update of all employees in a department using SQL++ to ensure all employees are updated or none are.

                                        START TRANSACTION;
UPDATE customer SET balance = balance + 100 WHERE cid = 4872;
SELECT cid, name, balance  from customer;
SAVEPOINT s1;
UPDATE customer SET balance = balance – 100 WHERE cid = 1924;
SELECT cid, name, balance from customer;
ROLLBACK WORK TO SAVEPOINT s1;
SELECT cid, name, balance from customer;
COMMIT ;

                                        // Transfer money from Beth’s account to Andy’s account

transactions.run((txnctx) -> {
var andy = txnctx.get(collection, "Andy");
var andyContent = andy.contentAsObject();
int andyBalance = andyContent.getInt("account_balance");
var beth = txnctx.get(collection, "Beth");
var bethContent = beth.contentAsObject();
int bethBalance = bethContent.getInt("account_balance");

	if (bethBalance > transferAmount) {
		andyContent.put("account_balance", andyBalance + transferAmount);
		txnctx.replace(andy, andyContent);
bethContent.put("account_balance", bethBalance - transferAmount);
txnctx.replace(beth, bethContent);
}
else throw new InsufficientFunds();  
txnctx.commit();
 });

                                        // Bulk update salaries of all employees in a department
transactions.run((ctx) -> {

    var auditLine = JsonObject.create().put("content", "Update on 4/20/2020");
    ctx.insert(auditCollection, "Dept10", auditLine);
    ctx.query("UPDATE employees 
               SET salary = salary * 1.1  
               WHERE dept = 10 AND salary < 50000");
    ctx.query("UPDATE department 
               SET status = 'updated'
               WHERE dept = 10");
   
    txnctx.commit();  
});