postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: markmaker (@markmaker) <[email protected]>
To: postgresql-interfaces/psqlodbc <[email protected]>
Subject: [postgresql-interfaces/psqlodbc] issue #116: Understanding Transactions in ODBC
Date: Fri, 16 May 2025 15:54:21 +0000
Message-ID: <[email protected]> (raw)

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

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