Use cases and Best Practices for Distributed Transactions through N1QL

Does Couchbase support transactions?

Yes! With 6.5, we introduced the ACID transaction support in Couchbase through the SDKs. The 1st question we got at the time from the customers that heard about it was:

Is transaction support available through N1QL?

Yes! With 7.0, we have started supporting transactions through N1QL as well!N1QL transactions are multi-everything. Multi-documents that can span multiple collections from multiple scopes within multiple buckets. You can have multiple transactions running on a single query node and you can have multiple query nodes. There is NO central coordinator thereby eliminating a single point of failure or contention and making way for infinite scalability!

What is N1QL? and Why is it so important for Couchbase?

Just like SQL is to RDBMS, N1QL is to Couchbase!

N1QL is a declarative language-almost identical to SQL and it is used to insert/retrieve and manipulate the data stored in the form of JSON documents.Learn more about N1QL here .

If N1QL is like SQL, then why did it not have ACID before?

N1QL always had ACID within a single document. What we did not have before 7.0 was Multi-document Transaction support through N1QL.

Well, let’s think first about why many people believe they need multi-document transactions. The first principle of relational data modeling is normalizing your data across tables. This means that many common database operations, like account creation, require atomic updates across many rows and columns.

In Couchbase, the data model is fundamentally different. The document model encourages users to store related data together in a single document. N1QL has always supported ACID transactions in a single document and, when leveraging the document model appropriately, many applications don’t need ACID guarantees across multiple documents. As shown below, data that needs to be stored in 3 different tables tied by PK-FK, gets stored in a single document in Couchbase.  

For e.g. Adding an item to a shopping cart does not need transactions in Couchbase as you are updating just a single document

However, transactions are not just a check box. Transactions, like every Couchbase feature, aim to make developers’ lives easier. ACID guarantees across documents simplify the application logic needed to satisfy complex applications.

What are the Use Cases for Multi-Document transactions?:

There are use cases where transactional ACID guarantees need to be applied to a set of operations that span multiple documents.  “System of Record” applications are the typical class of workload where multi-document transactions are useful. Examples include:

• Processing application events when users perform actions that if repeated may cause different results, so they have to either all succeed or all fail. For e.g. typical bank debit, credit.
• Logging custom application actions –that cannot be reverted back even in case of system failure, say when a user transfers ownership of a car or a house, the write should not be successful if the logging isn’t.
• Many to many relationships where the data naturally fit into defined objects — for example, Clients, client orders, products, and manufacturers. To calculate the total in a client order, values from each table need to be collected and summed up for every client.

Details about transactions through N1QL in Couchbase:

Let’s dive into N1QL transactions with a simple transaction, debit, and credit. Selects have to read their own updates (RYOW)

START TRANSACTION;

UPDATE customer SET balance = balance – 100 WHERE cid = 4872;

UPDATE customer SET balance = balance + 100 WHERE cid = 1924;

SELECT cid, name, balance from customer where cid in [4872,1924];

COMMIT ;

This is the same syntax as you would write in MySQL. That’s the beauty of N1QL.

You don’t have to learn a new language to use Couchbase if you are already familiar with SQL.

In the example above,

If you are already familiar with the architecture of Couchbase, then

• START TRANSACTION will go to a query node. Query node will assign a unique transaction id (txid) to this transaction.

• Next comes the update statement. Query node will look at the where clause, select the appropriate index that will serve the query, find the document id, send the document id to the data store(KV) and fetch the document into the query node and then update the balance. If this was not within a transaction, this would be sent back to the datastore immediately. But since it is within the transaction, this document will now be stored in the cache of the query node. This cache we refer to as the delta table. We have 1 delta table per transaction per collection. That’s why we need all statements of a transaction to come to the same query node. This stitching is done with the txid provided by START TRANSACTION.
• With the next update, same thing, Query node will look at the where clause, select the appropriate index that will serve the query, find the document id, but now instead of directly going to KV to fetch the document, since there is a delta table, it will first look in the delta table if this document already exists in the delta table. In this case, it doesn’t so it goes fetches from KV, updates the balance, and stores it in the delta table.
• Next comes the select statement to the query node. Again query node will look at the where clause, select the appropriate index that will serve the query, find the document id. Now it will look for the document ids in the delta table, find them there and send the projected fields back to the client.

• So far you see we have read from the data store but never written to the data store. We have only written to the cache in the query node. And hence we say we follow optimistic concurrency. Multiple transactions could be operating on these very same documents at the same time.
• Now when the next statement hits the Query node “COMMIT”, that’s when the updated documents will go to the KV node and we will use the commit protocol highlighted here to actually commit the changes. 
• At this point, if there were multiple transactions updating the same documents, one would succeed and others would rollback and have to retry.

N1QL transaction Best Practices :

When should you use N1QL transactions?

1. When you have multiple query statements that should either all go through or all fail.A typical example: debit credit, multi-leg airline reservation
2. If you want to make sure that changes once made are not reverted back even in case of a system crash, like transfer of title for a house/car.
3. When the logic required spans data that cannot be merged into a single document and requires updates to multiple documents.
4. Typically very short-running workloads involving small result sets-no major aggregates.
5. When you might need to modify/read the same documents over and over again  (supported by Read your own writes by using delta tables)

What should you avoid doing while using N1QL transactions?

From even as simple example above you can probably see that:

1. Delta table (which is per transaction/per collection) will grow with every mutation. If you have a transaction, with a lot of mutations, memory usage will increase. So the recommendation is to limit the number of mutations within a transaction. For ETL-like loads, or massive updates that need ACID guarantees we have provided another option of Implicit transactions. You can read about them here.“memory-quota” setting in the query service can control the amount of memory consumed by the delta tables.
2. Use transactions only on documents less than 10MB in size.
3. Do not include DDL operations within N1QL transactions. Any DDL operations will result in an error.
4. By default, the timeout is 15s. This can be modified depending on the means you are using to use N1QL transactions(cbq shell, UI, Java SDK or REST API)
5. The query service was designed to provide high throughput and low latency, both of which can be impacted with transactions. Use N1QL transactions when your use cases have the need for transactions.
6. Since we use optimistic concurrency, we have to be sure that most of our workload is such that multiple transactions are not modifying the same documents at the same time. Because in that case, one will succeed and others will rollback with “CAS mismatch” or “Write write conflict” errors.
7. We should not be modifying the same documents using transactions and non-transactions at the same time.

Understand how transactions work by using the Transactions simulator

More about Transactions support through N1QL

https://staging.couchbase.com/blog/transactions-n1ql-couchbase-distributed-nosql/

https://staging.couchbase.com/blog/couchbase-transactions-with-n1ql/

https://docs.couchbase.com/java-sdk/current/howtos/distributed-acid-transactions-from-the-sdk.html#n1ql-queries