You are viewing the documentation for a prerelease version.

N1QL Support for Couchbase Transactions

  • Couchbase Server 7.0
    +
    N1QL offers full support for Couchbase ACID transactions.

    Starting with Couchbase Server 7.0, N1QL offers full support for Couchbase ACID transactions based on optimistic concurrency. Refer to Transactions for an overview of Couchbase transactions.

    • Only DML statements are permitted within a transaction: INSERT, UPSERT, DELETE, UPDATE, MERGE, SELECT, EXECUTE FUNCTION, PREPARE, or EXECUTE.

    • The EXECUTE FUNCTION statement is only permitted in a transaction if the user-defined function does not contain any subqueries other than SELECT subqueries.

    • The PREPARE and EXECUTE statements are only permitted in a transaction for the DML statements listed above.

    All statements within a transaction are sent to the same Query node.

    Statements

    N1QL provides the following statements in support of Couchbase transactions. Refer to the documentation for each statement for more information and examples.

    Settings and Parameters

    The Query service provides settings and parameters in support of Couchbase transactions. Refer to the documentation for each parameter for more information and examples.

    • The txid request-level parameter specifies the transaction to which a statement belongs.

    • The tximplicit request-level parameter specifies that a statement is a single transaction.

    • The txstmtnum request-level parameter specifies the transaction statement number.

    • The kvtimeout request-level parameter specifies the maximum time to spend on a KV operation within a transaction before timing out.

    • The txtimeout request-level parameter, txtimeout node-level setting, and queryTxTimeout cluster-level setting specify the maximum time to spend on a transaction before timing out.

    • The atrcollection request-level parameter and atrcollection node-level setting specify where the active transaction record is stored.

    • The cleanupclientattempts and cleanuplostattempts node-level settings and the queryCleanupClientAttempts and queryCleanupLostAttempts cluster-level settings specify how expired transactions are cleaned up.

    • The cleanupwindow node-level setting and queryCleanupWindow cluster-level setting specify how frequently active transaction records are checked for cleanup.

    • The numatrs request-level parameter, numatrs node-level setting, and queryNumAtrs cluster-level setting specify the total number of active transaction records.

    In addition, the scan-consistency request-level parameter is used to specify the transactional scan consistency. Refer to Transactional Scan Consistency for details.

    Query Tools

    To create a Couchbase transaction using N1QL, you can use any of the tools that you use to run a N1QL query: the Query Workbench, the cbq shell, or the Query REST API. There are slight differences in the way these tools operate when creating Couchbase transactions. These are explained below.

    When using the Query Workbench, you can specify parameters for the Couchbase transaction using the query run-time preferences window.

    • If you are creating the Couchbase transaction one statement at a time, then once you have started the transaction, you must set the txid parameter to specify the transaction to which each subsequent statement belongs.

    • Alternatively, you can use the Run as Transaction button to run a single statement as a transaction, or a complete set of statements as a transaction all at once. In this case, you do not need to specify the txid parameter.

    When using the cbq shell, you can specify parameters for the Couchbase transaction using the \SET command.

    • If you are creating the Couchbase transaction one statement at a time, you don’t need to set the txid parameter. Once you have started a transaction, all statements within the cbq shell session are assumed to be part of the same transaction until you rollback or commit the transaction. [1]

    • Alternatively, you can use the tximplicit parameter to run a single statement as a transaction. In this case, you do not need to specify the txid parameter either.

    When using the Query REST API, you can specify parameters for the Couchbase transaction as body parameters or query parameters alongside the query statement.

    • If you are creating the Couchbase transaction one statement at a time, then once you have started the transaction, you must set the txid parameter to specify the transaction to which each subsequent statement belongs.

    • Alternatively, you can use the tximplicit parameter to run a single statement as a transaction. In this case, you do not need to specify the txid parameter.

    Monitoring

    You can monitor active Couchbase transactions using the system:transactions catalog. For more information, refer to system:transactions.

    Worked Example

    This worked example guides you through a complete Couchbase transaction session using the cbq shell. Note that you must be using cbq shell version 2.0 or above.

    Preparation

    1. Start the cbq shell and connect to the Couchbase cluster.

      $ cbq -e http://localhost:8091 -u Administrator -p password
    2. For the purposes of this worked example, create a temporary keyspace called test.

      For example, if you have already installed the travel-sample bucket, create a scope called transaction within that bucket, and then create a collection called test within that scope. Note that "transaction" is a reserved N1QL keyword, so the name of the transaction scope must be delimited with backticks ``.

      cbq> CREATE SCOPE `travel-sample`.`transaction`;
      cbq> CREATE COLLECTION `travel-sample`.`transaction`.test;
    3. Create a primary index on the test keyspace, so that you can query and update the documents in this keyspace.

      cbq> CREATE PRIMARY INDEX ON `travel-sample`.`transaction`.test;
    4. Set the default query context for the following statements, assuming that you have created the test collection within the `travel-sample`.`transaction` scope.

      cbq> \set -query_context `travel-sample`.`transaction`;
    5. If necessary, make sure the transaction timeout is set to a reasonably long duration, so that the transaction will not time out as you work through this worked example.

      cbq> \set -txtimeout "30m";

    Transaction

    1. Start the transaction.

      cbq> BEGIN WORK;

      Notice the statement returns a transaction ID. Since you are using the cbq shell, all statements within this session are assumed to be part of this transaction until you rollback or commit the transaction.

      [
          {
              "txid": "6dfa9dd6-303f-4adc-a99f-e05a44485c45"
          }
      ]
    2. Create a document in the test keyspace with the ID abc2, containing a single attribute a.

      cbq> INSERT INTO test VALUES("abc2", {"a":1});
    3. Set a savepoint:

      cbq> SAVEPOINT s1;
    4. Update the specified documents in the test keyspace to include a second attribute b.

      cbq> UPDATE test AS d SET d.b = 10 WHERE d.a > 0;
    5. Check the content of the specified documents in the test keyspace.

      cbq> SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;

      Notice the abc2 document now has two attributes: a and b.

      [
          {
              "a": 1,
              "b": 10,
              "id": "abc2"
          }
      ]
    6. Set a second savepoint.

      cbq> SAVEPOINT s2;
    7. Update the specified documents in the test keyspace to include a third attribute named c.

      cbq> UPDATE test AS d SET d.b = 10, d.c = "xyz" WHERE d.a > 0;
    8. Check the content of the specified documents in the test keyspace.

      cbq> SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;

      Notice the abc2 document now has three attributes: a, b, and c.

      [
          {
              "a": 1,
              "b": 10,
              "c": "xyz",
              "id": "abc2"
          }
      ]
    9. Roll back the transaction to the second savepoint.

      cbq> ROLLBACK TRAN TO SAVEPOINT s2;
    10. Check the content of the specified documents in the test keyspace again.

      cbq> SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;

      Notice the abc2 document again has only two attributes: a and b.

      [
          {
              "a": 1,
              "b": 10,
              "id": "abc2"
          }
      ]
    11. Roll back the entire transaction.

      cbq> ROLLBACK WORK;
    12. Check the content of the specified documents in the test keyspace again.

      cbq> SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;

      Notice the abc2 document no longer exists.

    Finishing Off

    1. You can now exit the cbq shell.

      cbq> \quit;

    1. You must be using cbq shell version 2.0 or above to use the automatic transaction ID functionality.