Complex queries with transaction is cancelled/rollbacked.

[zlev-intel]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

Environment

To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:

• Python: 3.8.10
• pyodbc: 4.0.32
• OS: Windows
• DB: Mssql
• driver: 18

Issue

I'm trying to execute the following (set of) queries: BEGIN TRAN SET NOCOUNT ON
IF (SELECT COUNT(*) FROM vaf_infra.mutex WITH (HOLDLOCK) WHERE row = 0 AND executor_id='00000000-0000-0000-0000-000000000000' ) != 0 BEGIN UPDATE vaf_infra.mutex SET executor_id=@uuid WHERE row = 0 AND executor_id='00000000-0000-0000-0000-000000000000' END SELECT executor_id FROM vaf_infra.mutex WITH (HOLDLOCK) WHERE row = 0
COMMIT where table is: CREATE TABLE cloudcds_dev.vaf_infra.mutex ( executor_id UNIQUEIDENTIFIER NULL ,lock_time DATETIME2 NULL ,row INT NULL DEFAULT (0) ) ON [PRIMARY] GO Size of table: 1row; Code used to implement some type of mutex; It is working OK when send from any DB SW - like dbforge or others, but failed when called from python/pyodbc. Looks like that pyodbc cancels the execution before COMMIT, which caused proper return, but table value is not modified( rollbacked)

[v-chojas]

Could you post an ODBC trace?

[zlev-intel]

SQL.LOG

[v-chojas]

acd_apis        5f8-5148    ENTER SQLEndTran 
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x000001C6DF2D8700
        SQLSMALLINT                  1 

1 is rollback. You need to call https://github.com/mkleehammer/pyodbc/wiki/Cursor#commit to cause it to commit the transaction.

[zlev-intel]

But I have commit inside of my query. And I do not issue rollback explicit in any place.

Thanks, Lev Zlotin @.*** +972-4-8656740 +972-53-5207679 WIKI: https://wiki.ith.intel.com/display/AETIDC (HTTP://GOTO/AETIDChttp://goto/AETIDC ) HSD: https://hsdes.intel.com/appstore/gts/#/

From: v-chojas @.> Sent: Monday, May 9, 2022 20:34 To: mkleehammer/pyodbc @.> Cc: Zlotin, Lev @.>; Author @.> Subject: Re: [mkleehammer/pyodbc] Complex queries with transaction is cancelled/rollbacked. (Issue #1057)

acd_apis 5f8-5148 ENTER SQLEndTran

           SQLSMALLINT                  2 <SQL_HANDLE_DBC>

           SQLHANDLE           0x000001C6DF2D8700

           SQLSMALLINT                  1

1 is rollback. You need to call https://github.com/mkleehammer/pyodbc/wiki/Cursor#commit to cause it to commit the transaction.

— Reply to this email directly, view it on GitHubhttps://github.com/mkleehammer/pyodbc/issues/1057#issuecomment-1121382765, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AOH2LE6UCMIDHQ7ZLAE5K4LVJFD67ANCNFSM5VNDKP5A. You are receiving this because you authored the thread.Message ID: @.**@.>>

Intel Israel (74) Limited

This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies.

[v-chojas]

pyODBC by default runs with autocommit off, and the ODBC driver by default will keep a transaction open, so the explicit statements in your query will open a nested transaction. You have committed the inner transaction but the outer (implicit) one remains open and gets rolled back when the handle is freed. Call cursor.commit() to commit the outer one too.

[zlev-intel]

Thank you, I set AutoCommit on.