Couchbase Transactions with N1QL

By: Binh Le

Couchbase introduced ACID transactions in its v6.5 release with the Couchbase SDKs, and this has now been extended to Couchbase N1QL Query in the upcoming release 7.

A quick recap — Couchbase provides support for multi-document ACID transactions for its distributed database platform that are both horizontally scalable and also support high availability. This feature is available to application developers through Couchbase SDK and APIs. The APIs allow developers to safely make modifications to the database documents inside a transaction. Then proceed to commit or rollback all of the changes in the transaction depending on the application logic. Couchbase transactions work across multi-node, and multi-document across multi-bucket, scope and collections. It ensures that only committed data are readable by other processes, and automatically handles locking and conflicting detection.

Couchbase release 7 now extends the support of transactions to the N1QL query language. For RDBMS users, this enhancement provides the familiar transactional constructs, such as START TRANSACTION, SAVEPOINT, ROLLBACK and COMMIT. And similar to RDBMS transactions, N1QL transactions support all DML which is inline with other RDBMS implementations.

The benefits of N1QL Transactions

The key benefits of bringing transaction support to the N1QL query language

1. Provide a safer environment for ad hoc data manipulation, allowing developers/DBAs to modify the data, and verify its correctness before committing the changes to the database.
2. A simple way to conceptualize the process, and allows developers to validate the logic before embedding the operations into application code.
3. N1QL transactions also bring the full functionality of N1QL Data Manipulation Language (select/insert/update/delete/merge), allowing operation on multiple documents in each operation.
4. N1QL transactions are also fully supported with Couchbase SDK allowing applications to include both data service and N1QL query API in the same transaction.

N1QL transactions allow for this work to take place, while maintaining the database’s data integrity by ensuring incomplete changes are isolated, and not committed to the database until the transaction completes, or in the event of any unforeseen system failure.

Couchbase ACID transactions with N1QL

The N1QL transactions feature was built on top of the current Couchbase ACID transaction framework, and therefore afforded the same guarantees of ACID.

AtomicityThe all-or-nothing semantics for supporting the update of multiple documents in multiple shards/nodes now extends to “statement level atomicity”. For instance, an UPDATE that qualifies 100 documents must either succeed in updating all of the documents or roll back. Partial update will not happen.Consistency

Couchbase transaction framework provides the highest level of consistency for the data service, i.e. replicas are immediately consistent with transaction commit.

For non-transactional N1QL, the scan consistency option remains the same as before, i.e. it supports unbounded, at_plus, and request_plus. For N1QL transactions, the scan consistency will be defaulted to request_plus for all transactional reads.

IsolationN1QL Isolation supports READ COMMITTED for all readers regardless of whether the read is in a transaction or not.Durability

While the Couchbase transaction framework supports all three levels of durability, N1QL transactions via non-SDK (WebUI/cbq/RestAPI) will default to “Majority”.

Please refer to Couchbase documentation for more detail on durability.

N1QL Transactions Scalability

As with any services that we have designed in Couchbase, scalability, or more specifically horizontal scalability, is a key requirement. For N1QL transactions, where all of the processing starts in the query service, scalability is accomplished with the following:

No central transaction management — All transaction tasks and overheads are performed and maintained within the query service. In effect, the transaction management is distributed across different query services, thus will not have a central point of failure.

Transaction volume and size — The size of the transaction, which is currently only limited by the resources available to the query service, can be scaled up depending on the complexity of the transaction, thus can be scaled out quickly with additional query service nodes.

The above diagram highlights the following points:

1. Non transactional N1QL queries can be served by any of the available query services. The query service selected by the SDK is determined by the ns_server and is typically cycled through using a round robin approach.
2. For a N1QL transaction, one executed under the transaction context that Couchbase Transactions libraries provide, all subsequent DML operations (identified with txid) will be directed to the same query node, until the transaction is committed or rolled back. This is available in Java SDK 1.1.3
3. The SDK to Query node affinity is automatically managed by the SDKs.
4. Couchbase transactions support both KV and Query service DML operations through the Java Couchbase transactions library. You can also continue to access other services, such as FTS and Analytics with scan consistency request plus that honors the commit under the transaction.

Where can I use N1QL Transactions?

The N1QL Transactions feature is supported from anywhere you can you a N1QL query. The main requirement here is that a txid value, which is returned from the START TRANSACTION command is used with all subsequent N1QL DMLs if they are to be part of the transaction, and also with the final COMMIT or ROLLBACK.

For Query Workbench and CBQ shell, the passing of the txid is managed transparently, and users need not take any explicit actions.

CBQ Shell

cbq> \SET -query_context "default:`travel-sample`._default"
cbq> START TRANSACTION;
cbq> SELECT COUNT(*) FROM airport WHERE city='Stanted';
cbq> UPDATE airport SET city='London' WHERE faa='STN';cbq> SAVEPOINT s1;
cbq> DELETE FROM airport WHERE city='London' AND faa != 'STN';
cbq> ROLLBACK TRANSACTION TO SAVEPOINT s1;
cbq> COMMIT TRANSACTION;

N1QL Rest API

Once a transaction begins, the cbq shell will establish a session with a specific query service, and ensure that all subsequent N1QL DMLs are sent with the txid (returned with TRANSACTION) to the same query service.

With the REST API, users will need to supply the value of the txid with subsequent DMLs.

>curl -u Administrator:password  http://localhost:8093/query/service -H "Content-Type: application/json" -d '{"statement":"START TRANSACTION", "pretty":true,"txtimeout":"2m","scan_consistency":"request_plus","durability_level":"majority","durability_timeout":"2s"}'
{
    "requestID": "34d585a7-3c4a-4f0e-8cd3-d3ffa7df0bb3",
    "signature": "json",
    "results": [
    {
        "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "4.196083ms",
        "executionTime": "4.12112ms",
        "resultCount": 1,
        "resultSize": 62,
        "serviceLoad": 0
    }
}
>curl -u Administrator:password http://localhost:8093/query/service -H 'Content-Type: application/json' -d '
{"statement":"SQL statement", "pretty":true,"txid":"d81d9b4a-b758-4f98-b007-87ba262d3a51"}'

Transaction Implicit setting — tximplicit

As part of the N1QL transactions, we also have the transaction implicit setting tximplicit. In a nutshell, when this is set to true, all subsequent N1QL DMLs are run as if they are wrapped between a START TRANSACTION and a COMMIT TRANSACTION. The implication of this setting means that you can use all of the N1QL enhancements that are part of the N1QL transactions feature.

Durability

Non transactional N1QL currently does not support any setting for durability. All mutations via N1QL uses durability=none. With tximplicit set, the N1QL DML will get the durability

Implications: The setting allows you to indirectly set the durability, but expect additional latency.

Scan consistency

Non transactional N1QL scan consistency can be set in the SDK to any level required by the application. https://docs.couchbase.com/server/5.0/indexes/performance-consistency.html

However with tximplicit set, the DML will be run with scan consistency request_plus.

Implications: The query latency may increase as it needs to ensure that the indexes that the query rely on need to to be updated to the timestamp when the query is issued.

Resource usage

Query service requires memory resources in order to process the query, especially for aggregation and sort operations. For non-transactional DMLs, all mutations take immediate effect. For transactional DMLs, all mutations within a transaction are kept locally on the query service until commit time. For this reason, memory resource usage will be much higher for the transaction span.

Implications: N1QL transactions mutations will increase memory usage.

N1QL Transactions guidelines

1. Because of the requirements for durability, scan consistency and resource usage with N1QL transactions, you should therefore, use a transaction only when you need to.
2. It is worth mentioning that you should only use N1QL transactions when your use cases have the need for transactions. The query service was designed to provide high throughput and low latency, both of which can be impacted with transactions.
3. Transaction size, i.e. the number of documents affected by the mutations and the number of operations, should be kept small.
4. Using N1QL transactions for large volume mutations use cases are not recommended.

Documentation

• What’s new
• Release notes
• Download the Couchbase 7 Beta
• Introducing Couchbase 7 Beta: Mapping RDBMS to NoSQL
• Scopes and Collections for Modern Multi-Tenant Applications: Couchbase 7.0

Binh Le is a Principal Product Manager for Couchbase Query service. Prior to Couchbase, he worked at Oracle and led the product management team for Sales Clould Analytics and CRM OnDemand. Binh holds a Bachelor’s Degree in Computer Science from the University of Brighton, UK.