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;
UPDATE customer SET balance = balance – 100 WHERE cid = 1924;
SELECT cid, name, balance from customer;
SELECT cid, name, balance from customer;
                                        // Transfer money from Beth’s account to Andy’s account -> {
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();  
                                        // Bulk update salaries of all employees in a department -> {

    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");