Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

Create Couchbase Transactions with SQL++

  • Capella Operational
  • how-to
    +
    How to create Couchbase transactions using SQL++.

    Introduction

    Couchbase transactions enable you to carry out ACID (atomic, consistent, isolated, and durable) actions on the database. This how-to guide covers SQL++ support for Couchbase transactions. Some SDKs also support Couchbase transactions. For more information, see Related Links.

    Only DML (data modification language) statements are permitted within a transaction: INSERT, UPSERT, DELETE, UPDATE, MERGE, SELECT, EXECUTE FUNCTION, PREPARE, or EXECUTE.

    If you want to try out the examples in this section, follow the instructions given in Create an Account and Deploy Your Free Tier Operational Cluster to create a free account, deploy a cluster, and load a sample dataset. To create a transaction using SQL++ in Couchbase Capella, you must use the cbq shell.

    Please note that the examples in this guide will alter the data in your sample database. To restore your sample data, remove and reinstall the travel sample data. Refer to Import Data with the Capella UI for details.

    Transaction Parameters

    You can specify various settings and parameters to control how transactions work. You can access transaction settings and parameters through the cbq shell.

    • CBQ Shell

    To specify parameters for a Couchbase transaction, use the \SET command.

    The following example shows transaction parameters for the examples on this page.

    \SET -txtimeout "2m"; (1)
\SET -scan_consistency "not_bounded"; (2)
\SET -durability_level "none"; (3)
    1 The transaction timeout.
    2 The transaction scan consistency.
    3 Durability level of all the mutations within the transaction.

    Click the View button to see this code in context.

    For more information, see Transaction Settings and Parameters.

    Single Statement Transactions

    You can create a Couchbase transaction containing a single DML statement.

    • CBQ Shell

    To execute a single statement as a transaction, set the tximplicit parameter to true.

    Context

    Set the query context to the inventory scope in the travel sample dataset. For more information, see Setting the Query Context.

    Query
    \SET -tximplicit true;

UPDATE hotel
SET price = "from £89"
WHERE name = "Glasgow Grand Central";

    For more information, see Query Tools.

    Multiple Statement Transactions

    A Couchbase transaction may contain multiple DML statements. In this case, you must use SQL++ transaction statements to support the transaction:

    • CBQ Shell

    To execute a transaction containing multiple statements, 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.

    You must be using cbq shell version 2.0 or above to use the automatic transaction ID functionality.

    For a worked example showing a complete transaction using SQL++, see Transaction Worked Example. Individual SQL++ transaction statements are described in the sections below.

    Begin a Transaction

    To start a transaction, use the BEGIN TRANSACTION statement.

    The following statement begins a transaction.

    BEGIN TRANSACTION;

    Click the View button to see this code in context.

    Result
    {
    "nodeUUID": "9803422b02fd9e6c9e7156b8ddb2d840",
    "txid": "fab2bd20-322e-4ed0-bc4e-035db995f349" (1)
}
    1 Beginning a transaction returns the transaction ID.

    For more information, see BEGIN TRANSACTION.

    Specify Transaction Settings

    To specify transaction settings, use the SET TRANSACTION statement.

    Currently, the only available transaction setting is ISOLATION LEVEL READ COMMITTED. This setting is enabled by default. The SET TRANSACTION statement is therefore optional and may be omitted.

    The following statement specifies transaction settings.

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    Click the View button to see this code in context.

    For more information, see SET TRANSACTION.

    Set a Savepoint

    To set a savepoint within a transaction, use the SAVEPOINT statement and specify a name for the savepoint.

    The following statement sets a savepoint.

    SAVEPOINT s1;

    Click the View button to see this code in context.

    For more information, see SAVEPOINT.

    Roll Back a Transaction

    To roll back a transaction, use the ROLLBACK TRANSACTION statement.

    By default, this statement rolls back the entire transaction. If you want to roll back to a savepoint, use the TO SAVEPOINT keywords and specify the savepoint name.

    The following statement rolls back a transaction to a specified savepoint.

    ROLLBACK TRANSACTION TO SAVEPOINT s2;

    Click the View button to see this code in context.

    For more information, see ROLLBACK TRANSACTION.

    Commit a Transaction

    To commit a transaction, use the COMMIT TRANSACTION statement.

    The following statement commits a transaction.

    COMMIT TRANSACTION;

    Click the View button to see this code in context.

    For more information, see COMMIT TRANSACTION.

    Reference and explanation:

    Online transaction simulator:

    Transactions with SDKs: