Message-ID: From: "markmaker (@markmaker)" To: "postgresql-interfaces/psqlodbc" Date: Fri, 16 May 2025 15:54:21 +0000 Subject: [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC List-Id: X-GitHub-Author-Id: 9963310 X-GitHub-Author-Login: markmaker X-GitHub-Issue: 116 X-GitHub-Repo: postgresql-interfaces/psqlodbc X-GitHub-State: open X-GitHub-Type: issue X-GitHub-Url: https://github.com/postgresql-interfaces/psqlodbc/issues/116 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: 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