[Zope-DB] Dynamically generate sql-query in ZSQL Method

[Jim Abramson]

Well, a couple posts ago, Matt Kromer provided an explanation 
of why not to meddle directly with RDBMS commit/rollback, which
distills the issue far better than I could.  The 
get_transaction() stuff, to me, is ZODB black magic - a can
of worms I've avoided opening for a year now, and have no 
plans to get into at this point if I can help it.  

I can fairly summarize my own experience like this...the app
I'm running at first needed only to do simple, one-shot database
writes, for which ZSQL was perfectly adequate.  Over time the 
feature set and the database evolved enough that I needed to 
implement actions involving several write ops, perhaps the 
results of one being used as input for another, and so on.  

What happened next is still kind of blurry, but i vaguely 
remember something about doing 'COMMIT' and 'ROLLBACK' inside 
ZSQL methods, and noticing that the results in the db were 
coming out highly inconsistent...last thing I recall is our 
DBA team inviting me for a meeting in a dark alley outside 
the office...something about 'deadlock'...everything went 
black...and I woke up later, badly bruised.

Since then I've been putting all the transactional stuff 
inside oracle as stored procedures (wrapped in PRAGMA 
AUTONOMOUS TRANSACTION to boot) and have had no further 
problems along these lines.

Jim


> -----Original Message-----
> From: Peter Sabaini [mailto:peter at sabaini.at]
> Sent: Wednesday, April 07, 2004 3:13 PM
> To: Jim Abramson
> Cc: zope-db at zope.org
> Subject: Re: [Zope-DB] Dynamically generate sql-query in ZSQL Method
> 
> 
> I did some quick googling and found a thread about problems 
> with manual 
> transaction handling in standard_error_message -- did I overlook 
> something? Could you summarize your experience in a few sentences?
> 
> Thanks,
> peter.
> 
> 
> Jim Abramson wrote:
> >>I've not used this feature much since I try to stay in stored 
> >>procedures, but there is the get_transaction().commit() / abort() 
> >>functionality. Is this invocation problematic?
> > 
> > 
> > I was under the impression that this was a per-thread thing, not
> > per-connection
> > which is what I'd need.  Furthermore, I've read many stern
> > admonishments, on this 
> > here list and elsewhere, never to attempt to mess with 
> commit/rollback
> > on an
> > external RDBMS connection, unless you want to suffer 
> miserably.  I've
> > learned
> > (by firsthand experience) that this is indeed good advice.
> > 
> > Jim
> > 
> > 
> > 
>