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 durability_level request-level parameter specifies the transactional durability level.

    • 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.

    Couchbase Transactions with the Query Workbench

    • To execute a transaction containing multiple statements, compose the sequence of statements in the Query Editor. Each statement must be terminated with a semicolon. After each statement, you must press Shift+Enter to start a new line without executing the query. You can then click Execute to execute the transaction.

    • To execute a single statement as a transaction, simply enter the statement in the Query Editor and click Run as TX.

    • In either case, you do not need to specify the txid parameter or the tximplicit parameter. If you need to specify any other parameters for the Couchbase transaction, you can use the query run-time preferences window.

    Couchbase Transactions with the cbq shell

    • To execute a transaction containing multiple statements, you can create the transaction one statement at a time. 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. In this case, you don’t need to set the txid parameter. [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.

    • You can specify parameters for the Couchbase transaction using the \SET command.

    Couchbase Transactions with the Query REST API

    • To execute a transaction containing multiple statements, you can create the transaction one statement at a time. 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.

    • You can specify parameters for the Couchbase transaction as body parameters or query parameters alongside the query statement.

    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.

    Preparation

    The worked example assumes that the supplied travel-sample bucket is installed. Refer to Sample Buckets for installation details.

    Keyspace

    For the purposes of this worked example, you must create a temporary keyspace and set the query context.

    • Query Workbench

    • CBQ Shell

    1. Create a scope called transaction within the travel-sample 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 ``.

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

      CREATE PRIMARY INDEX ON `travel-sample`.`transaction`.test;
    3. Set the default query context to `travel-sample`.`transaction`.

      The query context drop-down menu, with 'travel-sample.transaction' selected
    1. Create a scope called transaction within the travel-sample 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 ``.

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

      CREATE PRIMARY INDEX ON `travel-sample`.`transaction`.test;
    3. Set the default query context to `travel-sample`.`transaction`.

      \SET -query_context `travel-sample`.`transaction`;
    Parameters

    If necessary, set the transaction parameters for this worked example. In particular, you will turn off durability for the purposes of this example, in order to make sure that there are no problems meeting the transaction durability requirements.

    • Query Workbench

    • CBQ Shell

    1. Click the cog icon to display the Run-Time Preferences window.

    2. Open the Scan Consistency drop-down list and select not_bounded.

    3. In the Transaction Timeout box, enter 120.

    4. In the Named Parameters section, click the + button to add a named parameter.

    5. When the new named parameter appears, enter durability_level in the name box and "none" (with double quotes) in the value box.

    6. Choose Save Preferences to save the preferences and return to the Query Workbench.

    Enter the following parameters.

    \SET -txtimeout "2m"; (1)
    \SET -scan_consistency "not_bounded"; (2)
    \SET -durability_level "none"; (3)
    1 The transaction timeout.
    2 The transaction scan consistency. No scan consistency is set for individual statements within the transaction; they inherit from the transaction scan consistency.
    3 Durability level of all the mutations within the transaction.

    Transaction

    Example 1. Transaction using the Query Workbench or cbq shell

    Copy the entire sequence below and paste it into either the Query Workbench or the cbq shell. Note that you must be using cbq shell version 2.0 or above.

    Transaction
    BEGIN WORK; (1)
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED; (2)
    UPSERT INTO test VALUES("abc2", {"a":1}); (3)
    SAVEPOINT s1; (4)
    UPDATE test AS d SET d.b = 10 WHERE d.a > 0; (5)
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0; (6)
    SAVEPOINT s2; (7)
    UPDATE test AS d SET d.b = 10, d.c = "xyz" WHERE d.a > 0; (8)
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0; (9)
    ROLLBACK TRAN TO SAVEPOINT s2; (10)
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0; (11)
    COMMIT WORK; (12)
    1 Start the transaction.
    2 Specify transaction settings.
    3 Create a document in the test keyspace with the ID abc2, containing a single attribute a.
    4 Set a savepoint.
    5 Update the specified documents in the test keyspace to include a second attribute b.
    6 Check the content of the specified documents in the test keyspace.
    7 Set a second savepoint.
    8 Update the specified documents in the test keyspace to include a third attribute named c.
    9 Check the content of the specified documents in the test keyspace.
    10 Roll back the transaction to the second savepoint.
    11 Check the content of the specified documents in the test keyspace again.
    12 Commit the transaction.

    The results of running the transaction in the Query Workbench are shown below. If you are using the cbq shell, the results are formatted differently, but contain the same information.

    Results
    [
      {
        "_sequence_num": 1,
        "_sequence_query": "BEGIN WORK;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "txid": "d9dcc721-321b-42d2-825f-a623eefc28b6" (1)
          }
        ]
      },
      {
        "_sequence_num": 2,
        "_sequence_query": "\nUPSERT INTO test VALUES(\"abc2\", {\"a\":1});",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 3,
        "_sequence_query": "\nSAVEPOINT s1;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 4,
        "_sequence_query": "\nUPDATE test AS d SET d.b = 10 WHERE d.a > 0;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 5,
        "_sequence_query": "\nSELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "a": 1,
            "b": 10, (2)
            "id": "abc2"
          }
        ]
      },
      {
        "_sequence_num": 6,
        "_sequence_query": "\nSAVEPOINT s2;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 7,
        "_sequence_query": "\nUPDATE test AS d SET d.b = 10, d.c = \"xyz\" WHERE d.a > 0;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 8,
        "_sequence_query": "\nSELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "a": 1,
            "b": 10,
            "c": "xyz", (3)
            "id": "abc2"
          }
        ]
      },
      {
        "_sequence_num": 9,
        "_sequence_query": "\nROLLBACK TRAN TO SAVEPOINT s2;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 10,
        "_sequence_query": "\nSELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "a": 1,
            "b": 10, (4)
            "id": "abc2"
          }
        ]
      },
      {
        "_sequence_num": 11,
        "_sequence_query": "\nCOMMIT WORK;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      }
    ]
    1 Beginning a transaction returns a transaction ID.
    2 Before setting the second savepoint, the abc2 document has two attributes: a and b.
    3 After setting the second savepoint and performing an update, the abc2 document has three attributes: a, b, and c.
    4 After rolling back to the second savepoint, the abc2 document again has only two attributes: a and b.
    Example 2. Check the results of Example 1

    Check the result of committing the transaction.

    Query
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;
    Results
    [
      {
        "a": 1,
        "b": 10,
        "id": "abc2"
      }
    ]

    Notice the abc2 document has been added with the attributes that were present when the transaction was committed.

    Example 3. Transaction using the Query REST API

    For reference, this example shows the equivalent of Example 1 using the Query REST API.

    Begin transaction and set parameters
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "BEGIN WORK",
      "query_context": "`travel-sample`.`transaction`",
      "txtimeout": "2m",
      "scan_consistency": "request_plus",
      "durability_level": "none"
    }'
    Specify transaction settings
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Create a document with the ID "abc2", containing a single attribute "a"
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "UPSERT INTO test VALUES(\"abc2\", {\"a\":1});",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Set a savepoint
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "SAVEPOINT s1;",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Update the specified documents to include a second attribute "b"
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "UPDATE test AS d SET d.b = 10 WHERE d.a > 0;",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Check the content of the specified documents
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Set a second savepoint
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "SAVEPOINT s1;",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Update the specified documents to include a third attribute named "c"
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "UPDATE test AS d SET d.b = 10, d.c = \"xyz\" WHERE d.a > 0;",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Check the content of the specified documents
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Roll back the transaction to the second savepoint
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "ROLLBACK TRAN TO SAVEPOINT s2;",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Check the content of the specified documents again
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    Commit the transaction
    curl http://localhost:8093/query/service \
    -u Administrator:password \
    -H 'Content-Type: application/json' \
    -d '{
      "statement": "COMMIT TRANSACTION",
      "query_context": "`travel-sample`.`transaction`",
      "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
    }' (1)
    1 After beginning the transaction, each subsequent statement in the transaction must specify the transaction ID that was generated when the transaction began.

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