On Jan 9, 2009, at 6:05 PM, Randall Smith wrote:
> > There are two problems I see with this. > > 1. The session's view of the database is inconsistent, depending on if > and when queries that modify records are used. Every time a commit is > issued the view of the database (in terms of MVCC) has changed. for this reason the Session in modern SQLA defaults to "autocommit=False", where there is in fact a transaction begun on a single connection which remains open until commit() is called explicitly. That way you can be assured that the state of objects within the session is in sync with the current transactional view. After a rollback() or commit() the full contents of the session are expired. But as far as the "autocommit" Session, its quite usable as well and works just fine for web applications. The connection is returned to the pool after each individual execute() call, no matter if its a SELECT or a write operation, where a ROLLBACK is issued (see pool.py line 277). "autocommit" mode is actually very common in web applications, I believe its the default behavior for django and most PHP applications. > 2. In the case of Postgresql, a connection held open for a long time, > running only SELECT queries, would never see updates from other > sessions > and this might tax the database while it tries to maintain MVCC for > that > connection. A ROLLBACK is issued on the connection when it's returned to the pool, and the connection is returned to the pool immediately after each execution in the case of an autocommit Session or connectionless execution with an engine. So in the majority of usages (that is, using an autocommit Session or using connectionless execution, *not* the case of explicit execution with a Connection in autocommit mode), the connection is always completely fresh from a transactional standpoint and no transactional state builds up. If OTOH you're doing explciit transactions, then its up to you to decide how long you'd like to keep your transactions opened. > Once solution, at least for the psycopg2 based connection would be to > call commit on SELECTS also when not using a transaction (SA > transaction). I've done some testing and this seems to work well. our calling of ROLLBACK when connections are returned to the pool are equivalent to this from a "transactional state" standpoint (since the COMMIT in your scenario is not committing anything). I think with your project specifically, that of reading lots of information during a reflection process given a single Connection, we probably want to look into establishing transactional boundaries for the reflection process, because in that case you do have the less common use case of an explicit Connection object that's in autocommit mode. That's something we can look into in 0.6. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
