[Zope-DB] working with zope rdbms transaction mgmt

[Jim Abramson]

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