Understanding Transactions in ODBC

[markmaker]

Hi

thank you for this superb project!

Transactions in ODBC

I have trouble understanding how transactions work in ODBC in general, and with pslqODBC specifically.

ODBC creator Microsoft describes the behavior here: Performing Transactions in ODBC. However it is not entirely clear to me, whether that is ODBC intended standard behavior, or just their SQL Server's.

Most notably, there is no begin transaction in ODBC. It seems the ODBC driver is supposed to start a transaction implicitly when the first statement is executed after either setting autocommit off, or after SQLEndTran().

This seems very bad ODBC API design, especially as there seems no way to ask the ODBC driver whether a transaction is open or not (?).

If I'm not mistaken, I see this implemented in psqlODBC, here:

psqlodbc/connection.c

Line 1802 in 9cd50a5

issue_begin = ((flag & GO_INTO_TRANSACTION) != 0 && !CC_is_in_trans(self)),

But I sometimes still got errors that a transaction is not open. I can't reproduce it right now, but I feel I'm missing something.

What I'd like to achieve

I have (basically) two kinds of db access:

1. True business logic transactions, properly run in serializable isolation level. I would like full control of the begin and the commit.
2. Data base reads mostly for display (GUI) purposes. It doesn't matter whether a data record version is slightly stale, or if portions of the GUI come from different commits. So the read_committed isolation level is fine. This mode should be as light-weight as possible, and avoid blocking any of the business logic transactions (1) for long.

Reliably switching between the two makes it difficult. I don't want any lingering transactions, that were auto-opened after the last commit.

I wonder if I should switch autocommit ON after each SQLEndTran(). And switch it OFF to actually "simulate" the begin of the transaction (it will still only be initiated when the first statement is issued). To make matters worse, I would also have to switch between concurrency isolation levels. How? In what order?

I wonder if autocommit is slow. I imagine performing so many commits could be heavy. Maybe I should not use it, but commit the transaction after each top level GUI function call (or similar).

Any advice?

Thanks,
_Mark

[davecramer]

Hi

thank you for this superb project!

Transactions in ODBC

I have trouble understanding how transactions work in ODBC in general, and with pslqODBC specifically.

ODBC creator Microsoft describes the behavior here: Performing Transactions in ODBC. However it is not entirely clear to me, whether that is ODBC intended standard behavior, or just their SQL Server's.

Most notably, there is no begin transaction in ODBC. It seems the ODBC driver is supposed to start a transaction implicitly when the first statement is executed after either setting autocommit off, or after SQLEndTran().

This seems very bad ODBC API design, especially as there seems no way to ask the ODBC driver whether a transaction is open or not (?).

If I'm not mistaken, I see this implemented in psqlODBC, here:

psqlodbc/connection.c

Line 1802 in 9cd50a5

issue_begin = ((flag & GO_INTO_TRANSACTION) != 0 && !CC_is_in_trans(self)),
But I sometimes still got errors that a transaction is not open. I can't reproduce it right now, but I feel I'm missing something.

What I'd like to achieve

I have (basically) two kinds of db access:

1. True business logic transactions, properly run in serializable isolation level. I would like full control of the begin and the commit.

see

psqlodbc/results.c

Line 4334 in 9cd50a5

qflag |= GO_INTO_TRANSACTION;

There is a way to see if you are in a transaction. Also you are correct this is how you control transactions

3. Data base reads mostly for display (GUI) purposes. It doesn't matter whether a data record version is slightly stale, or if portions of the GUI come from different commits. So the read_committed isolation level is fine. This mode should be as light-weight as possible, and avoid blocking any of the business logic transactions (1) for long.>
   Reliably switching between the two makes it difficult. I don't want any lingering transactions, that were auto-opened after the last commit.

I wonder if I should switch autocommit ON after each SQLEndTran(). And switch it OFF to actually "simulate" the begin of the transaction (it will still only be initiated when the first statement is issued). To make matters worse, I would also have to switch between concurrency isolation levels. How? In what order?

I wonder if autocommit is slow. I imagine performing so many commits could be heavy. Maybe I should not use it, but commit the transaction after each top level GUI function call (or similar).

Autocommit is not really slow. There is nothing committed in a read.

Dave

Any advice?

Thanks, _Mark

[markmaker]

Thanks @davecramer,

There is a way to see if you are in a transaction

From outside the ODBC driver? How?

Also you are correct this is how you control transactions

So to begin a transaction, I would

1. SQLSetConnectAttr(... SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF ...);
2. SQLSetConnectAttr( ... SQL_ATTR_TXN_ISOLATION, SQL_TXN_SERIALIZABLE ...);

To commit I would...

1. SQLEndTran(... SQL_COMMIT);
2. SQLSetConnectAttr(... SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON ...);
3. SQLSetConnectAttr( ... SQL_ATTR_TXN_ISOLATION, SQL_TXN_READ_COMMITTED ...);

Rollback the same but with SQL_ROLLBACK in the first call.

Error handling

If I can't commit (deadlock etc.) then it is implicitly a rollback, right? Or should I then call SQLEndTran(... SQL_ROLLBACK) to be on the safe side?

I any case, I would still continue with 2. and 3.

_Mark