Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

SET TRANSACTION

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

    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
    [
  {
    "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.