Message-ID: From: "davecramer (@davecramer)" To: "postgresql-interfaces/psqlodbc" Date: Sun, 18 May 2025 15:29:47 +0000 Subject: Re: [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC In-Reply-To: References: List-Id: X-GitHub-Author-Login: davecramer X-GitHub-Comment-Id: 2889054644 X-GitHub-Comment-Type: issue_comment X-GitHub-Issue: 116 X-GitHub-Repo: postgresql-interfaces/psqlodbc X-GitHub-Type: comment X-GitHub-Url: https://github.com/postgresql-interfaces/psqlodbc/issues/116#issuecomment-2889054644 Content-Type: text/plain; charset=utf-8 > 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: > > [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)), > 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 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.> > 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