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.
![Analytics ecosystem diagra](https://cdn.statically.io/img/www.couchbase.com/wp-content/uploads/sites/3/2022/10/transaction.jpg)
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();
});
Related resources
Whitepaper
Ready to create amazing customer experiences?
The easiest and fastest way to begin with Couchbase