joining sessions / two phase commit
Chris Withers
I'm wondering where I can find out more about the kind of thing
described here:
http://www.sqlalchemy.org/docs/session.html#enabling-two-phase-commit
Here's the situation:
engine = create_engine(...encoding='utf-8', pool_recycle=3600)
SessionCls = sessionmaker(bind=engine, autoflush=True, autocommit=False)
...stuff...
session1 = SessionCls()
...do ORM-ish stuff in session1..
session2 = sessionmaker(create_engine('mysql://server/legacy_db,
echo=False,
pool_recycle=3600))()
session2.execute('raw sql update',{...some keywords to substitute...}
Now, what we'd like to do at this point is have both session1 and
session2 commit or rollback in a two-phase manner.
How can we achieve this?
cheers,
Chris
Michael Bayer
you would use set twophase=True on both sessions so that they call
begin_prepared() upon transaction start, then use the prepare() method on
both sessions, then commit() on both.
Alternatively, you'd stick both engines in one session, enable
twophase=True, and that Session will do the prepare()/commit() on both
engines for you.
>
> cheers,
>
> Chris
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> 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.
>
>
Chris Withers
>> session1 = SessionCls()
>>
>> ...do ORM-ish stuff in session1..
>>
>> session2 = sessionmaker(create_engine('mysql://server/legacy_db,
>> echo=False,
>> pool_recycle=3600))()
>>
>> session2.execute('raw sql update',{...some keywords to substitute...}
>>
>> Now, what we'd like to do at this point is have both session1 and
>> session2 commit or rollback in a two-phase manner.
>>
>> How can we achieve this?
>
> you would use set twophase=True on both sessions so that they call
> begin_prepared() upon transaction start, then use the prepare() method on
> both sessions, then commit() on both.
>
> Alternatively, you'd stick both engines in one session, enable
> twophase=True, and that Session will do the prepare()/commit() on both
> engines for you.
Do you have any good examples of both of these patterns for the hard of
thinking? (ie: me!)
The problem is that session2 (and it's engine) are only created in a
small part of the code, while session1 is created in a much wider
encompassing framework. As such, there's no obvious way to get session1
to the piece of code that calls commit on session2.
(an aside: what happens here, assuming the first of your possibilities:
session1.commit()
raise RuntimeError('something goes bang')
session2.commit())
Creating both engines in the outer loop doesn't feel right, since the
engine for session2 is only going to be used for one type of batch job,
of which there are many more that won't use it at all...
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
Michael Bayer
> Do you have any good examples of both of these patterns for the hard of
> thinking? (ie: me!)
>
> The problem is that session2 (and it's engine) are only created in a
> small part of the code, while session1 is created in a much wider
> encompassing framework. As such, there's no obvious way to get session1
> to the piece of code that calls commit on session2.
>
>
> engine for session2 is only going to be used for one type of batch job,
> of which there are many more that won't use it at all...
I've never actually used two phase transactions myself. But the idea is
to get your two pieces of the puzzle working together. Using the
sessions separately is fine, something just needs to mediate the bigger
picture using them both. But you could also bind your second engine into
the app-wide session too. Or build a third session with both engines
temporarily - if your major session is contextual you could patch it in.
> (an aside: what happens here, assuming the first of your possibilities:
> session1.commit()
> raise RuntimeError('something goes bang')
> session2.commit())
session1's data would be fully committed. session2's commit never gets
reached. the point of prepare() is so that all involved transactions hit
a point of "OK I'm definitely good to go" first, then the commits happen.
>
> Chris
>
> --
> Simplistix - Content Management, Batch Processing & Python Consulting
> - http://www.simplistix.co.uk
>
Ants Aasma
> The problem is that session2 (and it's engine) are only created in a
> small part of the code, while session1 is created in a much wider
> encompassing framework. As such, there's no obvious way to get session1
> to the piece of code that calls commit on session2.
>
> (an aside: what happens here, assuming the first of your possibilities:
> session1.commit()
> raise RuntimeError('something goes bang')
> session2.commit())
This is the reason why you need a transaction manager when using two-
phase transactions. The second transaction will remain in a prepared
state (modifications not visible to other transactions, still holding
any locks), even after a database crash and restart. The transaction
manager needs to ensure that all transactions in a group either get
committed or are rolled back. This should preferably be an automatic
process, as any prepared transactions left hanging will grind your
database to a halt pretty quickly.
Ants
Chris Withers
> On Feb 4, 12:41 am, Chris Withers <ch...@simplistix.co.uk> wrote:
>> The problem is that session2 (and it's engine) are only created in a
>> small part of the code, while session1 is created in a much wider
>> encompassing framework. As such, there's no obvious way to get session1
>> to the piece of code that calls commit on session2.
>>
>> (an aside: what happens here, assuming the first of your possibilities:
>> session1.commit()
>> raise RuntimeError('something goes bang')
>> session2.commit())
>
> This is the reason why you need a transaction manager when using two-
> phase transactions.
Okay, but what does the transaction manager do that's different from
calling commit on session1 and session2 in order?
> The second transaction will remain in a prepared
> state (modifications not visible to other transactions, still holding
> any locks), even after a database crash and restart.
So how do you un-f?$k it then? ;-)
> The transaction
> manager needs to ensure that all transactions in a group either get
> committed or are rolled back. This should preferably be an automatic
> process, as any prepared transactions left hanging will grind your
> database to a halt pretty quickly.
I know that zope's transaction package aims to do just this, I wonder if
anyone's used that, or anything else, with SA to solve this problem?
cheers,
Chris
Wichert Akkerman
> I know that zope's transaction package aims to do just this, I wonder if
> anyone's used that, or anything else, with SA to solve this problem?
You mean ZODB's transaction package? :). I use that all the time to get
transactions working across multiple storage systems. Most commonly
using repoze.tm2 to integrate with a WSGI stack, zope.sqlalchemy to
integrate SQLAlchemy with transaction and repoze.filesafe to do
transaction-safe file creation.
Wichert.
--
Wichert Akkerman <wic...@wiggy.net> It is simple to make things.
http://www.wiggy.net/ It is hard to make things simple.
Ants Aasma
> Okay, but what does the transaction manager do that's different from
> calling commit on session1 and session2 in order?
A TM should write to durable storage when a transaction group is
prepared before committing any transaction. When doing crash recovery
this information must be used to decide whether to commit or rollback
the rest of the prepared transactions.
> > The second transaction will remain in a prepared
> > state (modifications not visible to other transactions, still holding
> > any locks), even after a database crash and restart.
>
> So how do you un-f?$k it then? ;-)
For MySQL you can obtain the list of prepared transactions with the XA
RECOVER command. You can then use XA COMMIT or XA ROLLBACK commands as
appropriate to handle them.
> I know that zope's transaction package aims to do just this, I wonder if
> anyone's used that, or anything else, with SA to solve this problem?
I've only used two phase for the relatively trivial case of doing
filesystem updates atomically along with metadata updates in the
database. The zope transaction package doesn't seem to have any
disaster recovery story, but maybe I'm missing something. Depending on
your exact environment and requirements you might also find an easier
way, but be very-very careful with distributed transactions. It's a
really hard problem to get 100% correct in the face of arbitrary
software, network and hardware failures.
Ants
Chris Withers
> On 2010-2-9 09:48, Chris Withers wrote:
>> I know that zope's transaction package aims to do just this, I wonder if
>> anyone's used that, or anything else, with SA to solve this problem?
>
> You mean ZODB's transaction package?
it's actually now just a standalone package ;-)
http://pypi.python.org/pypi/transaction
Antoine Pitrou
> Wichert Akkerman wrote:
> > On 2010-2-9 09:48, Chris Withers wrote:
> >> I know that zope's transaction package aims to do just this, I wonder if
> >> anyone's used that, or anything else, with SA to solve this problem?
> >
> > You mean ZODB's transaction package?
>
> it's actually now just a standalone package ;-)
>
> http://pypi.python.org/pypi/transaction
I've found it to be an active nuisance. For example it will forbid the
use of commit() on the session, forcing you to use transaction.commit()
instead.
And transaction.commit() has the annoying "side effect" that it will
also purge the session afterwards, making all your objects unusable
unless you go out of your way to fetch them again manually.
(unfortunately, this package is used by TurboGears by default)
Regards
Antoine.
Wichert Akkerman
> Le dimanche 14 f�vrier 2010 � 17:47 +0000, Chris Withers a �crit :
>> Wichert Akkerman wrote:
>>> On 2010-2-9 09:48, Chris Withers wrote:
>>>> I know that zope's transaction package aims to do just this, I wonder if
>>>> anyone's used that, or anything else, with SA to solve this problem?
>>>
>>> You mean ZODB's transaction package?
>>
>> it's actually now just a standalone package ;-)
>>
>> http://pypi.python.org/pypi/transaction
>
> I've found it to be an active nuisance. For example it will forbid the
> use of commit() on the session, forcing you to use transaction.commit()
> instead.
That is by design: zope.sqlalchemy (which is really the thing you are
complaining about) forces you to commit the entire transaction. This is
required to coordinate transactions between multiple participants in a
transaction, prevent one of them from getting out of sync.
> And transaction.commit() has the annoying "side effect" that it will
> also purge the session afterwards, making all your objects unusable
> unless you go out of your way to fetch them again manually.
Iirc it starts a new session, making sure that you do not leak things
between transactions.
Wichert.
Antoine Pitrou
>
> That is by design: zope.sqlalchemy (which is really the thing you are
> complaining about) forces you to commit the entire transaction. This is
> required to coordinate transactions between multiple participants in a
> transaction, prevent one of them from getting out of sync.
I understand this is by design, but better again would have been a flag
to disable it.
> > And transaction.commit() has the annoying "side effect" that it will
> > also purge the session afterwards, making all your objects unusable
> > unless you go out of your way to fetch them again manually.
>
> Iirc it starts a new session, making sure that you do not leak things
> between transactions.
Well, there are situations where you'd want to "leak" things between
transactions... If SQLAlchemy itself doesn't enforce it, I'm not sure
why the "transaction" package thinks it should.
Regards
Antoine.
Wichert Akkerman
> Le dimanche 14 f�vrier 2010 � 19:45 +0100, Wichert Akkerman a �crit :
>>
>> That is by design: zope.sqlalchemy (which is really the thing you are
>> complaining about) forces you to commit the entire transaction. This is
>> required to coordinate transactions between multiple participants in a
>> transaction, prevent one of them from getting out of sync.
>
> I understand this is by design, but better again would have been a flag
> to disable it.
I have never seen a request for such a flag before, which makes me
suspect it is not a widely requested feature. You are more than welcome
to contribute such a flag to zope.sqlalchemy though.
>>> And transaction.commit() has the annoying "side effect" that it will
>>> also purge the session afterwards, making all your objects unusable
>>> unless you go out of your way to fetch them again manually.
>>
>> Iirc it starts a new session, making sure that you do not leak things
>> between transactions.
>
> Well, there are situations where you'd want to "leak" things between
> transactions... If SQLAlchemy itself doesn't enforce it, I'm not sure
> why the "transaction" package thinks it should.
If you are trying to put blame anywhere do it in the right place: the
'transaction' package does not do this. The SQLAlchemy transaction glue
in the zope.sqlalchemy package does.
Wichert.
Antoine Pitrou
> On 2/14/10 19:54 , Antoine Pitrou wrote:
> >>
> >> That is by design: zope.sqlalchemy (which is really the thing you are
> >> complaining about) forces you to commit the entire transaction. This is
> >> required to coordinate transactions between multiple participants in a
> >> transaction, prevent one of them from getting out of sync.
> >
> > I understand this is by design, but better again would have been a flag
> > to disable it.
>
> I have never seen a request for such a flag before, which makes me
> suspect it is not a widely requested feature. You are more than welcome
> to contribute such a flag to zope.sqlalchemy though.
Well the primary use for it is that session.commit() doesn't expunge the
session, while transaction.commit() does. So the real fix, or
improvement, would be to change or at least make configurable
transaction.commit()'s behaviour.
> If you are trying to put blame anywhere do it in the right place: the
> 'transaction' package does not do this. The SQLAlchemy transaction glue
> in the zope.sqlalchemy package does.
Oh, sorry. That doesn't really change my point, though, since we are
talking about use of "transaction" in an SQLAlchemy context.
Regards
Antoine.
Chris Withers
> Oh, sorry. That doesn't really change my point, though, since we are
> talking about use of "transaction" in an SQLAlchemy context.
Which doesn't imply using zope.sqlalchemy...