COMMIT TRANSACTION
- reference
The COMMIT TRANSACTION statement enables you to commit a transaction.
Purpose
The COMMIT TRANSACTION statement enables you to commit an ACID transaction.
Refer to SQL++ Support for Couchbase Transactions for further information.
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]
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
commit-transaction ::= 'COMMIT' ( 'WORK' | 'TRAN' | 'TRANSACTION' )?
The WORK, TRAN, and TRANSACTION keywords are synonyms.
These keywords are optional; you may include one of these keywords, or omit them entirely.
Usage
The transaction can only be committed if the transactional durability requirements can be met. The transaction durability level is set by the request-level durability_level parameter.
If transaction durability requirements cannot be met, then a 161 error code is generated when you attempt to commit the transaction, with the message "Durability requirements are impossible to achieve". For example: bucket replication is specified, but there are not enough Data nodes available on which to store the specified number of replicas at the requested durability level.
To avoid this error, it is recommended that you add the correct number of Data nodes for the required durability level, and rebalance.
As a temporary measure, you can set the request-level durability_level parameter to "none" to turn off durability for this request, or turn off bucket replication.
Examples
If you want to try these examples, 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;[
{
"_sequence_num": 1,
"_sequence_query": "-- Start the transaction\nBEGIN WORK;",
"_sequence_query_status": "success",
"_sequence_result": [
{
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51" (1)
}
]
},
{
"_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(\"bf7ad6fa-bdb9-4099-a840-196e47179f03\", {\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 = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
"_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 = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
"_sequence_query_status": "success",
"_sequence_result": [
{
"date": "07/24/2021",
"flight": "WN533",
"flighttime": 7713,
"name": "Keon Hoppe",
"price": 964.13,
"route": "63986",
"user": "0" (2)
}
]
},
{
"_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 = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
"_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 = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
"_sequence_query_status": "success",
"_sequence_result": [
{
"date": "07/24/2021",
"flight": "WN533",
"flighttime": 7713,
"name": "Rigoberto Bernier",
"price": 964.13,
"route": "63986",
"user": "1" (3)
}
]
},
{
"_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 = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
"_sequence_query_status": "success",
"_sequence_result": [
{
"date": "07/24/2021",
"flight": "WN533",
"flighttime": 7713,
"name": "Keon Hoppe",
"price": 964.13,
"route": "63986",
"user": "0" (4)
}
]
},
{
"_sequence_num": 12,
"_sequence_query": "\n\n-- Commit the transaction\nCOMMIT WORK;",
"_sequence_query_status": "success",
"_sequence_result": {
"results": []
}
}
]| 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". |
Check the result of committing the transaction.
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";{
"date": "07/24/2021",
"flight": "WN533",
"flighttime": 7713,
"name": "Keon Hoppe",
"price": 964.13,
"route": "63986",
"user": "0" (1)
}| 1 | The booking document has been added with the attributes that were present when the transaction was committed. |
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 set a savepoint, refer to SAVEPOINT.
-
To rollback a transaction, refer to ROLLBACK TRANSACTION.
-
Blog post: Couchbase Transactions: Elastic, Scalable, and Distributed.