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
Where is this coming from? SA? pyodbc? TDS?
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?
thanks,
pjjH
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
