no 'COMMIT' with explicit begin() + commit() if transactional=False is *not* specified

[Former user]

This is with MySQL using SA 0.4.2p3

I'm creating my engine like this:

  engine = create_engine(
   dburi,
   echo=echo,
   poolclass=sqlalchemy.pool.QueuePool,
   echo_pool=False,
   pool_recycle=3600,
   )

and my session like this:

_session_factory = None
def session_factory():
  global _session_factory
  if _session_factory is None:
    _session_factory = scoped_session(
     sessionmaker(
      bind=engine_factory()) )
  return _session_factory()

and using it like this (in another module):

sess = session_factory()

Usually I just do this:

objects = sess.query(....).do_stuff()

and

sess.update(object)
or
sess.delete(object)
or
sess.save(object)

followed by:

sess.flush()

and always followed by sess.close()

However, in case I update(object) a bunch of objects. I wrap that loop like this:

sess.begin()
for o in objects:
  # manipulate o ...
  sess.update(o)
sess.commit()

With the logging turned up (and verified by manual inspection of the DB), the "BEGIN" statement and "UPDATE" statements occur, but no "COMMIT" (or "ROLLBACK" for that matter....)

I have to use a slightly different session construction, specifying transactional=False to sessionmaker explicitly. Then I get what I expect. This seems like a bug.

[Mike Bayer]

The only "bug" i can see here is that, surprisingly, sessionmaker defaults transactional to True whereas Session defaults it to False. I'm a little surprised its like that....and unfortunately is pretty tough to fix until version 0.5.

However, as far as the actual behavior of transactional=True, its not a bug. when you're in transactional=True, there's always a transaction in progress. Saying begin() inside of a transaction, whether its a transaction on a Connection, Engine, or Session, has the effect of "nesting" the begin call such that a stack is incremented, and is decremented whenever commit() is called. so if you were to call begin() twice in a row, you'd call commit() twice as well and the second commit() would close the "outermost" pair of begin/commits and actually issue the COMMIT. Its the same with transactional=True; everytime you call commit(), begin() is called immediately afterwards (which results in the actual BEGIN as soon as a connection is re-grabbed from the pool).

So the solution to your issue is just to not call begin(); being in transactional=True means that BEGIN will be issued as soon as the connection is grabbed from the pool. If, OTOH, you are genuinely looking for a transaction that is local to itself and is isolated from the ongoing session-level transaction, you have the option to either use a different session, or to use a "nested" transaction via begin_nested(). begin_nested() is currently only supported on Mysql, Postgres, and Oracle, as it makes usage of SAVEPOINT.

jek is suggesting right now we change the whole terminology to autocommit=False/True. That might be a better way to make this clearer.

[Mike Bayer]

current 0.5 trunk consistently uses autocommit=False.

[Mike Bayer]

Removing milestone: 0.5.0 (automated comment)