Any way to know if a connection is already inside a transaction?

[daattali]

Ideally, every call to dbBegin() will be followed by exactly one call to either rollback or commit.

However, sometimes in complex situations where the transaction is happening throughout several functions, it might not be 100% certain that a rollback is called in case of an error. In that case, the next time I try to call dbBegin(), I'd like to clear any active transactions because if a previous transaction failed part-way through then I'm stuck and cannot start a new transaction.

To do this, it would be necessary to have a function such as DBI::isActiveTransaction() or similar

[daattali]

To show a concrete very simple example:

Suppose I have a global database connection con and two functions f1() and f2().

con <- DBI::dbConnect(...)

f1 <- function() {
  DBI::dbBegin(con)
  some_function_calls_that_result_an_in_error()
  DBI::dbCommit(con)
}

f2 <- function() {
  DBI::dbBegin(con)
  some_function_calls()
  DBI::dbCommit(con)
}

f1()
f2()

What would happen is that when I call f2(), it would fail because dbBegin() can't complete since a transaction is already in progress. The ideal solution here is to wrap the code in a try-catch and to roll back if an error occurs, I know that. But what if the transaction begins and ends in someone else's code where I have no control over it? It would be useful to have the ability to do

if (DBI::isTransactionActive(con)) DBI::dbRollback(con)

[r2evans]

(Up front, I'm guessing you already know this, and are asking for a stub for each driver to implement the actual method.)

It depends on the DBMS.

• SQL Server, if select @@TRANCOUNT is greater than 0 then you are in a transaction;
• Postgres, if select txid_current() is the same when repeated, then you are in a transaction;
• SQLite: I think RSQLite would need to add sqlite3_get_autocommit to the C functions it uses, since that's the (a?) canary for autocommit/in-transaction.

not sure about others ...

[daattali]

Thanks. Yeah, I was more wondering about a generic DBI function that works across the different implementations

[krlmlr]

I'd suggest to use dbWithTransaction() in your own code to ensure that transactions are finalized.

If you need to interact with code that you don't control, and this code behaves erratically regarding transactions, that's tough. Can you try(dbRollback()) unconditionally?

DBI knows if dbBegin() has been called or not, we could perhaps track and return this information even without changing all the drivers.

[benscarlson]

I just found this thread when I was also attempting to determine if there is an active transaction. I use RSQlite/DBI and would love to have isActiveTransaction() or something like that.

[krlmlr]

Thanks for the suggestions. I don't think this is a good fit for DBI at this time, please query the transaction status manually if you really need to.