How to determine if a session has any uncommitted changes

[Tom Dalton]

Using SQLAlchemy 0.9.

I am writing a server, that's running in gevent. As part of the library code, I am trying to write a session context manager that checks if the session has uncommitted changes when it's exited (this situation represents a logic/programming error, as I expect every server 'request' to finish with a committed or rolled back transaction. Each request has its own session but there may be multiple transactions within that request/session.

Based on the docs for Session and this question at stackoverflow (http://stackoverflow.com/questions/13910576/find-out-how-many-uncommitted-items-are-in-the-session) I wrote a context manager like so:

@contextmanager
def checked_scoped_session():
    session = get_session()
    try:
        yield session
    except:
        session.rollback()
        raise
    finally:
        if session.new or session.dirty or session.deleted:
            new = len(session.new)
            dirty = len(session.dirty)
            deleted = len(session.deleted)
            msg = "Session left with ({} new, {} dirty, {} deleted) instances"\
                " uncommitted".format(new, dirty, deleted)
            raise SessionLeftUnclean(msg)
        session.close()

While testing, I discovered that session.new, .dirty and .deleted appear to actually be UNFLUSHED instances, and not uncommitted. This is a problem since I am using (and want to keep using) autoflush. I have been reading up on session.transaction, however, it's still not clear to me how I can tell if the transaction has (uncommitted) changes, and this seems to be further complicated by the possibility of sub-transactions.

Is there an easy way to do what I want? E.g. Ideally I want to do:

    finally:
        if session.has_uncommitted_changes():
            raise SessionLeftUnclean()

Any and all help gratefully appreciated!

Regards,

Tom

[Michael Bayer]

Checking .new, .dirty, and .deleted is not a bad idea because they indicate activity that has occurred on the session subsequent to the last commit(). But if these changes have been flushed, then it’s “clean”, but the transaction may not have been committed yet. Right now the documentation encourages just using events (eg. after_begin, after_commit, after_rollback) to track the state of the Session regarding connections as you see fit, there’s not a public API method of checking this.

If you really want to see if the session is linked to a transaction in progress, you could check len(session.transaction._connections), if that’s nonzero, then there’s a DBAPI-level transaction in progress - it means there’s one or more connections that the Session is linked to. But you could achieve this same information using after_begin as well.

[Tom Dalton]

Thanks for the reply. I saw the session transaction events stuff but I'm not sure that they help me (correct me if I'm wrong). I believe a transaction will exist if I run *any* query, not just one that modifies data. Also, the docs imply that a session will effectively always have a transaction when using autoflush mode. Finally, the after_begin event only fires once. So I'd have no way to tell the difference between the session state after the following 3 scenarios:

1. (implicit) begin, select, update, select, (autoflush)
2. (implicit) begin, select, (autoflush)
3. (implicit) begin, select

In my use case, I want to detect the uncommitted (but flushed) changes and throw an error, but cases 2 and 3 are 'ok'.

Even disabling autoflush doesn't help, since I'd still be unable to tell if the (erroneous/buggy) code had done an explicit flush without a subsequent rollback or commit.

I'm feeling a bit stuck, but I assume this information must exist in SQLAlchemy somewhere, since if you do:

4. (implicit) begin, select X, update X, flush, rollback

then SQLAlchemy must 'know' which instance's (flushed) changes have been rolled back in order to change those instances' state (I think in the above example, it would mark X as detached?). The problem is I don't know where or how it's maintaining that info...

Any more ideas?

Tom

[Michael Bayer]

On Oct 9, 2014, at 12:16 PM, Tom Dalton <tom.d...@fanduel.com> wrote:

Thanks for the reply. I saw the session transaction events stuff but I'm not sure that they help me (correct me if I'm wrong). I believe a transaction will exist if I run *any* query, not just one that modifies data. Also, the docs imply that a session will effectively always have a transaction when using autoflush mode. Finally, the after_begin event only fires once. So I'd have no way to tell the difference between the session state after the following 3 scenarios:

1. (implicit) begin, select, update, select, (autoflush)
2. (implicit) begin, select, (autoflush)
3. (implicit) begin, select

In my use case, I want to detect the uncommitted (but flushed) changes and throw an error, but cases 2 and 3 are 'ok’.

OK, so catch after_begin, as well as after_flush - inside of after_flush, check a boolean for .new, .dirty, .deleted, that will tell you if the flush actually rendered any changes (which usually it has, you can just record after_flush() happening at all as “changes were probably emitted”).

Even disabling autoflush doesn't help, since I'd still be unable to tell if the (erroneous/buggy) code had done an explicit flush without a subsequent rollback or commit.

I'm feeling a bit stuck, but I assume this information must exist in SQLAlchemy somewhere, since if you do:

4. (implicit) begin, select X, update X, flush, rollback

then SQLAlchemy must 'know' which instance's (flushed) changes have been rolled back in order to change those instances' state (I think in the above example, it would mark X as detached?). The problem is I don't know where or how it's maintaining that info…

it maintains that in session.transaction._new, session.transaction._dirty, session.transaction._deleted, but these are weak referencing (because if references to the object are lost on the outside, we forget about it) so aren’t guaranteed to show you that something definitely didn’t happen.  (hence events the best way to go)

[Tom Dalton]

Brilliant, the after_flush event sounds like the way to go. I guess my event handlers would be:

after_begin:

  session.info.clean = True

after_flush:

  if new, dirty, deleted:

    session.info.clean = False

after_commit:

  session.info.clean = True

after_rollback:

  session.info.clean = True

Then in my context manager I just need to check both (current session.new .dirty .deleted) and (my new session.info.clean attribute) to determine if the session has been left with uncommitted changes. Does that sound like what you'd expect? Are there any other events you can think of that I'd need to handle/be careful of?

Thanks a lot for your help!

Tom

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Michael Bayer]

that should have you covered i think

[imal...@uber.com]

Why wouldn't you check for new, dirty deleted 'before_flush' instead of 'after_flush'. Would 'after_flush' even work?

[Mike Bayer]

you can check for new/dirty/deleted in after_flush, these collections haven't yet been reset.  after_flush_postexec is where they are reset.