How can I run a transaction from a SQL++ script?

cristiscu · August 22, 2024, 11:24pm

I try to run a simple multi-statement SQL++ script like this one, using a transaction.

Whatever I do, any SET TRANSACTION, SAVEPOINT, ROLLBACK or COMMIT (after the initial BEGIN TRAN) fail with “… statement is not supported outside the transaction”.

I tried to run this script:

in the Query Editor of the Capella web UI.
in the Playground of the Capella web UI.
in a VSCode Couchbase extension Workbench.
in a VSCode Couchbase notebook.

My preference would be the last option, but what should I change? I followed all the “Preparations” as instructed.

Thanks a lot.

cristiscu · August 22, 2024, 11:27pm

This is my JSON result of your own script referenced before, from the doc, when executed in the Query Editor of the web UI:

[
  {
    "batchQuery": "-- Start the transaction\r\nBEGIN WORK;",
    "batchQueryResult": [
      {
        "nodeUUID": "3704644d066f015444eb3910341d450d",
        "txid": "ee7843a1-0aef-471e-9406-f3dfab997b67"
      }
    ]
  },
  {
    "batchQuery": "\r\n\r\n-- Specify transaction settings\r\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED;",
    "batchQueryResult": [
      {
        "code": 17002,
        "msg": "SET_TRANSACTION_ISOLATION statement is not supported outside the transaction"
      }
    ]
  }
]

eben · August 23, 2024, 1:18pm

Unfortunately multi-statement transactions are not currently supported in the Capella web UI due to some backend limitations.

I don’t know about VSCode, perhaps @deniswsrosa knows.

mreiche · August 23, 2024, 1:59pm

@cristiscu I believe it’s only possible via an SDK due to required client-side processing.

cristiscu · August 24, 2024, 12:32pm

@eben @mreiche thanks, guys
I also tried it on the local Community Edition installation, I get the same error.
Due to the existing doc, I can only assume this may be fixed in time, but not soon enough…

mreiche · August 24, 2024, 1:42pm

Can you point me to the documentation you are following? I don’t think it can work in the web UI - because of the client-side processing required. (unless they have added specific transaction handling processing).

cristiscu · August 24, 2024, 6:04pm

The link was there in my first post: https://docs.couchbase.com/cloud/n1ql/n1ql-language-reference/transactions.html#transaction

And the same SQL++ script can be found in other 4-5 doc pages talking about transactions.

dh · August 24, 2024, 10:32pm

You should be able to run the script via the cbq-shell if you’ve a local CE install.

Try manually first to verify perhaps?
e.g.

cbq> begin work;
{
    "requestID": "d6c5eda7-568a-4ff2-801c-959379821b15",
    "signature": "json",
    "results": [
    {
        "nodeUUID": "366d019c74290d2af974b7cd3092d54f",
        "txid": "addde394-92b4-4a1e-8af8-ae543c6cbb25"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "79.642764ms",
        "executionTime": "73.807839ms",
        "resultCount": 1,
        "resultSize": 118,
        "serviceLoad": 3,
        "transactionElapsedTime": "27.281911ms",
        "transactionRemainingTime": "1m59.97270126s"
    }
}
cbq> set transaction isolation level read committed;
{
    "requestID": "a77ca0df-eb95-4db1-9fad-5dc1102a0542",
    "signature": "json",
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "1.221257ms",
        "executionTime": "964.277µs",
        "resultCount": 0,
        "resultSize": 0,
        "serviceLoad": 0,
        "transactionElapsedTime": "6.821475895s",
        "transactionRemainingTime": "1m53.178507012s"
    }
}
cbq> rollback;
{
    "requestID": "56a62937-964c-4000-9f16-f4196c3b13a2",
    "signature": "json",
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "2.997623ms",
        "executionTime": "2.816776ms",
        "resultCount": 0,
        "resultSize": 0,
        "serviceLoad": 0,
        "transactionElapsedTime": "13.764240079s"
    }
}

Or via the rest end point - https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/transactions.html#query-tools - but in particular the BEGIN WORK responds with a txid value. This must be supplied to the REST endpoint with each subsequent request that is part of the transaction.

e.g:

$  /usr/bin/curl -su $UID_PWD http://192.168.2.22:8093/query/service -d 'pretty=true&txtimeout=75s&statement=begin work'
{
    "requestID": "062a5521-2e31-4482-9a4e-702e63f71fac",
    "signature": "json",
    "results": [
    {
        "nodeUUID": "366d019c74290d2af974b7cd3092d54f",
        "txid": "75f64e0b-d015-4520-8325-a08336962337"
    }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "28.824627ms",
        "executionTime": "28.699877ms",
        "resultCount": 1,
        "resultSize": 118,
        "serviceLoad": 3,
        "transactionElapsedTime": "28.186275ms",
        "transactionRemainingTime": "1m14.971797119s"
    }
}
$  /usr/bin/curl -su $UID_PWD http://192.168.2.22:8093/query/service -d 'pretty=true&statement=set transaction isolation level read committed&txid=75f64e0b-d015-4520-8325-a08336962337'
{
    "requestID": "fff35c7f-e83a-4eb3-94d3-4afd1f668c1c",
    "signature": "json",
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "377.148µs",
        "executionTime": "320.811µs",
        "resultCount": 0,
        "resultSize": 0,
        "serviceLoad": 0,
        "transactionElapsedTime": "41.811171264s",
        "transactionRemainingTime": "33.188822407s"
    }
}
$  /usr/bin/curl -su $UID_PWD http://192.168.2.22:8093/query/service -d 'pretty=true&statement=rollback&txid=75f64e0b-d015-4520-8325-a08336962337'
{
    "requestID": "6b1bb647-8ff8-41c0-aeb4-8289005e842d",
    "signature": "json",
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "794.224µs",
        "executionTime": "592.333µs",
        "resultCount": 0,
        "resultSize": 0,
        "serviceLoad": 0,
        "transactionElapsedTime": "56.515037121s"
    }
}

Could you verify either of these approaches work for you?

FWIW, I verified on:

cbq> select ds_version();
{
    "requestID": "e2e6a017-de02-4699-8b76-8066e502b2a8",
    "signature": {
        "$1": "string"
    },
    "results": [
    {
        "$1": "7.6.2-3721-community"

And confirmed in the Query Workbench:

[
  {
    "_sequence_num": 1,
    "_sequence_query": "begin work;",
    "_sequence_query_status": "success",
    "_sequence_result": [
      {
        "txid": "5d729712-2e08-49ef-85ea-db6cf58c6540"
      }
    ]
  },
  {
    "_sequence_num": 2,
    "_sequence_query": "\nset transaction isolation level read committed;",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  },
  {
    "_sequence_num": 3,
    "_sequence_query": "\nrollback;",
    "_sequence_query_status": "success",
    "_sequence_result": {
      "results": []
    }
  }
]

cristiscu · August 24, 2024, 10:46pm

@dh Thanks, but I will publish in just 2-3 days an Udemy course about “SQL++ for JSON in Couchbase”, and I want to use on purpose only pure SQL++ queries. No SDKs, no CLI, no REST APIs, no other languages or web UI functionality.

It’s looking great so far, with 99% provided in your Couchbase extension for VSCode notebooks I still have to decide if I’ll talk about transactions at all. I may include them as they are, but not execute the statements (my whole course is hands-on, with live demos), and just mention they are not yet supported as an SQL++ script (you should fix this, guys!!).

(BTW, if anyone from Couchbase interested in free coupons, proof check or anything else, please DM me, it will be my pleasure . Click here for my Udemy instructor profile)

dh · August 25, 2024, 11:26am

OK. But regardless the tooling has to supply the txid along with the request. Couchbase Query is stateless and it is the only mechanism to tie transaction requests to the transaction.

As illustrated, the cbq-shell does this for you automatically, just as the CE edition UI’s QWB which I demonstrated. SDKs will do this too. My primary concern was your claim that the CE QWB didn’t work - but you never noted the version - in case it was a bug we’d fixed (we could then perhaps identify it), hence noting the version via the ds_version() SELECT.

(@eben already noted the Capella UI lacks this capability.)

cristiscu · August 26, 2024, 1:32am

I never claimed this, as I never mentioned the CE QWB

dh · August 26, 2024, 8:16am

Indeed, sorry @cristiscu. I incorrectly assumed from:

and that initially you’d started with:

that you’d be using the QWB in the CE install (UI for UI).

When you state:

I presume you’re indicating Capella then? For it does work in on-premises installations. Or are you indicating something else that doesn’t work?

deniswsrosa · August 27, 2024, 1:55pm

Jetbrains run as a transaction when you execute more than one query. This feature is not implemented yet on vscode.

system · November 25, 2024, 1:56pm

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
CouchBase Java Client and Handling Transactions Couchbase Server	1	4123	September 7, 2013
Couchbase Server 4.0 SQL++	1	1921	June 2, 2015
Transactions like in relational database Python SDK	1	1321	September 20, 2018
About Transaction SQL++ in Query Workbench SQL++ n1ql	6	439	April 1, 2024
Create, execute and delete prepared statements in cbq SQL++ n1ql	4	2834	January 27, 2016

How can I run a transaction from a SQL++ script?

Related topics