SAVEPOINT

  • Capella Operational
  • reference
    +
    The SAVEPOINT statement enables you to set a savepoint within a transaction.

    Purpose

    The SAVEPOINT statement enables you to set a savepoint within an ACID transaction. Refer to SQL++ Support for Couchbase Transactions for further information.

    This statement may only be used within a transaction.

    Syntax

    savepoint ::= 'SAVEPOINT' savepointname
    Syntax diagram: refer to source code listing
    savepointname

    An identifier specifying a name for the savepoint.

    If a savepoint with the same name already exists, the existing savepoint is replaced.

    Example

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

    Example 1. Set savepoints
    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": []
      },
      {
        "batchQuery": "\n\n-- Set a savepoint\nSAVEPOINT s1;",
        "batchQueryResult": []
      },
      {
        "batchQuery": "\n\n-- Update the booking document to include a user\nUPDATE bookings AS b\nSET b.`user` = \"0\"\nWHERE META(b).id = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
        "batchQueryResult": []
      },
      {
        "batchQuery": "\n\n-- Check the content of the booking and user\nSELECT b.*, u.name\nFROM bookings b\nJOIN users u\nON b.`user` = META(u).id\nWHERE META(b).id = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
        "batchQueryResult": [
          {
            "date": "07/24/2021",
            "flight": "WN533",
            "flighttime": 7713,
            "name": "Keon Hoppe",
            "price": 964.13,
            "route": "63986",
            "user": "0" (2)
          }
        ]
      },
      {
        "batchQuery": "\n\n-- Set a second savepoint\nSAVEPOINT s2;",
        "batchQueryResult": []
      },
      {
        "batchQuery": "\n\n-- Update the booking documents to change the user\nUPDATE bookings AS b\nSET b.`user` = \"1\"\nWHERE META(b).id = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
        "batchQueryResult": []
      },
      {
        "batchQuery": "\n\n-- Check the content of the booking and user\nSELECT b.*, u.name\nFROM bookings b\nJOIN users u\nON b.`user` = META(u).id\nWHERE META(b).id = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
        "batchQueryResult": [
          {
            "date": "07/24/2021",
            "flight": "WN533",
            "flighttime": 7713,
            "name": "Rigoberto Bernier",
            "price": 964.13,
            "route": "63986",
            "user": "1" (3)
          }
        ]
      },
      {
        "batchQuery": "\n\n-- Roll back the transaction to the second savepoint\nROLLBACK TRAN TO SAVEPOINT s2;",
        "batchQueryResult": []
      },
      {
        "batchQuery": "\n\n-- Check the content of the booking and user again\nSELECT b.*, u.name\nFROM bookings b\nJOIN users u\nON b.`user` = META(u).id\nWHERE META(b).id = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
        "batchQueryResult": [
          {
            "date": "07/24/2021",
            "flight": "WN533",
            "flighttime": 7713,
            "name": "Keon Hoppe",
            "price": 964.13,
            "route": "63986",
            "user": "0" (4)
          }
        ]
      },
      {
        "batchQuery": "\n\n-- Commit the transaction\nCOMMIT WORK;",
        "batchQueryResult": []
      }
    ]
    1 Beginning a transaction returns a transaction ID.
    2 Before setting the second savepoint, the booking document has user "0", name "Keon Hoppe".
    3 After setting the second savepoint and performing an update, the booking document has user "1", name "Rigoberto Bernier".
    4 After rolling back to the second savepoint, the booking document again has user "0", name "Keon Hoppe".