How to refer to a live SA transaction from subsequent requests

[Andronikos Nedos]

Hi all,

We make heavy use of SqlAchemy in our app and use a custom 2-phase commit protocol over HTTP to communicate
with mobile devices.

While all read,write,deletes happen in a first HTTP request, a subsequent HTTP request needs to find the transaction and either commit or abort. i.e.,

request 1, start tx
read,write,deletes

request 2, find tx
commit or abort tx

There's no requirement per se for sesion objects to be migrated from one thread to another one, but is it safe to save/pickle the SessionTransaction
object, until another request in another thread decides that all work done should be committed or aborted ? If the SessionTransaction is unsuitable, is there
another way to refer to an existing live transaction from a subsequent request ?

We use the zodb transaction, zope.sqlalchemy, tm2 machinery at the moment, so it's possibly to write our own TransactionManager, but the question is really
on how do we find and resurrect an existing SA transaction.

Thanks for all your help,
Andronikos

[Michael Bayer]

On Dec 17, 2011, at 9:04 AM, Andronikos Nedos wrote:

Hi all,

We make heavy use of SqlAchemy in our app and use a custom 2-phase commit protocol over HTTP to communicate
with mobile devices.

While all read,write,deletes happen in a first HTTP request, a subsequent HTTP request needs to find the transaction and either commit or abort. i.e.,

request 1, start tx
read,write,deletes

request 2, find tx
commit or abort tx

There's no requirement per se for sesion objects to be migrated from one thread to another one, but is it safe to save/pickle the SessionTransaction
object, until another request in another thread decides that all work done should be committed or aborted ?

Yikes, not at all- SessionTransaction holds onto DBAPI connections and they don't move.   Not like this wouldn't be an interesting feature,  it would involve custom __getstate__()/__setstate__(), and then a lot of tests.     But while I've been aware of this pattern in theory, in 16 years of web programming I've never heard of anyone actually doing it.  

But we do support two phase transactions and XID so the mechanics are there to achieve this effect more manually.

If you are using 2-phase to resurrect transactions into a new process, you need to use Connection directly along with begin_twophase:

http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.base.Connection.begin_twophase

where you'll note you can pass the xid.  The xid is the thing you actually need to be carrying along between requests.

Then you bind your Session to that connection using the techniques at http://www.sqlalchemy.org/docs/orm/session.html#joining-a-session-into-an-external-transaction.

If the SessionTransaction is unsuitable, is there
another way to refer to an existing live transaction from a subsequent request ?

We use the zodb transaction, zope.sqlalchemy, tm2 machinery at the moment, so it's possibly to write our own TransactionManager, but the question is really
on how do we find and resurrect an existing SA transaction.

Thanks for all your help,
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/-/zerH87cr-OIJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Andronikos Nedos]

On Saturday, 17 December 2011 17:22:26 UTC, Michael Bayer wrote:

On Dec 17, 2011, at 9:04 AM, Andronikos Nedos wrote:

Hi all,

We make heavy use of SqlAchemy in our app and use a custom 2-phase commit protocol over HTTP to communicate
with mobile devices.

While all read,write,deletes happen in a first HTTP request, a subsequent HTTP request needs to find the transaction and either commit or abort. i.e.,

request 1, start tx
read,write,deletes

request 2, find tx
commit or abort tx

There's no requirement per se for sesion objects to be migrated from one thread to another one, but is it safe to save/pickle the SessionTransaction
object, until another request in another thread decides that all work done should be committed or aborted ?

Yikes, not at all- SessionTransaction holds onto DBAPI connections and they don't move.   Not like this wouldn't be an interesting feature,  it would involve custom __getstate__()/__setstate__(), and then a lot of tests.     But while I've been aware of this pattern in theory, in 16 years of web programming I've never heard of anyone actually doing it.  

But we do support two phase transactions and XID so the mechanics are there to achieve this effect more manually.

If you are using 2-phase to resurrect transactions into a new process, you need to use Connection directly along with begin_twophase:

http://www.sqlalchemy.org/docs/core/connections.html#sqlalchemy.engine.base.Connection.begin_twophase

where you'll note you can pass the xid.  The xid is the thing you actually need to be carrying along between requests.

Thanks Michael.

I guess calling our protocol two-phase commit can be quite misleading, as it's not using any of the TPC database machinery, so
it's less complicated than it appears to be. In reality, it only really needs to know the XID of the transaction, so it can commit or abort the transaction in another process.
 

Then you bind your Session to that connection using the techniques at http://www.sqlalchemy.org/docs/orm/session.html#joining-a-session-into-an-external-transaction.

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 ?

Thanks for your help,
Andronikos

[Michael Bayer]

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

Thanks for your help,
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/-/Qm_KtbuE2GsJ.

[Andronikos Nedos]

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

[Joril]

An entirely new database connection can resume this transaction by again calling BEGIN PREPARED with the same XID.

I'm sorry, but I can't find this command inside the Postgresql docs... Am I missing something?
I'm trying to do a multi-request two-phase transaction too, so I thought I could use the xid to refer to the same transaction from every request, but it looks like BEGIN PREPARED doesn't exist, and if I issue a second "PREPARE TRANSACTION 'samexid'" it complains that the "transaction identifier "xyz" is already in use"

Thanks in advance!

[Michael Bayer]

On Jan 25, 2012, at 10:05 AM, Joril wrote:

An entirely new database connection can resume this transaction by again calling BEGIN PREPARED with the same XID.

I'm sorry, but I can't find this command inside the Postgresql docs... Am I missing something?

PREPARE TRANSACTION.   Paraphrasing, sorry.

I'm trying to do a multi-request two-phase transaction too, so I thought I could use the xid to refer to the same transaction from every request, but it looks like BEGIN PREPARED doesn't exist, and if I issue a second "PREPARE TRANSACTION 'samexid'" it complains that the "transaction identifier "xyz" is already in use"

Well you only PREPARE it once.  Once you do that, it's present in pg_prepared_xacts.     A second session can then "recover" that transaction, which means only that it can be committed or rolled back, by saying "COMMIT PREPARED 'xid'".      So I guess the idea is that each web request does a new PREPARE TRANSACTION with a unique xid, then when the results of all those requests are ready to be committed,  the "final" call then calls "COMMIT PREPARED" on the full list of xids.

I probably mentioned this earlier but two-phase transactions are very hard to work with and aren't really well suited to stateless communication; if the client disappears for three hours or three weeks, the transactional state stays wide open in the database, locks and all, until some cleanup process goes in and emits ROLLBACK PREPARED on the stale xids.   Using a session system, which could just mean extra tables/rows designed to store pending data specific to the workflow, is usually how this problem is handled.

[Joril]

So I guess the idea is that each web request does a new PREPARE TRANSACTION with a unique xid, then when the results of all those requests are ready to be committed,  the "final" call then calls "COMMIT PREPARED" on the full list of xids.

I see... Many thanks for your timely explanation :)