ROLLBACK TRANSACTION

  • Couchbase Server 7.0
    +
    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 N1QL Support for Couchbase Transactions for further information.

    This statement may only be used within a transaction.

    If you are using the Query REST API, you must set the txid query parameter to specify the transaction ID.

    If you are using the Query Workbench, you don’t need to specify the transaction ID, as long as the statement is part of a multi-statement request. When 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. 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]

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

    Example 1. Rollback a transaction

    If you want to try this example, first refer to Preparation to set up your environment.

    Transaction
    BEGIN WORK;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    UPSERT INTO test VALUES("abc2", {"a":1});
    SAVEPOINT s1; (1)
    UPDATE test AS d SET d.b = 10 WHERE d.a > 0; (2)
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;
    SAVEPOINT s2; (3)
    UPDATE test AS d SET d.b = 10, d.c = "xyz" WHERE d.a > 0; (4)
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;
    ROLLBACK TRAN TO SAVEPOINT s2; (5)
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;
    ROLLBACK WORK; (6)
    1 Set a savepoint.
    2 Update the specified documents in the test keyspace to include a second attribute b.
    3 Set a second savepoint.
    4 Update the specified documents in the test keyspace to include a third attribute named c.
    5 Roll back the transaction to the second savepoint.
    6 Roll back the entire transaction.
    Results
    [
      {
        "_sequence_num": 1,
        "_sequence_query": "BEGIN WORK;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "txid": "d9dcc721-321b-42d2-825f-a623eefc28b6"
          }
        ]
      },
      {
        "_sequence_num": 2,
        "_sequence_query": "\nUPSERT INTO test VALUES(\"abc2\", {\"a\":1});",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 3,
        "_sequence_query": "\nSAVEPOINT s1;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 4,
        "_sequence_query": "\nUPDATE test AS d SET d.b = 10 WHERE d.a > 0;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 5,
        "_sequence_query": "\nSELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "a": 1,
            "b": 10, (1)
            "id": "abc2"
          }
        ]
      },
      {
        "_sequence_num": 6,
        "_sequence_query": "\nSAVEPOINT s2;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 7,
        "_sequence_query": "\nUPDATE test AS d SET d.b = 10, d.c = \"xyz\" WHERE d.a > 0;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 8,
        "_sequence_query": "\nSELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "a": 1,
            "b": 10,
            "c": "xyz", (2)
            "id": "abc2"
          }
        ]
      },
      {
        "_sequence_num": 9,
        "_sequence_query": "\nROLLBACK TRAN TO SAVEPOINT s2;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      },
      {
        "_sequence_num": 10,
        "_sequence_query": "\nSELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;",
        "_sequence_query_status": "success",
        "_sequence_result": [
          {
            "a": 1,
            "b": 10, (3)
            "id": "abc2"
          }
        ]
      },
      {
        "_sequence_num": 11,
        "_sequence_query": "\nROLLBACK WORK;",
        "_sequence_query_status": "success",
        "_sequence_result": {
          "results": []
        }
      }
    ]
    1 Before setting the second savepoint, the abc2 document has two attributes: a and b.
    2 After setting the second savepoint and performing an update, the abc2 document has three attributes: a, b, and c.
    3 After rolling back to the second savepoint, the abc2 document again has only two attributes: a and b.
    Example 2. Check the result of Example 1

    Check the result of rolling back the transaction.

    Query
    SELECT d.*, META(d).id FROM test AS d WHERE d.a >= 0;
    Results
    {
      "results": []
    }

    Notice the abc2 document no longer exists.


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