postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feedFrom: davecramer (@davecramer) <[email protected]>
To: postgresql-interfaces/psqlodbc <[email protected]>
Subject: Re: [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC
Date: Sun, 18 May 2025 15:29:47 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[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
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: github://postgresql-interfaces/psqlodbc
Cc: [email protected], [email protected]
Subject: Re: [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC
In-Reply-To: <<[email protected]>>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox