SQL++ Support for Couchbase Transactions
- concept
SQL++ offers full support for Couchbase ACID transactions.
Starting with Couchbase Server 7.0, SQL++ offers full support for Couchbase ACID transactions based on optimistic concurrency. Refer to Transactions for an overview of Couchbase transactions.
-
Only DML statements are permitted within a transaction: INSERT, UPSERT, DELETE, UPDATE, MERGE, SELECT, EXECUTE FUNCTION, PREPARE, or EXECUTE.
-
The
EXECUTE FUNCTION
statement is only permitted in a transaction if the user-defined function does not contain any subqueries other thanSELECT
subqueries. -
The
PREPARE
andEXECUTE
statements are only permitted in a transaction for the DML statements listed above.
All statements within a transaction are sent to the same Query node.
Statements
SQL++ provides the following statements in support of Couchbase transactions. Refer to the documentation for each statement for more information and examples.
-
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.
-
To commit a transaction, refer to COMMIT TRANSACTION.
Settings and Parameters
The Query service provides settings and parameters in support of Couchbase transactions. Refer to the documentation for each parameter for more information and examples.
Setting / Parameter | Description |
---|---|
txid request-level parameter |
Specifies the transaction to which a statement belongs. |
tximplicit request-level parameter |
Specifies that a statement is a single transaction. |
txstmtnum request-level parameter |
Specifies the transaction statement number. |
kvtimeout request-level parameter |
Specifies the maximum time to spend on a KV operation within a transaction before timing out. |
durability_level request-level parameter |
Specifies the transactional durability level. |
txtimeout request-level parameter |
Specify the maximum time to spend on a transaction before timing out. |
atrcollection request-level parameter |
Specify where the active transaction record is stored. |
cleanupclientattempts node-level setting cleanuplostattempts node-level setting |
Specify how expired transactions are cleaned up. |
cleanupwindow node-level setting |
Specify how frequently active transaction records are checked for cleanup. |
numatrs request-level parameter |
Specify the total number of active transaction records. |
In addition, the scan-consistency request-level parameter is used to specify the transactional scan consistency. Refer to Transactional Scan Consistency for details.
Query Tools
To create a Couchbase transaction using SQL++, you can use any of the tools that you use to run a SQL++ query: the Query Workbench, the cbq shell, or the Query REST API. There are slight differences in the way these tools operate when creating Couchbase transactions. These are explained in the sections below.
Note that some Couchbase SDKs provide APIs to support Couchbase transactions. For further details, refer to Transactions.
Couchbase Transactions with the Query Workbench
-
To execute a transaction containing multiple statements, compose the sequence of statements in the Query Editor. Each statement must be terminated with a semicolon. After each statement, you must press Shift+Enter to start a new line without executing the query. You can then click Execute to execute the transaction.
-
To execute a single statement as a transaction, simply enter the statement in the Query Editor and click Run as TX.
-
In either case, you do not need to specify the
txid
parameter or thetximplicit
parameter. If you need to specify any other parameters for the Couchbase transaction, you can use the query run-time preferences window.
Couchbase Transactions with the cbq shell
-
To execute a transaction containing multiple statements, you can create the transaction one statement at a time. 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. In this case, you don’t need to set the
txid
parameter. [1] -
Alternatively, you can use the
tximplicit
parameter to run a single statement as a transaction. In this case, you do not need to specify thetxid
parameter either. -
You can specify parameters for the Couchbase transaction using the
\SET
command.
Couchbase Transactions with the Query REST API
-
To execute a transaction containing multiple statements, you can create the transaction one statement at a time. Once you have started the transaction, you must set the
txid
parameter to specify the transaction to which each subsequent statement belongs. -
Alternatively, you can use the
tximplicit
parameter to run a single statement as a transaction. In this case, you do not need to specify thetxid
parameter. -
You can specify parameters for the Couchbase transaction as body parameters or query parameters alongside the query statement.
Monitoring
You can monitor active Couchbase transactions using the system:transactions
catalog.
For more information, refer to system:transactions.
Permissions
When developing a transaction with an SDK, the transaction may contain a mixture of key-value operations and query statements.
To execute a key-value operation within a transaction, users must have the relevant Administrative or Data RBAC roles, and permissions on the relevant buckets, scopes and collections.
Similarly, to run a query statement within a transaction, users must have the relevant Administrative or Query & Index RBAC roles, and permissions on the relevant buckets, scopes and collections.
Refer to Roles for details.
Query Mode
When a transaction executes a query statement, the transaction enters query mode, which means that the query is executed with the user’s query permissions.
Any key-value operations which are executed by the transaction after the query statement are also executed with the user’s query permissions.
These may or may not be different to the user’s data permissions; if they are different, you may get unexpected results.
|
Worked Example
This worked example guides you through a complete Couchbase transaction session using SQL++.
Preparation
The worked example assumes that the supplied travel-sample
bucket is installed.
Refer to Sample Buckets for installation details.
For the purposes of this worked example, you must create a primary index in the keyspace you will be using.
Create a primary index on `travel-sample`.tenant_agent_00.bookings
, so that you can query and update the documents in this keyspace.
CREATE PRIMARY INDEX ON bookings;
If necessary, set the transaction parameters for this worked example. In particular, you will turn off durability for the purposes of this example, in order to make sure that there are no problems meeting the transaction durability requirements.
-
Query Workbench
-
CBQ Shell
-
Click the cog icon to display the Run-Time Preferences window.
-
Open the Scan Consistency drop-down list and select not_bounded.
-
In the Transaction Timeout box, enter
120
. -
In the Named Parameters section, click the + button to add a named parameter.
-
When the new named parameter appears, enter
durability_level
in the name box and"none"
(with double quotes) in the value box. -
Choose Save Preferences to save the preferences and return to the Query Workbench.
Enter the following parameters.
\SET -txtimeout "2m"; (1)
\SET -scan_consistency "not_bounded"; (2)
\SET -durability_level "none"; (3)
1 | The transaction timeout. |
2 | The transaction scan consistency. No scan consistency is set for individual statements within the transaction; they inherit from the transaction scan consistency. |
3 | Durability level of all the mutations within the transaction. |
Transaction
Copy the entire sequence below and paste it into either the Query Workbench or the cbq shell. Note that you must be using cbq shell version 2.0 or above.
-- 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;
The results of running the transaction in the Query Workbench are shown below. If you are using the cbq shell, the results are formatted differently, but contain the same information.
[
{
"_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. |
For reference, this example shows the equivalent of Example 1 using the Query REST API.
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "BEGIN WORK",
"query_context": "`travel-sample`.tenant_agent_00",
"txtimeout": "2m",
"scan_consistency": "request_plus",
"durability_level": "none"
}'
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "SET TRANSACTION ISOLATION LEVEL READ COMMITTED;",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "UPSERT INTO bookings VALUES(\"bf7ad6fa-bdb9-4099-a840-196e47179f03\", {
\"date\": \"07/24/2021\",
\"flight\": \"WN533\",
\"flighttime\": 7713,
\"price\": 964.13,
\"route\": \"63986\"
});",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "SAVEPOINT s1;",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "UPDATE bookings AS b
SET b.`user` = \"0\"
WHERE META(b).id = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "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\";",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "SAVEPOINT s2;",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "UPDATE bookings AS b
SET b.`user` = \"1\"
WHERE META(b).id = \"bf7ad6fa-bdb9-4099-a840-196e47179f03\";",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "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\";",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "ROLLBACK TRAN TO SAVEPOINT s2;",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "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\";",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
curl http://localhost:8093/query/service \
-u Administrator:password \
-H 'Content-Type: application/json' \
-d '{
"statement": "COMMIT TRANSACTION",
"query_context": "`travel-sample`.tenant_agent_00",
"txid": "d81d9b4a-b758-4f98-b007-87ba262d3a51"
}' (1)
1 | After beginning the transaction, each subsequent statement in the transaction must specify the transaction ID that was generated when the transaction began. |