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.

ROLLBACK TRANSACTION

  • Capella Operational
  • reference
March 23, 2025
+ 12
The ROLLBACK TRANSACTION statement enables you to rollback a transaction.

Purpose

The ROLLBACK TRANSACTION statement enables you to rollback an ACID transaction. You can rollback the entire transaction, or rollback to a previous savepoint. Refer to SQL++ Support for Couchbase Transactions for further information.

This statement may only be used within a transaction.

When you rollback the entire transaction, this statement removes all savepoints within the transaction.

If you are using the cbq shell, and a transaction fails for any reason, you must use the ROLLBACK TRANSACTION statement to remove the transaction context and reset the transaction ID.

Syntax

ebnf
ViewCopy
rollback-transaction ::= 'ROLLBACK' ( 'WORK' | 'TRAN' | 'TRANSACTION' )?
                       ( 'TO' 'SAVEPOINT' savepointname )?
Syntax diagram: refer to source code listing

The WORK, TRAN, and TRANSACTION keywords are synonyms. These keywords are optional; you may include one of these keywords, or omit them entirely.

Rollback to a Savepoint

The TO SAVEPOINT clause enables you to rollback to a specified savepoint. This clause is optional. If omitted, the entire transaction is rolled back.

savepointname

An identifier specifying a name for the savepoint.

Examples

If you want to try these examples, first refer to Preparation to set up your environment.

Example 1. Rollback a transaction
Transaction
sql++
Copy
-- Start the transaction
BEGIN WORK;

-- Specify transaction settings
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Create a booking document
UPSERT INTO `travel-sample`.tenant_agent_00.bookings
VALUES("42641d7a-cde3-4a4d-bfd5-fec321510f70", {
  "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 `travel-sample`.tenant_agent_00.bookings AS b
SET b.`user` = "0"
WHERE META(b).id = "42641d7a-cde3-4a4d-bfd5-fec321510f70";

-- Check the content of the booking and user
SELECT b.*, u.name
FROM `travel-sample`.tenant_agent_00.bookings b
JOIN `travel-sample`.tenant_agent_00.users u
ON b.`user` = META(u).id
WHERE META(b).id = "42641d7a-cde3-4a4d-bfd5-fec321510f70";

-- Set a second savepoint
SAVEPOINT s2;

-- Update the booking documents to change the user
UPDATE `travel-sample`.tenant_agent_00.bookings AS b
SET b.`user` = "1"
WHERE META(b).id = "42641d7a-cde3-4a4d-bfd5-fec321510f70";

-- Check the content of the booking and user
SELECT b.*, u.name
FROM `travel-sample`.tenant_agent_00.bookings b
JOIN `travel-sample`.tenant_agent_00.users u
ON b.`user` = META(u).id
WHERE META(b).id = "42641d7a-cde3-4a4d-bfd5-fec321510f70";

-- 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 `travel-sample`.tenant_agent_00.bookings b
JOIN `travel-sample`.tenant_agent_00.users u
ON b.`user` = META(u).id
WHERE META(b).id = "42641d7a-cde3-4a4d-bfd5-fec321510f70";

-- Roll back the entire transaction
ROLLBACK WORK;
Results
json
Copy
[
  {
    "_sequence_num": 1,
    "_sequence_query": "-- Start the transaction\nBEGIN WORK;",
    "_sequence_query_status": "success",
    "_sequence_result": [
      {
        "txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
      }
    ]
  },
  {
    "_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(\"42641d7a-cde3-4a4d-bfd5-fec321510f70\", {\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": []
    }
  },
  {
    "_sequence_num": 4,
    "_sequence_query": "\n\n-- Set a savepoint\nSAVEPOINT s1;",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  },
  {
    "_sequence_num": 5,
    "_sequence_query": "\n\n-- Update the booking document to include a user\nUPDATE `travel-sample`.tenant_agent_00.bookings AS b\nSET b.`user` = \"0\"\nWHERE META(b).id = \"42641d7a-cde3-4a4d-bfd5-fec321510f70\";",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  },
  {
    "_sequence_num": 6,
    "_sequence_query": "\n\n-- Check the content of the booking and user\nSELECT b.*, u.name\nFROM `travel-sample`.tenant_agent_00.bookings b\nJOIN `travel-sample`.tenant_agent_00.users u\nON b.`user` = META(u).id\nWHERE META(b).id = \"42641d7a-cde3-4a4d-bfd5-fec321510f70\";",
    "_sequence_query_status": "success",
    "_sequence_result": [
      {
        "date": "07/24/2021",
        "flight": "WN533",
        "flighttime": 7713,
        "name": "Keon Hoppe",
        "price": 964.13,
        "route": "63986",
        "user": "0" (1)
      }
    ]
  },
  {
    "_sequence_num": 7,
    "_sequence_query": "\n\n-- Set a second savepoint\nSAVEPOINT s2;",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  },
  {
    "_sequence_num": 8,
    "_sequence_query": "\n\n-- Update the booking documents to change the user\nUPDATE `travel-sample`.tenant_agent_00.bookings AS b\nSET b.`user` = \"1\"\nWHERE META(b).id = \"42641d7a-cde3-4a4d-bfd5-fec321510f70\";",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  },
  {
    "_sequence_num": 9,
    "_sequence_query": "\n\n-- Check the content of the booking and user\nSELECT b.*, u.name\nFROM `travel-sample`.tenant_agent_00.bookings b\nJOIN `travel-sample`.tenant_agent_00.users u\nON b.`user` = META(u).id\nWHERE META(b).id = \"42641d7a-cde3-4a4d-bfd5-fec321510f70\";",
    "_sequence_query_status": "success",
    "_sequence_result": [
      {
        "date": "07/24/2021",
        "flight": "WN533",
        "flighttime": 7713,
        "name": "Rigoberto Bernier",
        "price": 964.13,
        "route": "63986",
        "user": "1" (2)
      }
    ]
  },
  {
    "_sequence_num": 10,
    "_sequence_query": "\n\n-- Roll back the transaction to the second savepoint\nROLLBACK TRAN TO SAVEPOINT s2;",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  },
  {
    "_sequence_num": 11,
    "_sequence_query": "\n\n-- Check the content of the booking and user again\nSELECT b.*, u.name\nFROM `travel-sample`.tenant_agent_00.bookings b\nJOIN `travel-sample`.tenant_agent_00.users u\nON b.`user` = META(u).id\nWHERE META(b).id = \"42641d7a-cde3-4a4d-bfd5-fec321510f70\";",
    "_sequence_query_status": "success",
    "_sequence_result": [
      {
        "date": "07/24/2021",
        "flight": "WN533",
        "flighttime": 7713,
        "name": "Keon Hoppe",
        "price": 964.13,
        "route": "63986",
        "user": "0" (3)
      }
    ]
  },
  {
    "_sequence_num": 12,
    "_sequence_query": "\n\n-- Roll back the entire transaction\nROLLBACK WORK;",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  }
]
1 Before setting the second savepoint, the booking document has user "0", name "Keon Hoppe".
2 After setting the second savepoint and performing an update, the booking document has user "1", name "Rigoberto Bernier".
3 After rolling back to the second savepoint, the booking document again has user "0", name "Keon Hoppe".
Example 2. Check the result of Example 1

Check the result of rolling back the transaction.

Query
sql++
Copy
SELECT b.*, u.name
FROM `travel-sample`.tenant_agent_00.bookings b
JOIN `travel-sample`.tenant_agent_00.users u
ON b.`user` = META(u).id
WHERE META(b).id = "42641d7a-cde3-4a4d-bfd5-fec321510f70";
Results
json
Copy
{
  "results": []
}

Notice the booking document no longer exists.