ROLLBACK TRANSACTION

Capella Operational

reference

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

rollback-transaction ::= 'ROLLBACK' ( 'WORK' | 'TRAN' | 'TRANSACTION' )?
                       ( 'TO' 'SAVEPOINT' savepointname )?

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

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

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

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";