How to debug "Invalid transaction state" error in ODBC?

Igor Korot 26

Hi, ALL,

I have an application that throws an aforementioned error upon exit/disconnect.

I know that every transaction I start I COMMIT/ROLLBACK and those transactions are doing "CREATE TABLE/INSERT" statements.

However I am also doing SELECT statements outside of explicit BEGIN {TRANSACTION} statement.

Unfortunately the doc at https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqldisconnect-function?view=sql-server-ver16 does not help with identifying what and where the error is coming from.

My question now is: do I have to COMMIT/ROLLBACK every single SELECT, INSERT, DELETE and CREATE to fix that error?

Also, I'm calling:

ret = SQLSetConnectAttr( m_hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) FALSE, 0 );

in my application.

Please advise.

Thx.

1 answer

Erland Sommarskog 115.6K Reputation points MVP

2025-01-18T19:33:32.1+00:00

If I understand this line correctly:

ret = SQLSetConnectAttr( m_hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) FALSE, 0 );

You are turning off auto-commit, or in SQL Server parlance, you are setting the option IMPLICIT_TRANSACTIONS ON. (The default for this setting is OFF.) When this setting is ON, any DML or DDL statement, including SELECT; starts a transaction, which must be explicitly committed and rolled back.

This is a very odd creature in the SQL Server world, but it is actually standard, so this is the norm in many other products.
Please sign in to rate this answer.
Igor Korot 26 Reputation points

2025-01-18T20:46:20.3133333+00:00

@Erland Sommarskog ,

Yes it is weird. What is there to COMMIT/ROLLBACK? No changes were made...

Anyway I will try to add those to every query.

Thx.

Erland Sommarskog 115.6K Reputation points MVP

2025-01-18T20:50:38.0233333+00:00

Yes it is weird. What is there to COMMIT/ROLLBACK? No changes were made...

Yes, if the isolation level is READ UNCOMMITTED or READ COMMITTED, there is not much point in committing. But if the isolation level is REPEATABLE READ, SERIALIZABLE or SNAPSHOT, there has to be a transaction if you run multiple SELECT statements and you want uphold the promise of the isolation level.

Igor Korot 26 Reputation points

2025-01-18T20:57:31.6+00:00

@Erland Sommarskog ,

I'm not touching the isolation level.

But still - its very weird.

Thank you.

Erland Sommarskog 115.6K Reputation points MVP

2025-01-18T21:00:21.3233333+00:00

It would be even weirder if the behaviour was different depending on isolation level.

Then again, as I said, the whole idea is weird as such in the SQL Server world. But very much the norm other RDBMSs.

Igor Korot 26 Reputation points

2025-01-18T21:05:10.29+00:00

@Erland Sommarskog ,

What do you mean?

Are you saying that in i.e. PostgreSQL it is done the same way?

But if I remember correctly - you don't have to commit SELECT statement...

Thank you.

Erland Sommarskog 115.6K Reputation points MVP

2025-01-18T21:23:17.26+00:00

When you say it, that's probably is true. Then again, I found https://www.postgresql.org/docs/7.3/runtime-config.html and scroll down to section 3.4.4. It does not mention SELECT, but it mentions a couple of other statements as exceptions, so it seems reasonable to assume that SELECT starts a transaction in Postgres when AUTOCOMMIT is off.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to debug "Invalid transaction state" error in ODBC?

1 answer

Your answer