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
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
from sqlalchemy.sql import text
DBSession.execute(text("lock table %s" % tablename))
Diez