BEGIN TRANSACTION

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. For more information, see SQL++ Support for 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.

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' )?

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

Name	Description	Schema
nodeUUID required	The UUID of the Query node performing the transaction.	String
txid required	The transaction ID.	String

Name

Description

Schema

nodeUUID
required

The UUID of the Query node performing the transaction.

String

txid
required

The transaction ID.

String

If you’re 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’re using the Query Workbench, you do not 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’re using the cbq shell, you do not 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 see Preparation to set up your environment.

Example 1. Begin a transaction

Transaction

-- Start the transaction
BEGIN TRANSACTION;

-- 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
USE KEYS "bf7ad6fa-bdb9-4099-a840-196e47179f03"
SET b.`user` = "0";

-- Check the content of the booking and user
SELECT b.*, u.name
FROM bookings b
USE KEYS "bf7ad6fa-bdb9-4099-a840-196e47179f03"
JOIN users u
ON KEYS b.`user`;

-- Set a second savepoint
SAVEPOINT s2;

-- Update the booking documents to change the user
UPDATE bookings AS b
USE KEYS "bf7ad6fa-bdb9-4099-a840-196e47179f03"
SET b.`user` = "1";

-- Check the content of the booking and user
SELECT b.*, u.name
FROM bookings b
USE KEYS "bf7ad6fa-bdb9-4099-a840-196e47179f03"
JOIN users u
ON KEYS b.`user`;

-- Roll back the transaction to the second savepoint
ROLLBACK TRANSACTION TO SAVEPOINT s2;

-- Check the content of the booking and user again
SELECT b.*, u.name
FROM bookings b
USE KEYS "bf7ad6fa-bdb9-4099-a840-196e47179f03"
JOIN users u
ON KEYS b.`user`;

-- Commit the transaction
COMMIT TRANSACTION;

Results

[
  {
    "_sequence_num": 1,
    "_sequence_query": "-- Start the transaction\nBEGIN TRANSACTION;",
    "_sequence_query_status": "success",
    "_sequence_result": [
      {
        "nodeUUID": "b30cc79a9d942784c8a6b8968fe086ec",
        "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51" (1)
      }
    ]
  },
// ...

1	Beginning a transaction returns a transaction ID.

For an overview of Couchbase transactions, see Transactions.
To specify transaction settings, see SET TRANSACTION.
To set a savepoint, see SAVEPOINT.
To rollback a transaction, see ROLLBACK TRANSACTION.
To commit a transaction, see COMMIT TRANSACTION.
Blog post: Couchbase Transactions: Elastic, Scalable, and Distributed.

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