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
- 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.
-- 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;
[
{
"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" . |
Related Links
-
For an overview of Couchbase transactions, refer to Transactions.
-
To begin a transaction, refer to BEGIN TRANSACTION.
-
To specify transaction settings, refer to SET TRANSACTION.
-
To rollback a transaction, refer to ROLLBACK TRANSACTION.
-
To commit a transaction, refer to COMMIT TRANSACTION.
-
Blog post: Couchbase Transactions: Elastic, Scalable, and Distributed.