Subject: [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC

From: "markmaker (@markmaker)" <[email-masked]>
Date: Fri, 16 May 2025 15:54:21 +0000

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](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/odbc/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:

https://github.com/postgresql-interfaces/psqlodbc/blob/9cd50a509529843682ab9538ba6cac3f39dcb20c/connection.c#L1802

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

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 18 May 2025 15:29:47 +0000

> [psqlodbc/connection.c](https://github.com/postgresql-interfaces/psqlodbc/blob/9cd50a509529843682ab9538ba6cac3f39dcb20c/connection.c#L1802)
> 
> Line 1802 in [9cd50a5](/postgresql-interfaces/psqlodbc/commit/9cd50a509529843682ab9538ba6cac3f39dcb20c)
> 
>  issue_begin = ((flag & GO_INTO_TRANSACTION) != 0 && !CC_is_in_trans(self)), 

see https://github.com/postgresql-interfaces/psqlodbc/blob/9cd50a509529843682ab9538ba6cac3f39dcb20c/results.c#L4334

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.> 

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

Dave
> Thanks, _Mark

---

From: "markmaker (@markmaker)" <[email-masked]>
Date: Wed, 21 May 2025 07:30:14 +0000

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