[Zope-DB] Dynamically generate sql-query in ZSQL Method
jabramson at wgen.net
Wed Apr 7 22:21:45 EDT 2004
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.
> -----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?
> 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
> > on an
> > external RDBMS connection, unless you want to suffer
> miserably. I've
> > learned
> > (by firsthand experience) that this is indeed good advice.
> > Jim
More information about the Zope-DB