[Sqlalchemy-tickets] Issue #3946: ROLLBACK outside of transactions (zzzeek/sqlalchemy)

[Daniel E. <iss...@bi...>]

New issue 3946: ROLLBACK outside of transactions
https://bitbucket.org/zzzeek/sqlalchemy/issues/3946/rollback-outside-of-transactions

Daniel Eisner:

Hi,

If I try to run a bad "SELECT" statement without starting a transaction, SQLAlchemy tries to roll back the transaction, which fails, and then raises a ProgrammingError execption, obscuring the original exception.

I think the problem is with this bit of code in sqlalchemy.engine.base:

    def _autorollback(self):
        if not self._root.in_transaction():
            self._root._rollback_impl()


That logic looks backwards to me. Was this the original intent? Below is an example stack trace from a case like this.



---------------------------------
Traceback:



/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/pandas/io/sql.pyc in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    492             sql, index_col=index_col, params=params,
    493             coerce_float=coerce_float, parse_dates=parse_dates,
--> 494             chunksize=chunksize)
    495 
    496 

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/pandas/io/sql.pyc in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize)
   1126         args = _convert_params(sql, params)
   1127 
-> 1128         result = self.execute(*args)
   1129         columns = result.keys()
   1130 

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/pandas/io/sql.pyc in execute(self, *args, **kwargs)
   1020     def execute(self, *args, **kwargs):
   1021         """Simple passthrough to SQLAlchemy engine"""
-> 1022         return self.engine.execute(*args, **kwargs)
   1023 
   1024     def read_table(self, table_name, index_col=None, coerce_float=True,

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, statement, *multiparams, **params)
   2053 
   2054         connection = self.contextual_connect(close_with_result=True)
-> 2055         return connection.execute(statement, *multiparams, **params)
   2056 
   2057     def scalar(self, statement, *multiparams, **params):

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    937         """
    938         if isinstance(object, util.string_types[0]):
--> 939             return self._execute_text(object, multiparams, params)
    940         try:
    941             meth = object._execute_on_connection

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_text(self, statement, multiparams, params)
   1095             statement,
   1096             parameters,
-> 1097             statement, parameters
   1098         )
   1099         if self._has_events or self.engine._has_events:

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1187                 parameters,
   1188                 cursor,
-> 1189                 context)
   1190 
   1191         if self._has_events or self.engine._has_events:

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1384                 if cursor:
   1385                     self._safe_close_cursor(cursor)
-> 1386                 self._autorollback()
   1387 
   1388             if newraise:

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _autorollback(self)
    822     def _autorollback(self):
    823         if not self._root.in_transaction():
--> 824             self._root._rollback_impl()
    825 
    826     def close(self):

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _rollback_impl(self)
    701                 self.engine.dialect.do_rollback(self.connection)
    702             except BaseException as e:
--> 703                 self._handle_dbapi_exception(e, None, None, None, None)
    704             finally:
    705                 if not self.__invalid and \

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1313                                         self.dialect.dbapi.Error,
   1314                                         dialect=self.dialect),
-> 1315                 exc_info
   1316             )
   1317         self._reentrant_error = True

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    201     exc_type, exc_value, exc_tb = exc_info
    202     cause = exc_value if exc_value is not exception else None
--> 203     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    204 
    205 if py3k:

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _rollback_impl(self)
    699                 self.engine.logger.info("ROLLBACK")
    700             try:
--> 701                 self.engine.dialect.do_rollback(self.connection)
    702             except BaseException as e:
    703                 self._handle_dbapi_exception(e, None, None, None, None)

/home/developer/.conda/envs/core_runtime/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_rollback(self, dbapi_connection)
    438 
    439     def do_rollback(self, dbapi_connection):
--> 440         dbapi_connection.rollback()
    441 
    442     def do_commit(self, dbapi_connection):

ProgrammingError: (pyodbc.ProgrammingError) ('25000', '[25000] [FreeTDS][SQL Server]The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. (3903) (SQLEndTran)')