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/AETIDC<http://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<cursor.commit> to cause it to commit the transaction. — Reply to this email directly, view it on GitHub<#1057 (comment)>, or unsubscribe<https://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.