SA, without a transaction in progress will call commit on the database
connection when it detects an INSERT, UPDATE, DELETE, CREATE, etc. This
is it's "autocommit" mode. The relevant code is:
if context.should_autocommit and not self.in_transaction():
self._commit_impl()
and
def _do_commit(self):
self.connection._commit_impl()
The DBAPI specifies that a connection by default begins a transaction.
So if you only issue SELECT statements you remain in a single
transaction and with a db like Postgresql, by design, you don't see
changes made to the database by other sessions. You can test this by
connecting with psycopg2, issuing a select and then ps ax | grep
postgres. You'll see "idle in transaction".
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.
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.
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.
I know that to some extent this comes down to opinion on how the
connection should behave.
-Randall
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
