BEGIN TRANSACTION

  • Couchbase Server 7.0
    +
    The BEGIN TRANSACTION statement enables you to begin a transaction.

    Purpose

    The BEGIN TRANSACTION statement enables you to begin a sequence of statements as an ACID transaction. Refer to N1QL Support for Couchbase Transactions for further information.

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

    You can also specify a single DML statement as an ACID transaction by setting the tximplicit query parameter.

    Syntax

    begin-transaction ::= ( BEGIN | START ) ( WORK | TRAN | TRANSACTION )
                          [ ISOLATION LEVEL READ COMMITTED ]
    ( 'BEGIN' | 'START' ) ( 'WORK' | 'TRAN' | 'TRANSACTION' ) ( 'ISOLATION' 'LEVEL' 'READ' 'COMMITTED' )?

    The BEGIN and START keywords are synonyms. The statement must begin with one of these keywords.

    The WORK, TRAN, and TRANSACTION keywords are synonyms. The statement must contain one of these keywords.

    Transaction Settings

    Currently, the only available transaction setting is "isolation level read committed". This setting is enabled by default. The ISOLATION LEVEL READ COMMITTED keywords are therefore optional and may be omitted.

    Return Value

    The statement returns an object containing the following property.

    txid

    The transaction ID.

    If you are using the Query REST API, you must set the txid query parameter to specify the transaction ID for any subsequent statements that form part of the same transaction.

    If you are using the Query Workbench, you don’t need to specify the transaction ID for any statements that form a part of the same transaction within a multi-statement request. If you start a transaction within a multi-statement request, all statements within the request are assumed to be part of the same transaction until you rollback or commit the transaction.

    Similarly, if you are using the cbq shell, you don’t need to specify the transaction ID for any statements that form a part of the same transaction. 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]

    Example

    If you want to try this example, first refer to Preparation to set up your environment.

    Example 1. Begin a transaction
    Transaction
    BEGIN WORK; (1)
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    UPSERT INTO test VALUES("abc2", {"a":1}); (2)
    SAVEPOINT s1;
    UPDATE test AS d SET d.b = 10 WHERE d.a > 0;
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;
    SAVEPOINT s2;
    UPDATE test AS d SET d.b = 10, d.c = "xyz" WHERE d.a > 0;
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;
    ROLLBACK TRAN TO SAVEPOINT s2;
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;
    COMMIT WORK;
    1 Begin a transaction.
    2 A statement participating in the transaction.
    Result
    [
      {
        "_sequence_num": 1,
        "_sequence_query": "BEGIN WORK;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "txid": "9d0e0818-085a-40cf-92fc-8473cd5be586" (1)
          }
        ]
      },
      {
        "_sequence_num": 2,
        "_sequence_query": "\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 3,
        "_sequence_query": "\nUPSERT INTO test VALUES(\"abc2\", {\"a\":1});",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
    ...
    ]
    1 Beginning a transaction returns the transaction ID.

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