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

Igor Korot 51

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.

LiHongMSFT-4306 30,586 Reputation points

2025-01-20T02:27:52.03+00:00

Hi @Igor Korot

Do you have further question on this issue?

If any answer is helpful to your question, please accept it as an answer so that other users with similar questions can see it more easily. :)

1 answer

Erland Sommarskog 116.5K 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 51 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 116.5K 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 51 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 116.5K 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 51 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 116.5K 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.

Igor Korot 51 Reputation points

2025-01-19T18:39:38.3133333+00:00

@Erland Sommarskog ,

Is there a function that helps identifying which transaction is still open?

Thx.

Erland Sommarskog 116.5K Reputation points MVP

2025-01-19T19:14:22.8666667+00:00

Use SELECT @@trancount to get the nesting level of transactions. Any non-zero value means that you have an open transaction. COMMIT will decrement it by one. One when it goes down to zero, the transaction is actually committed.

ROLLBACK always takes @@trancount down to zero.

Despite the "cinnamon buns", @@trancount is officially a function.

Igor Korot 51 Reputation points

2025-01-19T20:04:00.1+00:00

@Erland Sommarskog ,

And I presume after that is only visually inspecting the code to find an unclosed transaction, right?

Thx

Erland Sommarskog 116.5K Reputation points MVP

2025-01-19T20:06:22.76+00:00

Not sure what you mean by that. @@trancount is a built-in function in SQL Server, and it certainly does not look at any code. Even less does it understand it.

Igor Korot 51 Reputation points

2025-01-19T20:11:07.4766667+00:00

@Erland Sommarskog ,

Yes, but it gives a number and not the actual query that is open, right?

Thank you.

Erland Sommarskog 116.5K Reputation points MVP

2025-01-19T20:17:06.5466667+00:00

Correct. The "actual query" is not really meaningful, since it could be a long transaction with many statements. You can get hold of what the transaction has been doing by inspecting sys.dm_tran_locks, provided that you have the permissions to that. It's certainly not something you would do in application code.

Igor Korot 51 Reputation points

2025-01-20T05:46:42.62+00:00

@Erland Sommarskog ,

So I put following right before SQLDisconnect()::

`int count; SQLLEN ptr;` ` ret = SQLAllocHandle( SQL_HANDLE_STMT, m_hdbc, &m_hstmt );` ` ret = SQLExecDirect( m_hstmt, L"SELECT @@trancount", SQL_NTS );` ` ret = SQLBindCol( m_hstmt, 1, SQL_C_SLONG, &count, 0, &ptr );` ` ret = SQLFetch( m_hstmt );` ` if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )` ` {` ` GetErrorMessage( errorMsg, STMT_ERROR );` ` result = 1;` ` }`

`

To my surprise the count variable comes up as 1 and not 2.

And then I still get the same error, trying to disconnect.

Any idea?

Thx.

EDIT:

I can try and call SQLEndTran(), but that would be wrong.

So I need a way of identifying what statement is still executing

Erland Sommarskog 116.5K Reputation points MVP

2025-01-20T21:50:42.8133333+00:00

There is no statement executing, obviously. If there were, you would be waiting for response. And if you submitted the request asynchronously, you would not be able to submit a new SELECT query on that session.

Essentially, you have submitted:

SET IMPLICIT_TRANSACTIONS ON SELECT ... SELECT ... SELECT ... SELECT @@trancount

And @@trancount is 1 at this point, and you need to commit or roll back the implicit transaction.

But since you don't seem to like using implicit transactions in the first place, but prefer autocommit, switch to autocommit and the problem should go away.

Igor Korot 51 Reputation points

2025-01-21T00:25:19.1966667+00:00

@Erland Sommarskog ,

I am not sure what you mean.

I believe this line

ret = SQLExecDirect( m_hstmt, L"SELECT @@trancount", SQL_NTS );`

does execute the statement of SELECT .

However contrary to what the error states the @@trancount result is showing as 1, which indicates as there is a current transaction and nothing else.

Now I actually want AUTOCOMMIT to be off an entire time during the run of the application.

Thank you.

Erland Sommarskog 116.5K Reputation points MVP

2025-01-21T22:01:44.02+00:00

Now I actually want AUTOCOMMIT to be off an entire time during the run of the application.

Then you will have to commit your implicitly started transactions. The best is to submit

IF @@trancount> 0 COMMIT TRANSACTION

before you close the connection.

Igor Korot 51 Reputation points

2025-01-22T02:22:10.45+00:00

@Erland Sommarskog ,

Yes I understand

Problem is - in ODBC/C++ world you have to have a statement and run that SELECT.

And when I did - I got that value of 1.

Am I missing something?

Besides - I went and inspected my code visually. Every single statement ends up in COMMIT/ROLLBACK prior to releasing the statement.

Thank you.

Erland Sommarskog 116.5K Reputation points MVP

2025-01-22T21:52:42.9066667+00:00

Problem is - in ODBC/C++ world you have to have a statement and run that SELECT.

Not sure what you mean. Obviously you can submit batches without SELECT. For instance, those with INSERT statements...

Besides - I went and inspected my code visually. Every single statement ends up in COMMIT/ROLLBACK prior to releasing the statement.

Seems that you are missing one nevertheless. (Since I don't see your code, I cannot say anything more useful.) But if you are also making a BEGIN TRANSACTION when you have already started an implicit transaction, one COMMIT is not enough

Anyway, submit

IF @@trancount > 0 COMMIT TRANSACTION

Or if you dabble with nested transactions

WHILE @@trancount > 0 COMMIT TRANSACTION

If you are using any transaction interfaces in ODBC, you need to commit through those. But now it gets complicated if you have IMPLICIT_TRANSACTIONS ON.

Igor Korot 51 Reputation points

2025-01-22T23:13:34.73+00:00

@Erland Sommarskog ,

[quote]

Not sure what you mean. Obviously you can submit batches without SELECT. For instance, those with INSERT statements...

[/quote]

Sure I can. But the point is that I am working with ODBC/C++ where you connect than get a statement handle and run queries on that handle. You can't run anything on the connection handle. And so when I make a statement handle and run that select - the transaction count will go up...

I will go thru my code again and check if I miss anything...

Thx.
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