[email protected] wrote: > > We are rolling out some SQLAlchemy infrastructure at work and came > across an interesting issue. We are using the mssql dialect on top of > pyodbc to talk to both SQL Server and Sybase dataservers. We use bound > metadata and scoped session_maker with autocommit=True. First off, the > problem we encountered was with the transaction log on master filling > up when the SQLAlchemy transaction ran This was very curious as the > login was read-only. It turns out that the application was emitting > thousands of queries (SELECT only, coming from my_table.select() on > the SA Table metadata objects, not via the ORM) and a transaction was > generated for each one. > > It looks (from tracing what is written down the socket to the > dataserver) like SA rolls back the transaction on connection checkin > and then *creates another transaction*? > write(4, 0x08CC0888, 59) = 59 > 0F01\0 ;\0\0\0\0 ! .\0\0\0\0 I F @ @ T R A N C O U N T > 0 > R O L L B A C K B E G I N T R A N S A C T I O N
the exact SQL here is some crap the MSSQL dialect has in do_begin() which is the only way we could find to get SAVEPOINT to work. However, even if this were removed, a new connection is in fact started the first time you use a freshly checked out connection. Connections returned to the pool have rollback() called unconditionally. The reasons are straightforward - a newly checked out connection should act just like a brand new one - no locks, no transactional state. a checked-in connection needs to act like a "closed" one in that it isnt hanging onto any locks. > > Ideally, our connection pool should provide connections without any > transactions active and in unchained mode (@@trancount = 0, > @@transtate = 1, @@tranchained = 0). The checkin code can rollback if > @@trancount > 0. > > What is the suggested configuration to achieve this? SQLAlchemy assumes autocommit=False which is per DBAPI spec (and note that autocommit=False requires that a transaction is present). There is always a transaction, and in fact this has nothing to do with SQLA - just use pyodbc directly and you will see this is the case. Some DBAPI's provide autocommit modes, but since these are not standardized or universally available, and because SQLA has its own "autocommit" that works very nicely and consistently, SQLA has no support for them. I dont know what Pyodbc provides. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
