SQL_AUTOCOMMIT_ON changes to OFF after calling SET TRANSACTION [ODBC176]

Question

SQL_AUTOCOMMIT_ON changes to OFF after calling SET TRANSACTION [ODBC176]

firebird-automations opened this issue 10 years ago · 8 comments

firebird-automations commented 10 years ago

Submitted by: Gregori Arjona (garjona)

Attachments:
SQL_ads.LOG
Firebird ODBC.zip

By default, after opening a conection and doing some select statement, the odbc driver sets the transaction isolation mode to:

READ COMMITTED NO WAIT NO RECORD_VERSION

I checked this by query to the mon$transaction table.If a do some changes to any table the changes are commited automaticaly.

This mode is not suitable for us, because this blocks reads of changed rows by another computer. But if i change manually the transaction isolation level with the command:

SET TRANSACTION READ WRITE WAIT READ COMMITTED RECORD_VERSION

After doing this i need to commit evey change made on the database, if not commited the changes are lost after closing the connection.

I dont know whats happening but this is driving me nuts, can it be and odbc error?

Thank you.

I send to you an odbc trace file.

Answer 1 · 2014-10-01T19:47:18.000Z

Commented by: Gregori Arjona (garjona)

ODBC SQL LOG

Answer 2 · 2014-10-01T19:47:18.000Z

Modified by: Gregori Arjona (garjona)

Attachment: SQL_ads.LOG [ 12593 ]

Answer 3 · 2014-10-01T22:03:29.000Z

Commented by: @alexpotapchenko

Do you really use ODBC driver 2.0.2 (maybe there are other versions of odbc driver are exist on your computer)?
The default transaction isolation mode in 2.0.1 and later is:
READ COMMITTED WAIT RECORD_VERSION
See attached screenshots from my computer - (MON$ISOLATION_MODE (isolation mode) - 2 - read committed record version).

Answer 4 · 2014-10-01T22:03:29.000Z

Modified by: @alexpotapchenko

Attachment: Firebird ODBC.zip [ 12594 ]

Answer 5 · 2014-10-01T22:03:53.000Z

Modified by: @alexpotapchenko

security: Developers [ 10012 ] =>

Answer 6 · 2014-10-02T11:09:06.000Z

Commented by: Gregori Arjona (garjona)

Hi Alexander.

You are wright, this is version 2.0.2.153 and works as you have said, the problem is produced by machines with an older version of ODBC, exactly 2.0.0.148. I did not realize it until you told me. :-(
The mixture of multiple versions is producing problems with transactions. Now i will update the old version of all computers.

Thanks for all.

Regards.

Answer 7 · 2014-10-02T11:24:08.000Z

Modified by: @alexpotapchenko

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

Fix Version: 2.0.3 [ 10581 ]

Answer 8 · 2015-01-01T17:29:49.000Z

Modified by: @alexpotapchenko

status: Resolved [ 5 ] => Closed [ 6 ]