sqlalchemy: Unable to use transactions with Azure Synapse Analytics

Discussed in https://github.com/sqlalchemy/sqlalchemy/discussions/8223

^{Originally posted by ww2406 July 7, 2022}

Describe the bug

Hello!

Problem: Unable to use transactions with Azure Synapse Analytics

Description: ODBC Error 111214 is returned: An attempt to complete a transaction has failed. No corresponding transaction found.

Other notes: Transactions function properly when using pyodbc directly [see MCVE]. I saw this post by gordthompson in response to a question on StackOverflow (and since added to the documentation) that

Azure SQL Data Warehouse does not support transactions

but this is outdated. Azure Synapse Analytics does support transactions in most cases; see this Microsoft documentation:

As you would expect, SQL pool supports transactions as part of the data warehouse workload. However, to ensure SQL pool is maintained at scale, some features are limited when compared to SQL Server. This article highlights the differences.

To Reproduce

import pyodbc 
import sqlalchemy as sa 
import os 

p_con_string = os.getenv("synapse_conn_pyodbc")
sa_eng_string = os.getenv("synapse_conn_sa")

p_con = pyodbc.connect(p_con_string)
engine = sa.create_engine(sa_eng_string)

p_cur = p_con.cursor()
p_cur.execute("INSERT INTO test VALUES ('test')")
p_con.commit()
p_con.close()
# No error

with engine.begin() as s_con:
    stmt = sa.Text("INSERT INTO test VALUES ('test')")
    s_con.execute(stmt)

# Traceback (most recent call last):
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3280, in _wrap_pool_connect
#     return fn()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 310, in connect
#     return _ConnectionFairy._checkout(self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 868, in _checkout
#     fairy = _ConnectionRecord.checkout(pool)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 476, in checkout
#     rec = pool._do_get()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\impl.py", line 145, in _do_get
#     with util.safe_reraise():
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
#     compat.raise_(
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
#     raise exception
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
#     return self._create_connection()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 256, in _create_connection
#     return _ConnectionRecord(self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 371, in __init__
#     self.__connect()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 679, in __connect
#     )._exec_w_sync_on_first_run(self.dbapi_connection, self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\event\attr.py", line 329, in _exec_w_sync_on_first_run
#     self(*args, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\event\attr.py", line 343, in __call__
#     fn(*args, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\langhelpers.py", line 1695, in go
#     return once_fn(*arg, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\create.py", line 694, in first_connect
#     dialect.do_rollback(c.connection)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\default.py", line 682, in do_rollback
#     dbapi_connection.rollback()
# pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]111214;An attempt to complete a transaction has failed. No corresponding transaction found. (111214) (SQLEndTran)')

# The above exception was the direct cause of the following exception:

# Traceback (most recent call last):
#   File "synapse_issue.py", line 15, in <module>
#     with engine.begin() as s_con:
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3062, in begin
#     conn = self.connect(close_with_result=close_with_result)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3234, in connect
#     return self._connection_cls(self, close_with_result=close_with_result)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
#     else engine.raw_connection()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3313, in raw_connection
#     return self._wrap_pool_connect(self.pool.connect, _connection)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3283, in _wrap_pool_connect
#     Connection._handle_dbapi_exception_noconnection(
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 2117, in _handle_dbapi_exception_noconnection
#     util.raise_(
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
#     raise exception
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3280, in _wrap_pool_connect
#     return fn()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 310, in connect
#     return _ConnectionFairy._checkout(self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 868, in _checkout
#     fairy = _ConnectionRecord.checkout(pool)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 476, in checkout
#     rec = pool._do_get()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\impl.py", line 145, in _do_get
#     with util.safe_reraise():
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
#     compat.raise_(
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
#     raise exception
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
#     return self._create_connection()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 256, in _create_connection
#     return _ConnectionRecord(self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 371, in __init__
#     self.__connect()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 679, in __connect
#     )._exec_w_sync_on_first_run(self.dbapi_connection, self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\event\attr.py", line 329, in _exec_w_sync_on_first_run
#     self(*args, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\event\attr.py", line 343, in __call__
#     fn(*args, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\langhelpers.py", line 1695, in go
#     return once_fn(*arg, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\create.py", line 694, in first_connect
#     dialect.do_rollback(c.connection)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\default.py", line 682, in do_rollback
#     dbapi_connection.rollback()
# sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]111214;An attempt to complete a transaction has failed. No 
# corresponding transaction found. (111214) (SQLEndTran)')
# (Background on this error at: https://sqlalche.me/e/14/f405)

Error

# Traceback (most recent call last):
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3280, in _wrap_pool_connect
#     return fn()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 310, in connect
#     return _ConnectionFairy._checkout(self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 868, in _checkout
#     fairy = _ConnectionRecord.checkout(pool)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 476, in checkout
#     rec = pool._do_get()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\impl.py", line 145, in _do_get
#     with util.safe_reraise():
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
#     compat.raise_(
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
#     raise exception
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
#     return self._create_connection()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 256, in _create_connection
#     return _ConnectionRecord(self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 371, in __init__
#     self.__connect()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 679, in __connect
#     )._exec_w_sync_on_first_run(self.dbapi_connection, self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\event\attr.py", line 329, in _exec_w_sync_on_first_run
#     self(*args, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\event\attr.py", line 343, in __call__
#     fn(*args, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\langhelpers.py", line 1695, in go
#     return once_fn(*arg, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\create.py", line 694, in first_connect
#     dialect.do_rollback(c.connection)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\default.py", line 682, in do_rollback
#     dbapi_connection.rollback()
# pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]111214;An attempt to complete a transaction has failed. No corresponding transaction found. (111214) (SQLEndTran)')

# The above exception was the direct cause of the following exception:

# Traceback (most recent call last):
#   File "synapse_issue.py", line 15, in <module>
#     with engine.begin() as s_con:
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3062, in begin
#     conn = self.connect(close_with_result=close_with_result)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3234, in connect
#     return self._connection_cls(self, close_with_result=close_with_result)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 96, in __init__
#     else engine.raw_connection()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3313, in raw_connection
#     return self._wrap_pool_connect(self.pool.connect, _connection)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3283, in _wrap_pool_connect
#     Connection._handle_dbapi_exception_noconnection(
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 2117, in _handle_dbapi_exception_noconnection
#     util.raise_(
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
#     raise exception
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\base.py", line 3280, in _wrap_pool_connect
#     return fn()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 310, in connect
#     return _ConnectionFairy._checkout(self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 868, in _checkout
#     fairy = _ConnectionRecord.checkout(pool)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 476, in checkout
#     rec = pool._do_get()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\impl.py", line 145, in _do_get
#     with util.safe_reraise():
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\langhelpers.py", line 70, in __exit__
#     compat.raise_(
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\compat.py", line 208, in raise_
#     raise exception
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\impl.py", line 143, in _do_get
#     return self._create_connection()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 256, in _create_connection
#     return _ConnectionRecord(self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 371, in __init__
#     self.__connect()
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\pool\base.py", line 679, in __connect
#     )._exec_w_sync_on_first_run(self.dbapi_connection, self)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\event\attr.py", line 329, in _exec_w_sync_on_first_run
#     self(*args, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\event\attr.py", line 343, in __call__
#     fn(*args, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\util\langhelpers.py", line 1695, in go
#     return once_fn(*arg, **kw)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\create.py", line 694, in first_connect
#     dialect.do_rollback(c.connection)
#   File "C:\Users\user\AppData\Roaming\Python\Python310\site-packages\sqlalchemy\engine\default.py", line 682, in do_rollback
#     dbapi_connection.rollback()
# sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]111214;An attempt to complete a transaction has failed. No 
# corresponding transaction found. (111214) (SQLEndTran)')
# (Background on this error at: https://sqlalche.me/e/14/f405)

Versions

OS: Windows 10
Python: 3.10
SQLAlchemy: 1.4.37
Database: Azure Synapse Analytics Dedicated Pool
DBAPI (eg: psycopg, cx_oracle, mysqlclient): pyodbc

Additional context

No response</div>

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 52 (32 by maintainers)

Commits related to this issue

Fix 'No transaction found' error on Synapse. Fixed issue where the SQL Server dialect's query for the current isolation level would fail on Azure Synapse Analytics, due to the way in which this datab... — committed to sqlalchemy/sqlalchemy by gordthompson 2 years ago

Most upvoted comments

my point is the same as always, DBAPI is not supposed to have restrictions on rollback(), otherwise you’d never know when you can call it or not if ceratin error conditions “autorollback” and break the driver’s knowledge of the sequence of events.

this is where pyodbc pretending to be “database agnostic” (95% of all pyodbc use is for SQL server) is a problem because it really should have logic to know that certain error conditions mean the driver just did an “autorollback”, unless there’s some way to configure this with the odbc driver or similar.

zzzeek on Jul 11, 2022

i thought gord could append to your patch. either way. sorry I just wait for the “it’s done, here’s our solution” patch to come out before I look too closely 😃

zzzeek on Jul 22, 2022

Okay, with big thanks to @ww2406

https://github.com/gordthompson/sqlalchemy/tree/xact_abort_0721a

now has a patch that works for me against a real Synapse instance.

After doing

pip install git+https://github.com/gordthompson/sqlalchemy@xact_abort_0721a

the following code

import logging

import sqlalchemy as sa

logging.basicConfig(level=logging.INFO)

connection_url = "<insert your connection URL here>"
engine = sa.create_engine(
    connection_url,
    xact_abort=True,
)

with engine.begin() as conn:
    pass

should not fail, but simply log

INFO:root:pyodbc.ProgrammingError 111214 ‘No corresponding transaction found.’ has been suppressed via xact_abort=True

If this looks okay then I can either

try submitting an addendum to #8233 , or
submit this as a separate change.

gordthompson on Jul 21, 2022

Absolutely! If it’d be helpful, I’d be happy to get you credentials to my personal SQL DW dedicated pool I play with occasionally for you to test directly without going through a middle man. Also happy to keep testing for you!

ww2406 on Jul 13, 2022

okay, just mentioned that because you said “maybe all … rollback calls will not work on this database” and this is one where it works.

at any rate, I believe this is a database functionality. I tested the following C# code (dotnet 5.0, Microsoft.Data.SqlClient 4.1.0) and receive a similar result.

using System;
using Microsoft.Data.SqlClient;

namespace SynapseTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Starting test...");

            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();.
            builder.DataSource = "SERVER";
            builder.UserID = "UID";
            builder.Password = "PWD";
            builder.InitialCatalog = "DATABASE";

            using SqlConnection conn = new SqlConnection(builder.ConnectionString);

            conn.Open();
            
            using var txn = conn.BeginTransaction();

            using var cmd = conn.CreateCommand();
            cmd.Transaction = txn;
            cmd.CommandText = "SELECT * FROM sys.dm_exec_sessions";
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            txn.Rollback();
            Console.WriteLine("All done");
        }
    }
}

Starting test...
Catalog view 'dm_exec_sessions' is not supported in this version.
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): 111214;An attempt to complete a transaction has failed. No corresponding transaction found.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransact
ion transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControl
Request)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at Microsoft.Data.SqlClient.SqlInternalTransaction.Rollback()
   at Microsoft.Data.SqlClient.SqlTransaction.Rollback()
   at SynapseTest.Program.Main(String[] args) in C:\Users\user\RiderProjects\SynapseTest\Program.cs:line 37
ClientConnectionId:6bcd9b04-a875-4a8c-a535-80f3cda02707
Error Number:111214,State:1,Class:16

ww2406 on Jul 11, 2022