Rolling back transactions in TG2
El Tea
grasp of SQL Alchemy.
I am adding a set of entries to the database, one at a time. Before
adding each entry I check to ensure a similar entry is not in the
database. If there is, I consider the entire set invalid and attempt
to do a rollback. So, in pseudocode:
Loop through my input data:
Query DB to check for duplicate
if not duplicate:
DBSession.add(entry)
if duplicate:
DBSession.rollback()
return #Returns to the controller method, which then returns result
data for rendering.
I keep getting the following error:
InvalidRequestError: The transaction is closed
Can anyone give me some guidance on this?
Diez B. Roggisch
Transactions in TG2 are managed through the repoze.tm2 middleware, *not*
by you. Unless of course you rip that out, as we did. But that's another
story.
However, in any case the above code is faulty. You don't rollback a
transaction when you'd have a duplicate entry, because then you'd
rollback *all* the previously successfull creations. That's the way
transactions work.
Instead, you do nothing.
And if you want to make 100% sure you don't run into a race-condition,
you first lock the table you are working with, because otherwise it can
happen that between checking for a row, and creating it, it is created
by another request.
Diez
El Tea
-Are you telling me that I cannot roll back transactions unless I rip
out the middleware? There's no accessible command to the middleware
with TG2 as it ships?
-How can I lock a table? I thought that was done by TG as well - that
each call to a controller made the database safe to access.
-I actually do want to roll back *all* the previous transactions made
in that sessions. It was an all-or-nothing scenario. If there's a
single duplicate in that database, then I want to remove all of the
new entries added during this call. I would expect rollback()
wouldn't remove items already in the database from a previous call to
the controller (which ends with a commit() as I understand it, perhaps
in repoze.tm2 as you point out?)
If this is documented from a TG2 perspective somewhere, I would love
to know where - I'm not afraid of reading, but figuring out how SQL
Alchemy is integrated with TG2 has been difficult. Thanks for the
help.
El Tea
Diez B. Roggisch
> out the middleware? There's no accessible command to the middleware
> with TG2 as it ships?
I think you want then is
import transaction
transaction.doom()
That will mark an ongoing transaction as failed, and provoke a rollback on
middleware egress.
> -How can I lock a table? I thought that was done by TG as well - that
> each call to a controller made the database safe to access.
Nope. And it wouldn't be desirable, because that would mean that the system
would slowdown to a screeching halt, and automatic locking is likely to
create deadlocks.
>
> -I actually do want to roll back *all* the previous transactions made
> in that sessions. It was an all-or-nothing scenario. If there's a
> single duplicate in that database, then I want to remove all of the
> new entries added during this call. I would expect rollback()
> wouldn't remove items already in the database from a previous call to
> the controller (which ends with a commit() as I understand it, perhaps
> in repoze.tm2 as you point out?)
A transaction spans usually a request/response-cycle (this is the way the
system is set up, you'd have to manually alter a lot of things to make it
work different), and the very nature of transactions is that they are
isolated from each other. So if you have ten calls to the action, and
rollback the last, then you'd have still the work of the 9 successful ones in
the DB.
Diez
El Tea
remove would indeed be during that single request/response cycle.
I've since learned I might be able to get the same effect by throwing
an exception, but again, I'm lacking documentation and I don't know if
I would have to catch it myself or what exception specifically to
throw.
I would still welcome any information on locking a table (even though
it's not actually required in my case). I understand your comment
about slowing down the system, as well as deadlocks.
Mike
Diez B. Roggisch
> Thanks, that was exactly what I wanted! Everything I would want to
> remove would indeed be during that single request/response cycle.
> I've since learned I might be able to get the same effect by throwing
> an exception, but again, I'm lacking documentation and I don't know if
> I would have to catch it myself or what exception specifically to
> throw.
>
> I would still welcome any information on locking a table (even though
> it's not actually required in my case). I understand your comment
> about slowing down the system, as well as deadlocks.
from sqlalchemy.sql import text
DBSession.execute(text("lock table %s" % tablename))
Diez