On Saturday, 17 December 2011 19:57:10 UTC, Michael Bayer wrote: > > > On Dec 17, 2011, at 2:24 PM, Andronikos Nedos wrote: > > > So, if I understand correctly, I need to maintain the Connection object > between requests and on the 2nd request bind a session to the existing > Connection object and then > session.commit() or session.abort() ? The question now is how can I > persist the connection object between requests, is in-memory the only > option here ? > > > One or both of us might be misunderstanding parts of the background here, > but my current take on it is that you want one request to end completely, > but for the database to maintain the transaction. Then you'd like to > resume the transaction in a subsequent request. This approach uses a > transaction that was created using PREPARE TRANSACTION along with an XID. > When that technique is used, the database, let's say its Postgresql, will > persistently hold onto the transaction noted by an XID after the connection > which created it is gone. An entirely new database connection can resume > this transaction by again calling BEGIN PREPARED with the same XID. > > So what I'm suggesting is that the string "XID" symbol is the only thing > you need to carry across to the second request, in order to pick up again > on that same transaction. You'd never want to hold DBAPI connections or > any other kind of resource like that between requests. The XID approach > is already quite dangerous, as if your second HTTP request never comes in, > you have a dangling transaction in your database, locks and all. The only > way to zap these is to query through pg_stat_activity, get their ids and > cancel them with "ROLLBACK PREPARED <xid>". > >
You're spot on Michael. I thought we could get away without using a DB-driven TPC as we don't need to commit to multiple databases on the backend, but it seems our requirements are best solved with the machinery offered by TPC. As for stale transactions, not much we can do about it, other than use a timeout to identify and terminate what appear to be a stale txns. Thanks again, Andronikos -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/8s0yVwcpPGkJ. 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.
