[Zope-DB] Question: detecting aborted transactions

[Matthew T. Kromer]

jelle wrote:

>Hello Chris,
>
>My workflow-type app does 3-20 sql statments per web page. Normally, under 
>low load situations, a transaction begins with the first sql statement and 
>commits upon completion of the thread. Under high load, Postgresql 
>occasionally aborts a statement with the message "could not serialize 
>access due to concurrent update" which causes the Zope mechanism to 
>silently rollback the transaction. The very next sql statement within the 
>same Zope thread starts a new transaction resulting in an funky data.
>
>Perhaps I can solve the problem by (1) committing after each 
>statement, and (2) detecting rollbacks and resending the offending sql 
>statement after a short delay.
>
>The questions: is that possible within ZSQL and if so how?
>
>
>  
>

You should not attempt to commit data directly!  Instead, you want to 
let the database connection object commit for you.  If you commit 
directly you are interfering with Zope's built in transactional 
boundaries.  The transaction manager in Zope is one of the most clever 
things it has -- let it work on your behalf!

Now, maybe you ARE doing this, and I'm misinterpreting what you're saying.

When you say "commits upon completion of the thread" is where I get 
confused.  Zope threads really don't tend to just spring into being and 
then complete, instead the medusa component (the web server part) queues 
up requests for worker threads which rendezvous to pick up work.  Each 
worker thread hangs around afterwards rather than dissapearing -- it 
just goes back in line to pick up more work.

When the worker thread hands the results back over to medusa at the end 
of the request, it commits the Zope transaction.  This is a two phase 
commit (tpc_vote comes first) and your database connection object SHOULD 
have registererd itself with the transaction manager to do a postgres 
commit during the vote phase.   Depending on the semantics of your 
adapter (none of them do two phase to my knowledge) it can do a one or 
two phase commit itself.  The only time this becomes important is those 
rare circumstances like you describe when the commit itself aborts. 

If the Zope transaction aborts during the vote phase (which is allowed) 
the transaction is retried or aborted (well, it depends on what error it 
raises -- database adapters probably don't raise ConflictErrors which 
would be what causes the request to be retried).  If the commit error 
occurs AFTER the vote phase bad things happen, tending to cause Zope 
some grief.

What you *probably* want to look at doing is tweaking the posgresql 
adapter to see what error types it is throwing, and if you're getting a 
serialization error turn that into a ConflictError so that Zope retries 
the request for you.