Executing callback after current transaction commit unless rollback occurs

[Yegor Roganov]

I would like to get what I described in the subject: attach an event listener that will execute once after current transaction's successful commit (analogue to django's on_commit ). If current transaction rolls back, the event listener(s) should be discarded.

I have tried using `event.listens_for(sess, 'after_commit', once=True)`, but probably as expected it will execute the callback if session continues to be used after rollback. Here is a complete source that you can play around with:

from sqlalchemy import Column, Integer, create_engine, event

from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('sqlite:///:memory:', echo=True)

Session = sessionmaker(bind=engine)

class User(Base):

    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)

Base.metadata.create_all(engine)

sess = Session()

sess.add(User(id=1))

sess.commit()

@event.listens_for(sess, 'after_commit', once=True)

def foo(session):

    print("shouldn't be called since the transaction will be rolled back!")

sess.add(User(id=1))

try:

    sess.commit()

except:

    sess.rollback()

sess.add(User(id=2))

sess.commit()  # event callback will be called here

There is also an `after_transaction_end` event, but unfortunately I have not managed to bend it to my needs as well: 1) I haven't found public API to determine the transaction state when the callback executes 2) If an event listener is attached in a subtransaction, the callback will get called when the subransaction commits, while I'd like to the callback to only get called when the 'outermost' transaction commits successfully.


Greatly appreciate any help.

[Mike Bayer]

On 03/28/2016 03:21 PM, Yegor Roganov wrote:
> I would like to get what I described in the subject: attach an event
> listener that will execute once after current transaction's successful
> commit (analogue to django's on_commit

> <https://docs.djangoproject.com/en/1.9/topics/db/transactions/#django.db.transaction.on_commit> ).

> If current transaction rolls back, the event listener(s) should be
> discarded.

set your own token to disable the event after the transaction ends:

@event.listens_for(sess, 'after_commit')
def foo(session):
if "my_token" not in session.info:
print("do my event")


@event.listens_for(sess, "after_transaction_end")
def bar(session, transaction):
session.info["my_token"] = True

> --
> 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
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

You could also implement a Two Phase Commit using `zope.sqlalchemy` and the `transaction` package.

[Yegor Roganov]

Thanks Mike

Unfortunately it seems that after_transaction_end event executes twice: right before COMMIT and after after_commit event. Here what I see with echo=True and some prints:

2016-03-29 07:29:14,799 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id) VALUES (?)

2016-03-29 07:29:14,799 INFO sqlalchemy.engine.base.Engine (3,)

after_transaction_end

2016-03-29 07:29:14,799 INFO sqlalchemy.engine.base.Engine COMMIT

after_commit

after_transaction_end

2016-03-29 07:29:14,800 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

2016-03-29 07:29:14,800 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id) VALUES (?)

2016-03-29 07:29:14,800 INFO sqlalchemy.engine.base.Engine (2,)

after_transaction_end

2016-03-29 07:29:14,801 INFO sqlalchemy.engine.base.Engine COMMIT

after_commit

after_transaction_end

Consequently the event in after_commit never executes since token is already set. Here is the complete code:

from sqlalchemy import Column, Integer, create_engine, event

from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

engine = create_engine('sqlite:///:memory:', echo=True)

Session = sessionmaker(bind=engine)

class User(Base):

    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)

Base.metadata.create_all(engine)

sess = Session()

sess.add(User(id=1))

sess.commit()

@event.listens_for(sess, 'after_commit')

def foo(session):

    print('after_commit')

    if "my_token" not in session.info:

        print("never called")

@event.listens_for(sess, "after_transaction_end")

def bar(session, transaction):

    print('after_transaction_end')

    session.info["my_token"] = True

sess.add(User(id=3))

sess.commit()

sess.add(User(id=2))

sess.commit()

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[Mike Bayer]

On 03/29/2016 12:32 AM, Yegor Roganov wrote:
> Thanks Mike
>
> Unfortunately it seems that after_transaction_end event executes twice:
> right before COMMIT and after after_commit event. Here what I see with
> echo=True and some prints:

OK you need to filter out for the top-level transaction only:

@event.listens_for(sess, "after_transaction_end")
def bar(session, transaction):
print('after_transaction_end')

if transaction._parent is None:
session.info["my_token"] = True


there might be other ways to do this w/o knowing the internals of
SessionTransaction but this one is pretty safe. SessionTransaction
should have a bit more public API for things like this.

> if "my_token" not in session.info <http://session.info>:

> print("do my event")
>
>
> @event.listens_for(sess, "after_transaction_end")
> def bar(session, transaction):

> session.info <http://session.info>["my_token"] = True

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com

> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.

> > Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> 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

> <mailto:sqlalchemy+...@googlegroups.com>.

[Yegor Roganov]

Thank you very much!

If you don't mind me asking again, I'm a bit confused why there are non-toplevel transactions as I didn't use any subtransactions/savepoints.

>      > <mailto:sqlalchemy+unsub...@googlegroups.com <javascript:>>.

>      > To post to this group, send email to sqlal...@googlegroups.com
>     <javascript:>
>      > <mailto:sqlal...@googlegroups.com <javascript:>>.
>      > Visit this group at https://groups.google.com/group/sqlalchemy
>     <https://groups.google.com/group/sqlalchemy>.
>      > For more options, visit https://groups.google.com/d/optout
>     <https://groups.google.com/d/optout>.
>
> --
> 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

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[Mike Bayer]

the flush() process uses its own subtransaction.

On 03/29/2016 09:48 AM, Yegor Roganov wrote:
> Thank you very much!
>
> If you don't mind me asking again, I'm a bit confused why there are
> non-toplevel transactions as I didn't use any subtransactions/savepoints.
>
> On Tuesday, March 29, 2016 at 4:01:45 PM UTC+3, Mike Bayer wrote:
>
>
>
> On 03/29/2016 12:32 AM, Yegor Roganov wrote:
> > Thanks Mike
> >
> > Unfortunately it seems that after_transaction_end event executes
> twice:
> > right before COMMIT and after after_commit event. Here what I see
> with
> > echo=True and some prints:
>
> OK you need to filter out for the top-level transaction only:
>
> @event.listens_for(sess, "after_transaction_end")
> def bar(session, transaction):
> print('after_transaction_end')
> if transaction._parent is None:

> session.info <http://session.info>["my_token"] = True
>
>

> > if "my_token" not in session.info <http://session.info>:

> > print("never called")
> >
> >
> > @event.listens_for(sess, "after_transaction_end")
> > def bar(session, transaction):
> > print('after_transaction_end')

> > session.info <http://session.info>["my_token"] = True
> >
> >

> > > <mailto:sqlalchemy+...@googlegroups.com
> <javascript:> <javascript:>>.

> > > To post to this group, send email to
> sqlal...@googlegroups.com
> > <javascript:>
> > > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > > Visit this group at
> https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>
> > <https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>>.
> > > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>
> > <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>.
> >
> > --
> > 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 <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
> --
> 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

> <mailto:sqlalchemy+...@googlegroups.com>.