CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Joril
Joril

Reputation: 20547

How to handle two-phase commit with SQLAlchemy

I'm trying to do a two-phase commit using SQLalchemy 0.6.8 with Postgresql 8.3.4, but I think I'm missing something...
The workflow goes like this:

session = sessionmaker(engine)(autocommit=True)
tx = session.connection().begin_twophase(xid) # Doesn't issue any SQL
session.begin()
session.add(obj1)
session.flush()
tx.prepare()

then from another session

session = sessionmaker(engine)(autocommit=True)
session.connection().commit_prepared(xid, recover=True) # recover=True because otherwise it complains that you can't issue a COMMIT PREPARED from inside a transaction

This doesn't raise any error, but doesn't write anything to the table either... O_o What am I missing?

I tried even blocking the application after the prepare() and issuing a COMMIT PREPARED 'xid' from pgadmin, but still nothing gets written.

Upvotes: 7

Views: 4613

Answers (1)

Joril
Joril

Reputation: 20547

I managed to get it working, here's how:

session = sessionmaker(engine)(twophase=True)
session.add(obj1)
session.prepare()
# Find transaction id
for k, v in s.transaction._connections.iteritems():
   if isinstance(k, Connection):
      return v[1].xid

then from another session

session = sessionmaker(engine)(twophase=True)
session.connection().commit_prepared(xid, recover=True)

Upvotes: 7

Related Questions