{"id":9439,"date":"2020-10-14T12:45:47","date_gmt":"2020-10-14T19:45:47","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=9439"},"modified":"2025-06-13T18:44:03","modified_gmt":"2025-06-14T01:44:03","slug":"transactions-n1ql-couchbase-distributed-nosql","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/transactions-n1ql-couchbase-distributed-nosql\/","title":{"rendered":"N1QL Transactions : Elastic, Scalable, and Distributed"},"content":{"rendered":"

SQL is the only 22nd-century language available for developers today.<\/span><\/p><\/blockquote>\n

ABSTRACT<\/strong><\/p>\n

In relational database systems, SQL is more than a declarative query language. It includes procedural language (T-SQL, PL\/SQL, etc) and defines transactions and its semantics.\u00a0 SQL as a query language has been <\/span>unreasonably effective even in <\/span>NoSQL database systems<\/span><\/a>. However, few NoSQL database systems support transactions.\u00a0 The ones that support come with a long list of limitations and\/or were unable to support SQL operations within the transaction.\u00a0 We introduce and explain transactions in Couchbase N1QL: SQL for JSON.\u00a0 N1QL transactions are multi-everything: multi-document, multi-bucket, multi-scope, multi collection, and multi-DML-statement.<\/span><\/p>\n

N1QL Transactions is available with Couchbase 7.0 Beta. You can download it here: https:\/\/www.couchbase.com\/downloads<\/a>. See the documentation here<\/a>.<\/p>\n

INTRODUCTION<\/strong><\/p>\n

\"\"<\/a>N1QL<\/span><\/a> is<\/span>\u00a0a declarative language to manipulate JSON. Couchbase stores all the documents in the data service.\u00a0 The query service orchestrates the <\/span>query execution<\/span><\/a> optimizing the query, creating an execution plan, and then executing it using data, indexing, and FTS.\u00a0 The Couchbase SDK<\/a> and query interaction protocol is built via REST over HTTP\/S.\u00a0 \u00a0N1QL DML statements include SELECT<\/a>, INSERT<\/a>, UPDATE<\/a>, UPSERT<\/a>, DELETE<\/a>, and MERGE<\/a>.\u00a0\u00a0<\/span><\/p>\n

N1QL TRANSACTIONS<\/strong><\/p>\n

Here’s an example of transactions in RDBMS and Couchbase N1QL.<\/p>\n

\n\n\n\n\n\n\n
\n
Transactions<\/strong><\/span><\/h6>\n<\/td>\n
\n
MySQL Database (Statements are same\/similar in Oracle, SQL Server, Informix & DB2)<\/span><\/h6>\n<\/td>\n
\n
Couchbase Database (Cheshire-Cat)<\/strong><\/span><\/h6>\n<\/td>\n<\/tr>\n
\n
Insert data. Tuples in MySQL, JSON documents in Couchbase<\/h6>\n<\/td>\n
\n
INSERT INTO customer(cid, name, balance) VALUES(4872, “John Doe”, 724.23);<\/h6>\n
INSERT INTO customer(cid, name, balance) VALUES(1924, “Bob Stanton”, 2735.48);<\/h6>\n<\/td>\n
\n
INSERT INTO customer
\nVALUES(“cx4872”, {“cid”: 4872, “name”:”John Doe”, “balance”:724.23});<\/h6>\n
INSERT INTO customer
\nVALUES(“cx1924”, {“cid”: 1924, “name”:”Bob Stanton”, “balance”:2735.48});<\/h6>\n<\/td>\n<\/tr>\n
\n
Simple transaction, debit, and credit. Intermediate selects have to be read their own updates (RYOW)<\/h6>\n<\/td>\n
\n
START TRANSACTION;<\/strong><\/h6>\n
UPDATE customer SET balance = balance + 100 WHERE cid = 4872;<\/h6>\n
SELECT cid, name, balance\u00a0 from customer;<\/h6>\n
UPDATE customer SET balance = balance – 100 WHERE cid = 1924;<\/h6>\n
SELECT cid, name, balance from customer;<\/h6>\n
COMMIT ;<\/strong><\/h6>\n<\/td>\n
\n
START TRANSACTION;<\/strong><\/h6>\n
UPDATE customer SET balance = balance + 100 WHERE cid = 4872;<\/h6>\n
SELECT cid, name, balance\u00a0 from customer;<\/h6>\n
UPDATE customer SET balance = balance – 100 WHERE cid = 1924;<\/h6>\n
SELECT cid, name, balance from customer;<\/h6>\n
COMMIT ;<\/strong><\/h6>\n<\/td>\n<\/tr>\n
\n
The second transaction with partial rollback.<\/h6>\n<\/td>\n
\n
START TRANSACTION;<\/strong><\/h6>\n
UPDATE customer SET balance = balance + 100 WHERE cid = 4872;<\/h6>\n
SELECT cid, name, balance\u00a0 from customer;<\/h6>\n
SAVEPOINT s1;<\/strong><\/h6>\n
UPDATE customer SET balance = balance – 100 WHERE cid = 1924;<\/h6>\n
SELECT cid, name, balance from customer;<\/h6>\n
ROLLBACK WORK TO SAVEPOINT s1;<\/strong><\/h6>\n
SELECT cid, name, balance from customer;<\/h6>\n
COMMIT ;<\/strong><\/h6>\n<\/td>\n
\n
START TRANSACTION;<\/strong><\/h6>\n
UPDATE customer SET balance = balance + 100 WHERE cid = 4872;<\/h6>\n
SELECT cid, name, balance\u00a0 from customer;<\/h6>\n
SAVEPOINT s1;<\/strong><\/h6>\n
UPDATE customer SET balance = balance – 100 WHERE cid = 1924;<\/h6>\n
SELECT cid, name, balance from customer;<\/h6>\n
ROLLBACK WORK TO SAVEPOINT s1;<\/strong><\/h6>\n
SELECT cid, name, balance from customer;<\/h6>\n
COMMIT ;<\/strong><\/h6>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

If you didn’t see much difference, that’s because, there isn’t.\u00a0<\/span><\/p><\/blockquote>\n

N1QL TRANSACTIONS STATEMENTS<\/strong><\/p>\n

N1QL transactions a set of transactions that include any of the DML statements in all forms: no restrictions<\/span>. Transactional protection is issued from new statements: BEGIN\/START, COMMIT, ROLLBACK, SAVEPOINT<\/span>.<\/p>\n

START TRANSACTION\u00a0 (same as BEGIN WORK)<\/strong><\/h6>\n

This statement starts a new transaction, assigns a new transaction ID, and returns the transaction ID to the caller. There are two rules the SDKs, tools (e.g. CBQ shell) follow to successfully execute the rest of the transaction.<\/p>\n

    \n
  1. Send this transaction ID as a parameter every subsequent statement within the transaction.\u00a0 This is how the query service knows the statement should be run as part of a particular transaction.<\/li>\n
  2. Couchbase can have multiple query service nodes, but a single transaction is executed on a single query node. You can start a new transaction in ANY QUERY NODE. However, the rest of the statements FOR THAT SINGLE TRANSACTION should be sent to the VERY SAME query node.<\/li>\n<\/ol>\n
    COMMIT TRANSACTION or COMMIT WORK;<\/strong><\/h6>\n

    This commits all the changes in the transaction to the data store. This is a distributed commit of the transaction into the Couchbase key-value data store.\u00a0 The commit supports all of the Couchbase durability<\/a> options. This is still a distributed system — just like astronomy, rare things happen often. On any failure, the complete transaction is rolled back automatically and the application needs to retry the transaction. Failures can occur for various reasons: network failure, node failure, node overloaded, and write-write conflict.\u00a0 Just like direct Couchbase WRITEs are optimistic and failures can occur due to concurrent writes resulting in CAS conflicts<\/a>, N1QL transactions can also fail due to write conflicts. We implement a form of optimistic concurrency<\/a> approach to transactions.<\/p>\n

    ROLLBACK TRANSACTION or ROLLBACK WORK;<\/strong><\/h6>\n

    On an application issued rollback, all of the modifications done within the transaction are rolled back.<\/p>\n

    As you’ve seen from the examples above, N1QL also supports savepoints<\/strong> and rollbacks to the savepoints within the transaction.\u00a0 From the application perspective, these work the same as the RDBMS counterparts.<\/p>\n

    TRANSACTIONAL FEATURES<\/strong><\/p>\n

    The transaction is more than just the statements — it’s all about the semantics and guarantees.\u00a0 Hence the ACID<\/a> definition. We talked about atomicity earlier wrt to COMMIT.\u00a0 Let’s talk a bit more on this.<\/p>\n

    ATOMICITY\u00a0<\/strong>is required for both the whole transaction and each statement.\u00a0 The DML statements will atomically rollback on any failure, but the transaction itself is open and can be continued. An example of a failure is a document key conflict on insert.<\/p>\n

    CONSISTENCY<\/strong> ensures the constraints are applied consistently for each statement. The only constraint in Couchbase is the unique constraint on the document key.\u00a0 N1QL checks for the pre-existence of each of the key inserted and rolls back the statement on any conflict.\u00a0 Remember we use optimistic concurrency control.\u00a0 That means, even after the INSERT is successful,\u00a0 the commit stage can still run into a write-write conflict because some other session could have inserted between the insert and commit.\u00a0 \u00a0You’ll have to retry the transaction on such failures.<\/p>\n

    ISOLATION<\/strong><\/p>\n

    We support the COMMITTED READ isolation level. All the data that’s read and evaluated is committed data in the index and data store. By default, we use the stringent request_plus<\/a> consistency on the index reads.\u00a0 This means, for a given predicate, we use the latest data in the index to qualify the documents to qualify select\/update\/delete.\u00a0 We then go the extra step to fetch the documents from the KV store and re-apply the predicates to ensure the latest committed version of the document is qualified and updated.<\/p>\n

    If performance wasn’t a consideration, everyone would have used serializable transactions ;-) You can change the scan consistency to unbounded<\/a> for improved index scan performance.<\/p>\n

    DURABILITY<\/strong><\/p>\n

    N1QL supports all of the durability options<\/a> and features with the Couchbase data store to ensure durability on our distributed database.<\/p>\n

    CONCURRENCY<\/strong>\"\"<\/a>Broadly speaking, database transactions use either pessimistic or optimistic concurrency control<\/a>. Traditional single node databases follow the pessimistic concurrency control to avoid conflicts.\u00a0 This approach is also applied to some of the multi-node implementations like Oracle RAC, DB2 Sysplex. Multi-node implementations are possible but require expensive Infiniband, custom hardware, etc.<\/p>\n

    Optimistic concurrency control version each base unit of tuple (rows in rdbms, documents in Couchbase), remember the version they read to modify and check if the version has changed during the write.\u00a0 If there’s indeed a conflict, the whole transaction has to be retried. The advantage of this approach is, in a well-designed application, there should be little conflicts: you won’t withdraw cash and transfer money between accounts at the same nanosecond.\u00a0 On the rare occasion you do, the retry is tolerated.<\/p>\n

    Concurrency in the N1QL Query Service<\/strong><\/h6>\n

    Couchbase N1QL uses optimistic concurrency control. Each transaction reads the documents it needs to update, updates them, and keeps the updated documents in its private per-transaction<\/strong> cache. When you issue a subsequent statement, the query service is aware of the updated documents within the transactions and uses that version instead of the older version reflected in the index\/data.\u00a0 This is how it provides READ-YOUR-OWN-WRITE support.\u00a0 This is modeled so all the DML statements, all operations (select, join, project, aggregate, nest, unnesst, etc) will all get this RYOW benefit providing a consistent and crucial feature of the transaction. Even while the application is doing transactions (doing both reads and writes), within the transaction we’re reading and caching the updates until the commit time. This is the READ phase of the transaction, And because of this approach, there’s no coordination between multiple transactions or multiple query nodes within a transaction until commit (WRITE phase). This ensures the performance and scalability of distributed transactions in Couchbase.\u00a0 And, before you ask,\u00a0 all these work concurrently with Couchbase distributed transactions<\/a> we released in 6.5<\/a>.<\/p>\n

    Coordination is the bane of scalable systems — Peter Bailis<\/a><\/span><\/p><\/blockquote>\n