Multiple transactions within request
Vlad K.
Hi all!
Is it possible to have multiple transactions within a request, and how
to start new one after transaction.commit() is manually invoked?
But maybe I'm asking wrong question. What I'm trying to do is
database-based process locking. A request must check for this lock
(basically a field in the database must be False) obtain it (set that
field to True, COMMIT!) and do some processing (interact with db some
more, COMMIT), release the lock (COMMIT), or return with no processing
if the lock cannot be obtained. The atomicity of the field value is
ensured with select ... for update.
Or maybe I'm off the mark altogether?
--
.oO V Oo.
Malthe Borch
> Is it possible to have multiple transactions within a request, and how to
> start new one after transaction.commit() is manually invoked?
A new one is always started after a call to commit(). In other words:
it just works.
\malthe
Vlad K.
How dumb of me for not trying that... thanks! It seems it also works for
transaction.abort(), except that invalidates previous session altogether.
.oO V Oo.
Vlad K.
Seems I spoke too fast, it doesn't just work.
The problem is if a transaction fails, in my case for IntegrityError. I
catch the transaction and act accordingly, even using savepoints
(transaction.savepoint()) to rollback only the innermost changes that
caused the failure, start new transaction but it fails with:
TransactionFailedError: An operation previously failed, with traceback
.oO V Oo.
Malthe Borch
> TransactionFailedError: An operation previously failed, with traceback
I think you have to issue a manual rollback in case of a transaction abort.
\malthe
Vlad K.
entire transaction is aborted. If I use savepoint.rollback()
(savepoint being given by transaction.savepoint()) I get
InvalidSavepointRollbackError.
The SQLAlchemy manual suggests that for each session.being_nested()
(which makes a savepoint) must be matched with one session.commit() or
session.rollback(). And I can't use session.commit or rollback in
Pyramid, must rely on transaction, and transaction.abort() (as it has
no .rollback()) invalidates all sessions in the transaction.
.oO V Oo.
mike bayer
Was just talking to Vlad over on the SQLAlchemy list. What is needed in this case is the ability to use SQLAlchemy's "begin_nested()" method, which emits a "SAVEPOINT" instruction against the relational database. When using this feature, the next call to rollback(), assuming the "nested" status is still in progress, will "ROLLBACK TO SAVEPOINT". My understanding is that we just need to know how to use this feature when using the zope.transaction extension to manage transactions. In particular, if zope.transaction intercepts calls to "Session.rollback" and prevents them, it needs to either provide its own means of managing a "nested" SQLAlchemy transaction, or needs to recognize SQLAlchemy rollback() calls that are in fact against a "nested" transaction, which would be via some further introspection on the SQLAlchemy session when the event is intercepted.
At some point I'll have the opportunity to work with zope.transaction firsthand and then we'll no longer need to educate zzzeek about such things. Until then please bear with me !
Vlad K.
Hi, thanks for joining us here. :)
Yes, zope.transaction does indeed support savepoints:
somesavepoint = transaction.savepoint()
And indeed I can see it being emitted in the SQL debug output:
2011-11-13 15:59:57,895 INFO [sqlalchemy.engine.base.Engine][worker 0]
SAVEPOINT sa_savepoint_1
2011-11-13 15:59:57,896 INFO [sqlalchemy.engine.base.Engine][worker 0] {}
2011-11-13 15:59:57,896 INFO [sqlalchemy.engine.base.Engine][worker 0]
INSERT INTO user_stats (user_id, tstamp, portal, ads_new, ads_modified)
VALUES (%(user_id)s, %(tstamp)s, %(portal)s, %(ads_new)s, %(ads_modified)s)
2011-11-13 15:59:57,897 INFO [sqlalchemy.engine.base.Engine][worker 0]
{'ads_modified': 0, 'user_id': 1, 'ads_new': 0, 'portal': 'XXXXXX',
'tstamp': datetime.date(2011, 11, 13)}
2011-11-13 15:59:57,898 INFO [sqlalchemy.engine.base.Engine][worker 0]
ROLLBACK TO SAVEPOINT sa_savepoint_1
2011-11-13 15:59:57,898 INFO [sqlalchemy.engine.base.Engine][worker 0] {}
2011-11-13 15:59:57,904 INFO [sqlalchemy.engine.base.Engine][worker 0]
BEGIN (implicit)
2011-11-13 15:59:58,100 ERROR [pyramid_debugtoolbar][worker 0] Exception
at http://localhost:6543/ads/view/28328?category=
The exception thrown in the last line is
TransactionFailedError: An operation previously failed, with traceback
And the traceback ends with:
IntegrityError: (IntegrityError) duplicate key value violates unique
constraint "user_stats_pkey"
Stemming from the transaction.commit() I manually issue in order to
match (in number) against savepoints issued. Even if I remove it
manually, it will still be called (and cause the exception) by the
transaction middleware outside of the request handler.
But that IntegrityError has been caught (right when the ROLLBACK is
issued above). Also the ROLLBACK is apparently done automatically by
zope.transaction because if I try it manually:
savepoint.rollback()
I get InvalidSavepointRollbackError
I tried pretty much every combo of with and without savepoints, manual
or automatic flushin, commits, rollbacks and aborts, I just can't get
the request to complete without any exceptions.
.oO V Oo.
> --
> You received this message because you are subscribed to the Google
> Groups "pylons-discuss" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/pylons-discuss/-/Jmji6_ohyCYJ.
> To post to this group, send email to pylons-...@googlegroups.com.
> To unsubscribe from this group, send email to
> pylons-discus...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/pylons-discuss?hl=en.
Chris McDonough
I hate to ask, but would it be possible to concoct a short Python script
that creates a SQLA DBSession (with the ZopeTransactionExtension
enabled) that issues transaction.commit/savepoint/abort commands that
demonstrates the problem being encountered? I'd be guessing if I tried
to help with this without that.
- C
Vlad K.
Sure, I'll whip something up.
Thanks.
.oO V Oo.
Vlad K.
Here:
https://gist.github.com/1363860
This simple test case works just fine, meaning the problem is somewhere
in my application. As you can see from the code, I tried both a
"standalone" test and through Pyramid WSGI chain checking if pyramid_tm
middleware possibly borks something somewhere, but both cases worked fine.
However, I can't really replicate a test scenario fully because the
problematic part of my application involves several models with
relationships, multiple updates. If I use EXACTLY the same flow for the
savepoint, ie:
sp = transaction.savepoint()
try:
... # Try insert
transaction.commit()
except IntegrityError:
transaction.abort()
... # Try Update
Then the result is:
2011-11-14 12:58:29,689 INFO [sqlalchemy.engine.base.Engine][worker 0]
BEGIN (implicit)
... SELECTs and UPDATEs
2011-11-14 12:58:29,726 INFO [sqlalchemy.engine.base.Engine][worker 0]
SAVEPOINT sa_savepoint_1
2011-11-14 12:58:29,726 INFO [sqlalchemy.engine.base.Engine][worker 0] {}
2011-11-14 12:58:29,727 INFO [sqlalchemy.engine.base.Engine][worker 0]
INSERT INTO user_stats (user_id, tstamp, portal, ads_new, ads_modified,
ads_deleted) VALUES (%(user_id)s, %(tstamp)s, %(portal)s, %(ads_new)s,
%(ads_modified)s, %(ads_deleted)s)
2011-11-14 12:58:29,727 INFO [sqlalchemy.engine.base.Engine][worker 0]
{'user_id': 1, 'ads_new': 0, 'ads_deleted': 0, 'tstamp':
datetime.date(2011, 11, 14), 'ads_modified': 0, 'portal': 'test'}
2011-11-14 12:58:29,728 INFO [sqlalchemy.engine.base.Engine][worker 0]
ROLLBACK TO SAVEPOINT sa_savepoint_1
2011-11-14 12:58:29,728 INFO [sqlalchemy.engine.base.Engine][worker 0] {}
2011-11-14 12:58:29,754 INFO [sqlalchemy.engine.base.Engine][worker 0]
BEGIN (implicit)
2011-11-14 12:58:29,756 INFO [sqlalchemy.engine.base.Engine][worker 0]
UPDATE user_stats SET ads_new=(user_stats.ads_new + %(ads_new_1)s),
ads_modified=(user_stats.ads_modified + %(ads_modified_1)s),
ads_deleted=(user_stats.ads_deleted + %(ads_deleted_1)s) WHERE
user_stats.user_id = %(user_id_1)s AND user_stats.portal = %(portal_1)s
AND user_stats.tstamp = %(tstamp_1)s
2011-11-14 12:58:29,756 INFO [sqlalchemy.engine.base.Engine][worker 0]
{'ads_deleted_1': 0, 'portal_1': 'test', 'ads_new_1': 0, 'tstamp_1':
datetime.date(2011, 11, 14), 'user_id_1': 1, 'ads_modified_1': 1}
2011-11-14 12:58:29,956 ERROR [pyramid_debugtoolbar][worker 0] Exception
at http://localhost:6543/ads/view/28328?category=
...
DetachedInstanceError: Parent instance <AdLand at 0x7ffb900faa90> is not
bound to a Session; lazy load operation of attribute 'ad_base' cannot
proceed
The exception being thrown when I, after the savepoint fail, try to READ
a value from a model read/updated within the outer transaction. The process:
0. enter view
1. session = DBSession()
2. load some models
3. change them with POSTed data (including adding/removing/updating
models through relationship class members)
4. session.flush() changes (possibly redundant because savepoint
unconditionally flushes)
5. make savepoint, try insert or update some statistical models (which
are not used or in any way related with those from the outer
transaction), commit or abort the savepoint
6. read some data from models loaded in step 2 (reading again here
because flush() in step 4 returns new IDs etc... so another phase of
update* is required)
7. session.flush() everything again
8. exit view
*update involving external resources like processing related uploaded
files which names depend on IDs and other data obtained after flush in
step 4
The DetachedInstanceError is thrown at step 6, more precisely:
if not ad.ad_base.main_image_id and len(ad.ad_base.images):
...
where ad is a "main" model, ad.ad_base is a relationship() with parent
(of another class), main_image_id is integer and ad.ad_base.images is a
relationship to yet another model. The DetachedInstance means the 'ad'
being detached, so ad.ad_base is void.
So somehow the transaction of the savepoint messes up with the external
session, even though there is no relationship between those and the
models inserted/updated within the inner transaction. No foreignkey or
relationship() connection.
.oO V Oo.
Vlad K.
Eh... please excuse my haste, having ton of work this morning and my
brain is already melting... :)
It fails after all, exactly the same way as in my app:
https://gist.github.com/1363906
Output:
Outer transaction...
First pass...
INSERTED
Second pass...
UPDATED
tm.name:
Traceback (most recent call last):
File "__init__.py", line 108, in <module>
main(None)
File "__init__.py", line 98, in main
my_view(None)
File "__init__.py", line 75, in my_view
print "tm.name:", tm.name
File
"/home/vlad/dev/Test/virtualenv/lib/python2.7/site-packages/SQLAlchemy-0.7.3-py2.7-linux-x86_64.egg/sqlalchemy/orm/attributes.py",
line 168, in __get__
return self.impl.get(instance_state(instance),dict_)
File
"/home/vlad/dev/Test/virtualenv/lib/python2.7/site-packages/SQLAlchemy-0.7.3-py2.7-linux-x86_64.egg/sqlalchemy/orm/attributes.py",
line 422, in get
value = callable_(passive)
File
"/home/vlad/dev/Test/virtualenv/lib/python2.7/site-packages/SQLAlchemy-0.7.3-py2.7-linux-x86_64.egg/sqlalchemy/orm/state.py",
line 271, in __call__
self.manager.deferred_scalar_loader(self, toload)
File
"/home/vlad/dev/Test/virtualenv/lib/python2.7/site-packages/SQLAlchemy-0.7.3-py2.7-linux-x86_64.egg/sqlalchemy/orm/mapper.py",
line 1589, in _load_scalar_attributes
(state_str(state)))
sqlalchemy.orm.exc.DetachedInstanceError: Instance <TestModel at
0x1bf58d0> is not bound to a Session; attribute refresh operation cannot
proceed
.oO V Oo.
Chris McDonough
> Here:
>
> https://gist.github.com/1363860
>
> This simple test case works just fine, meaning the problem is somewhere
> in my application. As you can see from the code, I tried both a
> "standalone" test and through Pyramid WSGI chain checking if pyramid_tm
> middleware possibly borks something somewhere, but both cases worked fine.
I think the issue here is that (for better or worse), calling
transaction.commit() causes the session to be closed, which detaches the
objects obtained from that session. The following iteration on your
script actually completes (note that I requery for "tm" after "second
pass")....
import transaction
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Unicode
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
from zope.sqlalchemy import ZopeTransactionExtension
DBSession =
scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()
class TestModel(Base):
__tablename__ = "test"
id = Column(Integer, primary_key=True, autoincrement=False)
name = Column(Unicode)
value = Column(Integer)
def __init__(self, id):
self.id = id
self.name = u"Hello World!"
self.value = 123
def initialize_sql(engine):
DBSession.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
def insert_or_update():
session = DBSession()
savepoint = transaction.savepoint()
try:
# Try insert as new
tm = TestModel(1)
session.add(tm)
transaction.commit()
print "INSERTED"
except IntegrityError:
transaction.abort()
# Update instead (part of outer transaction)
session.query(TestModel)\
.filter_by(id=1)\
.update({"name" : u"Hello!", "value" : 123})
print "UPDATED"
def my_view(request):
session = DBSession()
print "Outer transaction..."
tm = session.query(TestModel).get(999) or TestModel(999)
tm = session.merge(tm)
print "First pass..."
insert_or_update()
print "Second pass..."
insert_or_update()
tm = session.query(TestModel).get(999) or TestModel(999)
print "tm.name:", tm.name
print "Committing..."
transaction.commit()
print "Testing..."
tm = session.query(TestModel).get(1)
print "1:", "ok" if tm else "not found"
tm = session.query(TestModel).get(999)
print "999:", "ok" if tm else "not found"
tm = session.query(TestModel).get(100)
print "100:", "error" if tm else "ok"
return "Check console output..."
def main(global_config, **settings):
engine = create_engine("postgresql://test:test@localhost:5432/test")
initialize_sql(engine)
my_view(None)
"""
config = Configurator(settings=settings)
config.add_route('home', '/')
config.add_view(my_view, route_name='home', renderer='string')
return config.make_wsgi_app()
"""
if __name__ == "__main__":
main(None)
Vlad K.
For worse I'd say because using SQLAlchemy directly works just fine and
as expected, without the need to reload the data after failed session.
Is there any benefit of the Transaction in Pyramid if all I'm using is
it for is SQLAlchemy (and don't need transaction abstraction)? In fact
I'm only using it because pyramid_routesalchemy template uses it by default.
Aside to commit veto in the middleware, am I losing anything if I remove
pyramid_tm altogether and use SQLAlchemy directly? Yes, I know I'll have
to manually commit at the end of each request or write my own middleware...
Thanks! ;)
.oO V Oo.
Chris McDonough
> For worse I'd say because using SQLAlchemy directly works just fine and
> as expected, without the need to reload the data after failed session.
>
> Is there any benefit of the Transaction in Pyramid if all I'm using is
> it for is SQLAlchemy (and don't need transaction abstraction)? In fact
> I'm only using it because pyramid_routesalchemy template uses it by default.
>
> Aside to commit veto in the middleware, am I losing anything if I remove
> pyramid_tm altogether and use SQLAlchemy directly? Yes, I know I'll have
> to manually commit at the end of each request or write my own middleware...
You can use it or not use it as you see fit.
- C
Chris McDonough
> On Mon, 2011-11-14 at 20:30 +0100, Vlad K. wrote:
> > For worse I'd say because using SQLAlchemy directly works just fine and
> > as expected, without the need to reload the data after failed session.
> >
> > Is there any benefit of the Transaction in Pyramid if all I'm using is
> > it for is SQLAlchemy (and don't need transaction abstraction)? In fact
> > I'm only using it because pyramid_routesalchemy template uses it by default.
> >
> > Aside to commit veto in the middleware, am I losing anything if I remove
> > pyramid_tm altogether and use SQLAlchemy directly? Yes, I know I'll have
> > to manually commit at the end of each request or write my own middleware...
>
> You can use it or not use it as you see fit.
Out of curiosity, why are you committing in the middle of view logic?
It's none of my business really, but session.flush() would seem to get
you what you want and would work fully within the one-request-one-commit
policy.
- C
Vlad K.
Because each savepoint must be met by one commit or one rollback. If I
use session.flush(), the second call would start savepoint 2, but would
not release savepoint 1 (nor roll it back). While that may work "fine"
for this case, I am not sure I want that in the real app where situation
is much more complex.
http://www.sqlalchemy.org/docs/orm/session.html#using-savepoint
Also, this answers my other problem I posted a few days ago about using
database-based process "locks" with select ... for update. I need
multiple commits within single request.
.oO V Oo.
Chris McDonough
> Because each savepoint must be met by one commit or one rollback. If I
> use session.flush(), the second call would start savepoint 2, but would
> not release savepoint 1 (nor roll it back). While that may work "fine"
> for this case, I am not sure I want that in the real app where situation
> is much more complex.
>
>
> http://www.sqlalchemy.org/docs/orm/session.html#using-savepoint
>
>
> Also, this answers my other problem I posted a few days ago about using
> database-based process "locks" with select ... for update. I need
> multiple commits within single request.
I'll defer to you on this, but I'll note that the "savepoint" object you
get back from calling transaction.savepoint() has a "rollback" method
FWIW.
- C
Michael Bayer
On Nov 14, 2011, at 2:44 PM, Chris McDonough wrote:
>
> Out of curiosity, why are you committing in the middle of view logic?
> It's none of my business really, but session.flush() would seem to get
> you what you want and would work fully within the one-request-one-commit
> policy.
hear, hear !
Vlad K.
When using Transaction the rollback is emitted implicitly either by
Transaction or by zope.sqlalchemy, and manually calling
savepoint.rollback() after a failure results with
InvalidSavepointRollbackError. At least that was the case when I tried
that, please see earlier posts in this topic.
Thanks!
.oO V Oo.
Vlad K.
This has turned into a much bigger issue than I originally thought it would.
As suggested by Chris here and Michael on the SQLAlchemy list where I
asked first about refreshing sessions, this problem is solvable if you
reload any model you need after the rollback. But all this fails if I
deal with lists of model instances. I can't reload the list and go
through it again, over and over if any of the list elements causes a
rollback...
It seems to me this is a major bug wrt the Transaction package, unless
there is a solution which I'm currently oblivious to. So the review what
is going on:
* A subtransaction (created with transaction.savepoint()) fails, eg.
raises IntegrityError
- Transaction emits rollback (manually invoking
savepoint.rollback() fails)
- If transaction.abort() is not called, the entire request then
fails with InvalidRequestError: This Session's transaction has been
rolled back due to a previous exception during flush
- If transaction.abort() is called, the outer transaction (every
model loaded into session between BEGIN and SAVEPOINT) is invalidated
and requires manual reloading
- manual reloading is impossible if you're in the middle of a loop
through a list of loaded models
Please let me know if I'm being silly or dumb and not seeing something
too obvious, because I'm still learning both SQLAlchemy and how to
properly use the Transaction package, and dealing with IntegrityErrors
in the middle of a view without invalidating entire transaction is IMHO
not some borderline or arcane usage of transactions.
Currently my only choice is abandoning Transaction and writing my own
tween to properly commit or rollback the transaction in uncaught
exceptions -- basically rewrite pyramid_tm not to use Transaction. If I
can avoid that (and rewriting the app to use session directly instead of
transaction), it would be great!
Many thanks!
.oO V Oo.
On 11/14/2011 07:50 PM, Chris McDonough wrote:
Chris McDonough
> This has turned into a much bigger issue than I originally thought it would.
>
> As suggested by Chris here and Michael on the SQLAlchemy list where I
> asked first about refreshing sessions, this problem is solvable if you
> reload any model you need after the rollback. But all this fails if I
> deal with lists of model instances. I can't reload the list and go
> through it again, over and over if any of the list elements causes a
> rollback...
>
>
>
> It seems to me this is a major bug wrt the Transaction package, unless
> there is a solution which I'm currently oblivious to. So the review what
> is going on:
>
> * A subtransaction (created with transaction.savepoint()) fails, eg.
> raises IntegrityError
>
> - Transaction emits rollback (manually invoking
> savepoint.rollback() fails)
> - If transaction.abort() is not called, the entire request then
> fails with InvalidRequestError: This Session's transaction has been
> rolled back due to a previous exception during flush
This is the first issue to investigate, I think. Can you provide some
code that does something you want to do that has this/these outcome(s)?
I can ship it off to the z.sqla maintainer and see if it's expected
behavior or what.
Chris McDonough
> as expected, without the need to reload the data after failed session.
By the way, are you sure about this? I've heard that when you commit or
abort a "raw" SQLA session, the outcome is the same. Objects loaded
from a finished SQLA session become invalidated without the
"expire_on_commit=False" (not the default) argument to the sessionmaker.
Am I wrong about that?
- C
Michael Bayer
The SQLA session invalidates everything after a commit or a rollback. If expire_on_commit=False, then you're OK after the commit, but still if the rollback happens, everything is expired. There's a way to turn that off too but then you're really in the "you're doing it wrong" area.
Overall, the use case here I thought was to use begin_nested(), i.e. SAVEPOINT. Nothing gets expired when you commit on a SAVEPOINT since you're still within the original transaction.
Chris McDonough
OK. So I think if we can understand (and fix, if necessary) the
savepoint issue, Vlad will be able to do everything he could do without
zope.sqla in a very similar way then.
- C
Vlad K.
Okay, I guess I have fixed it. I say I guess because the solution
between my app and a simple test scenario is different. The new test
scenario is here:
https://gist.github.com/1366878
Basically the same as before, but now you can vary between using
Transaction or SQLAlchemy directly with the two vars at the beginning.
What happens is the following:
* The test case in gist:
- works fine if called standalone and transaction.abort() is not called
- if transaction.abort() is called then the models loaded in outer
transaction are expired
- if called as Pyramid view (ie. by starting paster serve
development.ini) and transaction.abort() is not called, it hangs (using
unchanged development.ini as given by pyramid_routesalchemy template)
* My app:
- if transaction.abort() is called, it behaves the same as test
case: models are expired, reloading required
- if transaction.abort() is NOT called, then InvalidRequestError is
issued by SQLAlchemy because of "previous exception during flush". I am
not quite sure why it happens in my app and not in the test case.
Solution in my app:
call session.rollback() instead of transaction.abort()
I have also removed transaction.commit() in the middle of view and am
using session.flush() exclusively, now along with session.rollback().
Why I didn't think of this earlier? Transaction complains if you use
session.commit() or session.begin_nested() directly, wants you to use
transaction.commit() and transaction.savepoint() instead and it just
didn't occur to me to try session.rollback() nevertheless (and in my
mind transaction.abort() == session.rollback() which now I see is NOT
the same), and trying savepoint.rollback() fails, I assumed
session.rollback() was called by Transaction since the SQL debug output
clearly shows savepoint rollback being emitted, so I went to search for
another solution.
Aside from me being silly for not trying this before (and it is even
suggested by the InvalidRequestError!), it is a bit illogical to have to
use transaction.savepoint() and then use session.rollback() instead of
savepoint.rollback().
I am still not sure if using session.rollback() manually is the right
solution. My app didn't fail this time, the SQL debug output shows
everything as expected (savepoint issued, savepoint rolled back, outer
transaction committing without failure and without the need to reload
models after rollback) and I still have to test a dozen other parts of
the code where IntegrityError handling is being done. I also have to
test the scenario where I explicitly must commit the transaction several
times in the life of a Pyramid request.
If it matters, relevant pip freeze for the virtualenv used:
Chameleon==2.5.1
Jinja2==2.6
Mako==0.5.0
MarkupSafe==0.15
PIL==1.1.7
Paste==1.7.5.1
PasteDeploy==1.5.0
PasteScript==1.7.4.2
Pygments==1.4
SQLAlchemy==0.7.3
WebOb==1.1.1
WebTest==1.3.1
colander==0.9.3
elementtree==1.2.7-20070827-preview
iso8601==0.1.4
peppercorn==0.3
psycopg2==2.4.2
pyramid==1.2.1
pyramid-debugtoolbar==0.9.4
pyramid-jinja2==1.2
pyramid-tm==0.3
repoze.lru==0.4
requests==0.6.1
simplejson==2.2.1
transaction==1.1.1
translationstring==0.4
venusian==1.0a1
wsgiref==0.1.2
zope.component==3.11.0
zope.deprecation==3.5.0
zope.event==3.5.1
zope.interface==3.8.0
zope.sqlalchemy==0.6.1
Thanks! ;)
.oO V Oo.
Michael Bayer
On Nov 15, 2011, at 7:09 AM, Vlad K. wrote:
>
>
> Why I didn't think of this earlier? Transaction complains if you use session.commit() or session.begin_nested() directly, wants you to use transaction.commit() and transaction.savepoint() instead and it just didn't occur to me to try session.rollback() nevertheless (and in my mind transaction.abort() == session.rollback() which now I see is NOT the same), and trying savepoint.rollback() fails, I assumed session.rollback() was called by Transaction since the SQL debug output clearly shows savepoint rollback being emitted, so I went to search for another solution.
>
> Aside from me being silly for not trying this before (and it is even suggested by the InvalidRequestError!), it is a bit illogical to have to use transaction.savepoint() and then use session.rollback() instead of savepoint.rollback().
glad you figured this out. Now we need to adjust zope.sqlalchemy's API and/or documentation so that the SAVEPOINT use case is made clear. I would think that since SAVEPOINTs can be per-connection, perhaps zope.sqlalchemy would support begin_nested() on individual sessions...or maybe not.
Michael Merickel
--
You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-...@googlegroups.com.
To unsubscribe from this group, send email to pylons-discus...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en.
Michael
Chris McDonough
> It's been hard to follow what has actually been tried, but I just
> wanted to point some stuff out about zope.sqlalchemy (the code for it
> is literally 1 small file and shouldn't be talked about with such a
> scary tone).
>
>
> When doing transaction.savepoint() it returns a savepoint object that
> calls "session.begin_nested()". and when you call rollback on that
> savepoint object it calls "session.rollback()". I think that is
> identical to SQLAlchemy's expected use. For example:
>
>
> sp = transaction.savepoint() # session.begin_nested()
> try:
> # do stuff
> except IntegrityError:
> sp.rollback() # session.rollback()
>
>
> # do more stuff
> transaction.commit() # or ideally don't commit and let pyramid_tm do
> that part for you
Vlad: it'd be useful to know whether this (savepoint.rollback()) doesn't
work for you (as opposed to using session.rollback()).
- C
> To unsubscribe from this group, send email to pylons-discuss
> +unsub...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/pylons-discuss?hl=en.
>
>
>
>
>
>
> --
>
> Michael
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "pylons-discuss" group.
> To post to this group, send email to pylons-...@googlegroups.com.
> To unsubscribe from this group, send email to pylons-discuss
> +unsub...@googlegroups.com.
Vlad K.
Yes it actually works if I use session.flush() instead of
transaction.commit() within the subtransaction. So I guess
transaction.commit() and transaction.abort() close the entire
transaction unlike session.commit() which when matched against
session.begin_nested() releases the savepoint but keeps outer
transaction intact. Obviously I failed to try all combinations before
when using savepoint to rollback would raise an error (because the
transaction was finalized with transaction.commit()).
Now looking back at all these tests performed and various combinations,
it does all make sense and it was my wrong understanding that I could
use savepoints as "real" subtransactions, ie. commit (to disc)
everything between savepoint and its commit regardless of the "outer"
transaction, although the other way around works: outer transaction can
be committed if "inner" rolled back.
I still don't know how I can manually release the savepoint using
Transaction, or even if that is necessary at all? Does releasing
savepoint_1 "return" its code back to the "outer" transaction, so that
savepoint_2's failure would not rollback even savepoint_1?
So basically this entire issue was caused by my lack of experience with
savepoints and total misunderstanding of how the Transaction package
works as opposed to using SQLA's session directly.
.oO V Oo.
Michael Merickel
--
You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
To post to this group, send email to pylons-discuss@googlegroups.com.
To unsubscribe from this group, send email to pylons-discuss+unsubscribe@googlegroups.com.
Michael
Vlad K.
According to my tests it is, or at least the end result is savepoint
release being emitted before final commit. For this code:
sp = transaction.savepoint()
... do something
session.flush()
sp2 = transaction.savepoint()
... do something
session.flush()
# Oops, IntegrityError
sp.rollback()
...
transaction.commit()
The following SQL is emitted:
BEGIN (implicit)
...
SAVEPOINT sa_savepoint_1
...
SAVEPOINT sa_savepoint_2
ROLLBACK TO SAVEPOINT sa_savepoint_2
...
RELEASE SAVEPOINT sa_savepoint_1
COMMIT
The test (resulting with the SQL above) is posted here. You can vary the
params at the beginning to try various combinations with or without the
Transaction.
https://gist.github.com/1370792
.oO V Oo.
Chris Withers
>
> Currently my only choice is abandoning Transaction and writing my own
> tween to properly commit or rollback the transaction in uncaught
> exceptions -- basically rewrite pyramid_tm not to use Transaction.
A pyramid_sa_tm would likely be something that a lot of people would use
and be interested in, the usecase being that people want to use SA but
only SA, and not any other type of transaction (which is where you
really want the transaction package).
It'd also be pretty thin and should be quite easy to write ;-)
cheers,
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk