Mailing List Archive

Jim Abramson wrote:
> Hello all,
>
> As has been mentioned many times on that list, calling explicit 'commit' on the db connection you
> are using during a Zope request is a no-no...
>
> My question is, if i grab a reference to the cursor from some db connection inside a python
> script, and do a bunch of statements directly on this cursor, and something goes wrong on the nth
> statement (i.e. uncaught Exception), should I expect all of the statements performed using that
> cursor up to that point will be automatically rolled back?

Yes.

> If yes, next question is, if I trap an exception that's raised during the nth statement, and
> proceed (using the same cursor) to the (n+1)th statement, and the REQUEST wraps up cleanly,
> should I expect all of the successful statements will be automatically committed?

Yes.


That ways easy :-)

Database transactions are handled on the connection, not the cursors,
so unless you connection dies in the middle of processing a request,
anything you do on the connection will get committed if Zope finds
that processing was successful, that is in particular, no unhandled
exception was raised.

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jul 02 2004)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Thanks a lot for that advice, very helpful.

And now for the finale:

Is there a reliable way, somewhere within a series of statements during the request, to effectively commit the connection (without, of course, commit()!) i've seen 'select sysdate from dual' work at times, as a way of 'committing', but I don't know exactly how dependable this is, or what happens under the hood.

and the converse is also interesting: is there any reasonable way to 'fool' the tx manager by...uh... letting an exception go up a few levels before trapping it?

Best,
Jim

> > My question is, if i grab a reference to the cursor from
> some db connection inside a python
> > script, and do a bunch of statements directly on this
> cursor, and something goes wrong on the nth
> > statement (i.e. uncaught Exception), should I expect all of
> the statements performed using that
> > cursor up to that point will be automatically rolled back?
>
> Yes.
>
> > If yes, next question is, if I trap an exception that's
> raised during the nth statement, and
> > proceed (using the same cursor) to the (n+1)th statement,
> and the REQUEST wraps up cleanly,
> > should I expect all of the successful statements will be
> automatically committed?
>
> Yes.
>
>
> That ways easy :-)
>
> Database transactions are handled on the connection, not the cursors,
> so unless you connection dies in the middle of processing a request,
> anything you do on the connection will get committed if Zope finds
> that processing was successful, that is in particular, no unhandled
> exception was raised.
>
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Jim Abramson wrote:
> Thanks a lot for that advice, very helpful.
>
> And now for the finale:
>
> Is there a reliable way, somewhere within a series of statements during the request, to effectively commit the connection (without, of course, commit()!) i've seen 'select sysdate from dual' work at times, as a way of 'committing', but I don't know exactly how dependable this is, or what happens under the hood.

Believe me: you don't want to do that.

> and the converse is also interesting: is there any reasonable way to 'fool' the tx manager by...uh... letting an exception go up a few levels before trapping it?

Sure, but there's no trickery involved. You can create
as many errors as you like as long as Zope gets to finish
the request in a proper state (which then results in a call
to .commit() internally).

Good luck :-)

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jul 08 2004)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

> >
> > Is there a reliable way, somewhere within a series of
> statements during the request, to effectively commit the
> connection (without, of course, commit()!) i've seen 'select
> sysdate from dual' work at times, as a way of 'committing',
> but I don't know exactly how dependable this is, or what
> happens under the hood.
>
> Believe me: you don't want to do that.
>

do what? select sysdate from dual? i know i don't want to commit the connection explicitly, I've seen the consequences firsthand. What I was hoping for is a way to insure that the results of write op #1, write op #2 are committed before doing write op #3, all in the space of a request.

I guess what you're saying is that there's nothing other than the request's wrapping-up exception-free, that will commit activities on a zope-managed connection? but then, how is it that i can see changes i made to the db render on the resulting page, by selecting them back out (potentially having used a different connection in the pool to do it)?


> > and the converse is also interesting: is there any
> reasonable way to 'fool' the tx manager by...uh... letting an
> exception go up a few levels before trapping it?
>
> Sure, but there's no trickery involved. You can create
> as many errors as you like as long as Zope gets to finish
> the request in a proper state (which then results in a call
> to .commit() internally).
>

if the unhandled exception makes any connections that were involved during the request rollback, where does the rollback occur? is it prior to standard_error_message() or after?

Is all this behavior documented anywhere? (...ok, ok, silly question, stop laughing!)


Jim

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Jim Abramson wrote:
>>> Is there a reliable way, somewhere within a series of
>>
>> statements during the request, to effectively commit the connection (without, of course,
>> commit()!) i've seen 'select sysdate from dual' work at times, as a way of 'committing', but I
>> don't know exactly how dependable this is, or what happens under the hood.
>>
>> Believe me: you don't want to do that.
>>
>
>
> do what? select sysdate from dual? i know i don't want to commit the connection explicitly,
> I've seen the consequences firsthand. What I was hoping for is a way to insure that the results
> of write op #1, write op #2 are committed before doing write op #3, all in the space of a
> request.
>
> I guess what you're saying is that there's nothing other than the request's wrapping-up
> exception-free, that will commit activities on a zope-managed connection? but then, how is it
> that i can see changes i made to the db render on the resulting page, by selecting them back out
> (potentially having used a different connection in the pool to do it)?

Transactions are normally isolated, so there's no way you can see
changes you made on one pending connection using another connection.
All changes become visible once you commit them.

>>> and the converse is also interesting: is there any
>>
>> reasonable way to 'fool' the tx manager by...uh... letting an exception go up a few levels
>> before trapping it?
>>
>> Sure, but there's no trickery involved. You can create as many errors as you like as long as
>> Zope gets to finish the request in a proper state (which then results in a call to .commit()
>> internally).
>>
>
> if the unhandled exception makes any connections that were involved during the request rollback,
> where does the rollback occur? is it prior to standard_error_message() or after?
>
> Is all this behavior documented anywhere? (...ok, ok, silly question, stop laughing!)

Yes: I'd suggest you get a good book on database transactions
and how they work.

The only things to keep in mind when doing Zope programming are:

* don't do explicit commits/rollbacks in your code - Zope does
that for you

* transactions start and end at request boundaries in Zope
(just as in most other application servers)

* don't try to play tricks on the transaction manager: you're
going to lose one way or another

--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source (#1, Jul 09 2004)
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

On 2004-07-09 at 00:11:49 [+0200], Jim Abramson <jabramson@wgen.net> wrote:
> do what? select sysdate from dual? i know i don't want to commit the
> connection explicitly, I've seen the consequences firsthand. What I was
> hoping for is a way to insure that the results of write op #1, write op #2
> are committed before doing write op #3, all in the space of a request.

Well, this is what the RDBMS will do for you within a single transaction
assuming the schema is correct and Zope's transactional manager will enforce
this in the case of other events outside of the database that you might also
consider essential for this particular "business" transaction. Commit()
really is binary - yes or no.

> I guess what you're saying is that there's nothing other than the request's
> wrapping-up exception-free, that will commit activities on a zope-managed
> connection? but then, how is it that i can see changes i made to the db
> render on the resulting page, by selecting them back out (potentially
> having used a different connection in the pool to do it)?

I'm not sure what you mean here. Of course, you can see the results of your
transaction on a results page if the transaction was successful. It won't
matter which connection you are using to do this. There is a nice explanation
of transactional security and record locking on the PostgreSQL website - it
was in this week's news.

> > > and the converse is also interesting: is there any
> > reasonable way to 'fool' the tx manager by...uh... letting an
> > exception go up a few levels before trapping it?
> >
> > Sure, but there's no trickery involved. You can create
> > as many errors as you like as long as Zope gets to finish
> > the request in a proper state (which then results in a call
> > to .commit() internally).
> >
>
> if the unhandled exception makes any connections that were involved during
> the request rollback, where does the rollback occur? is it prior to
> standard_error_message() or after?

The standard_error_message should in theory be called after request failure
and transaction rollback unless you call it explicitly from within the
request but I think that way madness lies!

> Is all this behavior documented anywhere? (...ok, ok, silly question, stop
> laughing!)

Yes, in the Zope documentation. You should probably spend some time looking
at the developer docs and probably the source code of the transaction manager.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Jim Abramson wrote at 2004-7-1 17:19 -0400:
>My question is, if i grab a reference to the cursor from some db connection inside a python script, and do a bunch of statements directly on this cursor, and something goes wrong on the nth statement (i.e. uncaught Exception), should I expect all of the statements performed using that cursor up to that point will be automatically rolled back?
>
>If yes, next question is, if I trap an exception that's raised during the nth statement, and proceed (using the same cursor) to the (n+1)th statement, and the REQUEST wraps up cleanly, should I expect all of the successful statements will be automatically committed?

What you do may result in undefined behaviour...

When you use the official DA API, the DA registers itself
with Zope's transaction.
Due to this registration Zope's transaction can inform the
DA when it commits/rolls back. The DA then issues
a commit/rollback to the database.

When you simply steal (and use) the cursor, the DA may or may
not be registered with Zope's transaction (depending
on whether the request used the official API as well).
If it is not registered, the database transaction will
not follow the Zope transaction...
Chaos is the result...

You can call "register" (or similar) explicitly on the
DA -- if you feel a need to use its cursor directly.
Look at the "query" method implementation, for details.

--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Jim Abramson wrote at 2004-7-8 18:11 -0400:
> ...
>What I was hoping for is a way to insure that the results of write op #1, write op #2 are committed before doing write op #3, all in the space of a request.

I do not understand why you want this (why must "write op #3"
be done in a separte transaction?).
But, when you *really* want it, make a redirect between "op #2" and "op #3".

On my Zope page, you will find an External Method "emulatedRedirect"
which may be helpful should do want to avoid a real redirect.
Be warned however, that its use may cause "op #1" and "op #2" to
be executed twice (in case of request retrial after "ConflictError").

<http://www.dieter.handshake.de/pyprojects/zope>


--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Thanks Dieter.

> > ...
> >What I was hoping for is a way to insure that the results of
> write op #1, write op #2 are committed before doing write op
> #3, all in the space of a request.
>
> I do not understand why you want this (why must "write op #3"
> be done in a separte transaction?).

Here's a concrete example, at the risk of tiring you all. An Oracle schema I use includes a trigger that makes a call to a stored procedure in another schema. An interesting thing is, in order for that trigger/proc combo to work as deployed, the proc must be wrapped in an autonomous nested transaction. (Convoluted, yes. This unfortunate situation is beyond my control.)

So when the action of a web request writes some data to the db, and one of those inserts wakes up the trigger, but it hasn't committed yet, it's going to fail:

ORA-00060: deadlock detected while waiting for resource
(if we're lucky. it might just lock up and hang.)

This occurs because the nested transaction taking place inside the trigger's logic needs to FK back to as-yet-uncommitted data. The only way it _could_ work is if I wrap the previous statements, on which the trigger depends, in autonomous transactions as well. But that solution is no good, for a number of reasons - not least because some of these operations must themselves nest (and thus, the same deadlocking issue).

Clearly this type of scenario is not one for which Zope's scripting/tx-mgmt mechanisms are suitable. I knew that even before I started asking questions. But since migrating my entire data layer out of Zope is not an option right now, I've had to look for a workaround.


As an aside, thanks for the _register() tip. I did already come across that somewhere, and use it whenever I steal a connection to work with directly.


Jim
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

>> My question is, if i grab a reference to the cursor from some db
>> connection inside a python
>> script, and do a bunch of statements directly on this cursor, and
>> something goes wrong on the nth
>> statement (i.e. uncaught Exception), should I expect all of the
>> statements performed using that
>> cursor up to that point will be automatically rolled back?
>
> Yes.
I would say you may expect automation but ONLY if you have
used Zope machinery to get connection object and if
you've registered ResourceManager in transaction machinery.

It is something like that:
when you call Zope connection object (one visible in ZMI)
it returns instance of ResourceManager (zope.Shared.DC.ZRDB.TM)
that has real connection object as it's attribute (usually 'db').

So you may do something like:

resource_manager = context.OracleDatabaseConnection()
db = resource_manager.db
cur = db.cursor()

etc.

But to commit or rollback automatically you need to call
_register() function of resource_manager.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Upsss... sorry It's really old post...
My sort order failed or something...

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db