postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)help / color / mirror / Atom feed
[postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC 3+ messages / 2 participants [nested] [flat]
* [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC @ 2025-05-16 15:54 "markmaker (@markmaker)" <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: markmaker (@markmaker) @ 2025-05-16 15:54 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <[email protected]> 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-transaction...). 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/conn... 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC @ 2025-05-18 15:29 "davecramer (@davecramer)" <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: davecramer (@davecramer) @ 2025-05-18 15:29 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <[email protected]> > 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-transaction...). 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/conn...) > > 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/resu... 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC @ 2025-05-21 07:30 "markmaker (@markmaker)" <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: markmaker (@markmaker) @ 2025-05-21 07:30 UTC (permalink / raw) To: postgresql-interfaces/psqlodbc <[email protected]> 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-05-21 07:30 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-05-16 15:54 [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC "markmaker (@markmaker)" <[email protected]> 2025-05-18 15:29 ` "davecramer (@davecramer)" <[email protected]> 2025-05-21 07:30 ` "markmaker (@markmaker)" <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox