SQL++ Support for Couchbase Transactions

concept

SQL++ offers full support for Couchbase ACID transactions based on optimistic concurrency.

A transaction is a group of operations that are either committed to the database together, or are all undone from the database if there’s a failure. 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 than SELECT subqueries.
The PREPARE and EXECUTE 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.

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.

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:

The Query Workbench

Note that some Couchbase SDKs provide APIs to support Couchbase transactions. For further details, refer to Transactions in the Server documentation.

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.

You do not need to specify the txid parameter or the tximplicit parameter. If you need to specify any other parameters for the Couchbase transaction, you can use the Query Options window.

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 key-value operations or query statements within a transaction, users must have the relevant database access privileges, with permissions on the relevant buckets, scopes and collections.

See Configure Database Credentials for details.

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.

Context

For this worked example, set the query context to the tenant_agent_00 scope in the travel sample dataset. For more information, see Query Context.

Index

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;

Parameters

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

Click Query Options to display the Query Options 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 to save the preferences and return to the Query tab.

Transaction

Example 1. Transaction using the Query Workbench

Copy the entire sequence below and paste it into the Query Workbench, then click Run.

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;

The results of running the transaction in the Query Workbench are shown below.

Results

[
  {
    "batchQuery": "-- Start the transaction\nBEGIN WORK;",
    "batchQueryResult": [
      {
        "txid": "d3bbf182-1179-42ba-8900-eb20fda69e42" (1)
      }
    ]
  },

Beginning a transaction returns a unique transaction ID txid.

  {
    "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" (1)
      }
    ]
  },

Before setting the second savepoint, the booking document has user "0", name "Keon Hoppe".

  {
    "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" (1)
      }
    ]
  },

After setting the second savepoint and performing an update, the booking document has user "1", name "Rigoberto Bernier".

  {
    "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" (1)
      }
    ]
  },
  {
    "batchQuery": "\n\n-- Commit the transaction\nCOMMIT WORK;",
    "batchQueryResult": []
  }
]

After rolling back to the second savepoint, the booking document again has user "0", name "Keon Hoppe".

Example 2. Check the results of Example 1

Check the result of committing the transaction.

Query

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";

Results

{
  "date": "07/24/2021",
  "flight": "WN533",
  "flighttime": 7713,
  "name": "Keon Hoppe",
  "price": 964.13,
  "route": "63986",
  "user": "0" (1)
}

The booking document has been added with the attributes that were present when the transaction was committed.

Blog post: Couchbase Transactions: Elastic, Scalable, and Distributed.

SQL++ Support for Couchbase Transactions

Statements

Settings and Parameters

Query Tools

Couchbase Transactions with the Query Workbench

Monitoring

Permissions

Worked Example

Preparation

Transaction

Related Links