Multi-document, distributed ACID transactions are now available in Couchbase as part of the N1QL query language.
Couchbase introduced ACID transactions in its v6.5 release with the Couchbase SDKs, and this has now been extended to the Couchbase N1QL Query Service in the 7.0 release.
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 and then commit or rollback all of the changes in the transaction depending on the application logic.
Couchbase transactions work everywhere: across nodes, documents, buckets, Scopes and Collections. The transactions process ensures that only committed data is readable by other processes, and it automatically handles locking and conflicting detection.
The 7.0 release extends distributed transaction support to the N1QL query language.
For relational database (RDBMS) users, this N1QL enhancement provides familiar transactional constructs, such as START TRANSACTION, SAVEPOINT, ROLLBACK and COMMIT. N1QL transactions also support all Data Manipulation Languages (DML) which is in line with other RDBMS implementations.
The Benefits of N1QL Transactions
The key benefits of bringing distributed transaction support to the N1QL query language include:
- A safer environment for ad hoc data manipulation, allowing developers and DBAs to modify the data and verify its correctness before committing the changes to the database.
- A simple way to conceptualize the process that allows developers to validate the logic before embedding the operations into application code.
- The full functionality of N1QL Data Manipulation Language (select/insert/update/delete/merge), enabling operations on multiple documents at a time.
- Complete compatibility with the Couchbase SDK, allowing applications to include both the Data Service and the N1QL query API in the same transaction.
N1QL transactions allow for all of the above to take place. At the same time, N1QL transactions maintain 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 full ACID compliance.
| Atomicity | The all-or-nothing semantics for supporting the update of multiple documents in multiple shards or 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 updates won’t happen. |
| Consistency | The 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: it supports |
| Isolation | N1QL 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 details on durability. |
Horizontal Scalability and N1QL Transactions
As with any service in Couchbase, horizontal scalability is a key requirement.
All processing for N1QL transactions starts in the Query Service, and horizontal scalability is accomplished via 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, and thus doesn’t 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, is scaled up depending on the complexity of the transaction. Thus, the transaction can be scaled out quickly with additional Query Service nodes.
The above diagram highlights the following points:
- Non-transactional N1QL queries are served by any of the available Query Services. The Query Service selected by the SDK is determined by the
ns_serverand is typically cycled through using a round-robin approach. - For a N1QL transaction executed under the transaction context that Couchbase Transactions libraries provide, all subsequent DML operations (identified with
txid) are directed to the same query node, until the transaction is committed or rolled back. This is available in Java SDK 1.1.3 - The SDK-to-Query node affinity is automatically managed by the SDKs.
- Couchbase transactions support both key-value and Query Service DML operations through the Java Couchbase transactions library. You can also access other services, such as Full-Text Search and Analytics, with scan consistency requests that honor the commit under the transaction.
Where You Can Use N1QL Transactions
The N1QL transactions feature is supported anywhere you use a N1QL query.
The main requirement is that a txid value – which is returned from the START TRANSACTION command – is used with all subsequent N1QL DMLs if they’re a part of the transaction. The same goes for the final COMMIT or ROLLBACK.
If you’re using Query Workbench and cbq shell, passing txid is managed transparently. You don’t need to take any explicit actions.
CBQ Shell
Here’s an example of a N1QL transaction using cbq shell:
|
1 2 3 4 5 6 7 8 |
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 establishes a session with a specific Query Service, and ensures that all subsequent N1QL DMLs are sent with the txid (returned with TRANSACTION) to the same Query Service.
With the REST API, you need to supply the value of the txid with subsequent DMLs. For example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
>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
As part of N1QL transactions, there’s also 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. This setting means that you can use all of the N1QL enhancements that are part of the N1QL transactions feature.
Durability
Non-transactional N1QL doesn’t currently support any setting for durability. All mutations via N1QL use durability=none. With tximplicit set, the N1QL DML gets the durability
Implications: This tximplicit setting allows you to indirectly set the durability, but you should expect additional latency.
Scan Consistency
Non-transactional N1QL scan consistency is set in the SDK to any level required by the application. (See the docs on performance and consistency for more information.)
However, with tximplicit set, the DML is run with scan consistency as request_plus.
Implications: With this setting, query latency may increase. This is because the DML needs to ensure that any dependent indexes are updated to the same timestamp as the query.
Resource Usage
The 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 is much higher for the transaction span.
Implications: Mutations from N1QL transactions increase memory usage.
Guidelines for When (and When not) to Use N1QL Transactions
With the introduction of N1QL transactions, it’s important to keep in mind a few guidelines on when (and when not) to use this new capability in your application.
The general rule of thumb is to use N1QL transactions only when you need to. The Couchbase Query Service is designed to provide high throughput and low latency, both of which are impacted with transactions. This is because of the subsequent requirements and costs in terms of durability, scan consistency and resource usage.
You should also keep transaction size small in terms of the number of documents and operations affected by the mutations. And finally, we don’t recommend you use N1QL transactions for large-volume mutation use cases.
Further Resources on N1QL Transactions
English