A newer version of this documentation is available.

View Latest

SET TRANSACTION

  • reference
    +
    The SET TRANSACTION statement enables you to specify settings for a transaction.

    Purpose

    The SET TRANSACTION statement enables you to specify settings for an ACID transaction. Refer to SQL++ Support for Couchbase Transactions for further information.

    This statement may only be used within a transaction.

    If you are using the Query REST API, you must set the txid query parameter to specify the transaction ID.

    If you are using the Query Workbench, you don’t need to specify the transaction ID, as long as the statement is part of a multi-statement request. When 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. 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]

    You may also optionally specify settings when you start the transaction using the BEGIN TRANSACTION command.

    Currently, the only available transaction setting is "isolation level read committed". This setting is enabled by default. The SET TRANSACTION statement is therefore optional and may be omitted.

    Syntax

    set-transaction ::= 'SET' 'TRANSACTION' 'ISOLATION' 'LEVEL' 'READ' 'COMMITTED'
    Syntax diagram: refer to source code listing

    Example

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

    Example 1. Specify transaction settings
    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
    [
      {
        "_sequence_num": 1,
        "_sequence_query": "-- Start the transaction\nBEGIN WORK;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51" (1)
          }
        ]
      },
      {
        "_sequence_num": 2,
        "_sequence_query": "\n\n-- Specify transaction settings\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 3,
        "_sequence_query": "\n\n-- Create a booking document\nUPSERT INTO `travel-sample`.tenant_agent_00.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});",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
    // ...
    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.