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

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.

For an overview of Couchbase transactions, refer to Transactions.
To specify transaction settings, refer to SET TRANSACTION.
To set a savepoint, refer to SAVEPOINT.
To rollback a transaction, refer to ROLLBACK TRANSACTION.
To commit a transaction, refer to 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.