BEGIN TRANSACTION

  • Capella Operational
  • reference
    +
    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 SQL++ 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. If your cluster uses private endpoints, the load balancer ensures that the same query node carries out all the steps in the transaction.

    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' )?
    Syntax diagram: refer to source code listing

    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 tab, 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
    -- Start the transaction
    BEGIN WORK;
    
    -- Specify transaction settings
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    
    -- Create a booking document
    UPSERT INTO bookings
    VALUES("bf7ad6fa-bdb9-4099-a840-196e47179f03", {
      "date": "07/24/2021",
      "flight": "WN533",
      "flighttime": 7713,
      "price": 964.13,
      "route": "63986"
    });
    
    -- Set a savepoint
    SAVEPOINT s1;
    
    -- Update the booking document to include a user
    UPDATE bookings AS b
    SET b.`user` = "0"
    WHERE META(b).id = "bf7ad6fa-bdb9-4099-a840-196e47179f03";
    
    -- Check the content of the booking and user
    SELECT b.*, u.name
    FROM bookings b
    JOIN users u
    ON b.`user` = META(u).id
    WHERE META(b).id = "bf7ad6fa-bdb9-4099-a840-196e47179f03";
    
    -- Set a second savepoint
    SAVEPOINT s2;
    
    -- Update the booking documents to change the user
    UPDATE bookings AS b
    SET b.`user` = "1"
    WHERE META(b).id = "bf7ad6fa-bdb9-4099-a840-196e47179f03";
    
    -- Check the content of the booking and user
    SELECT b.*, u.name
    FROM bookings b
    JOIN users u
    ON b.`user` = META(u).id
    WHERE META(b).id = "bf7ad6fa-bdb9-4099-a840-196e47179f03";
    
    -- Roll back the transaction to the second savepoint
    ROLLBACK TRAN TO SAVEPOINT s2;
    
    -- Check the content of the booking and user again
    SELECT b.*, u.name
    FROM bookings b
    JOIN users u
    ON b.`user` = META(u).id
    WHERE META(b).id = "bf7ad6fa-bdb9-4099-a840-196e47179f03";
    
    -- Commit the transaction
    COMMIT WORK;
    Results
    [
      {
        "batchQuery": "-- Start the transaction\nBEGIN WORK;",
        "batchQueryResult": [
          {
            "txid": "d3bbf182-1179-42ba-8900-eb20fda69e42" (1)
          }
        ]
      },
      {
        "batchQuery": "\n\n-- Specify transaction settings\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED;",
        "batchQueryResult": []
      },
      {
        "batchQuery": "\n\n-- Create a booking document\nUPSERT INTO bookings\nVALUES(\"bf7ad6fa-bdb9-4099-a840-196e47179f03\", {\n  \"date\": \"07/24/2021\",\n  \"flight\": \"WN533\",\n  \"flighttime\": 7713,\n  \"price\": 964.13,\n  \"route\": \"63986\"\n});",
        "batchQueryResult": []
      },
    // ...
    1 Beginning a transaction returns a transaction ID.

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