[Zope-DB] Problems with transaction management and many database adapters

[Marco Bizzarri]

M.-A. Lemburg wrote:

> Marco Bizzarri wrote:
>
>> Hi all.
>>
>> I think we have found some problem in the management of the 
>> transactions with databases. The problem show both with  zpsycopg 
>> (1.1.10), zpopy (2.0.8) and zmysqlda (2.0.8)
>>
>> You can test the problem importing the attached zexp. It has a 
>> psycopg connection to a db (named PAFlow), and a SQL Method which 
>> queries a table called entita. The problem is not in the nature of 
>> the sql, but how the sql method is called:
>>
>> we have a page template (index_html) which calls a dtml 
>> (standard_html_header) which calls a page template (menu_html) which 
>> calls the SQL method.
>>
>> Look at the log of your postgres/mysql db: there are two 
>> transactions: the first one correctly finishes, the second one 
>> remains open and idle. If you visit again the page, there is an abort 
>> in your log, a new transaction which closes correctly, and another 
>> transaction which remains open.
>>
>> Any suggestion about this problem?
>
>
> If "abort" means that the transaction in the database is rolled back,
> then I don't see any problem: transactions are usually created
> implicitly, ie. if you commit or rollback a transaction, the database
> automatically starts a new one which, of course, remains open until
> the next request comes in.
>
> Now, some DAs may continue using the already opened transactions
> or start a new one by forcing a rollback or a reconnect. Others,
> like the mxODBC Zope DA, may not because, it's really unnecessary.
>
First of all, thanks for your reply. After a few research, we discover 
the problem caused by our example. As you may remember, there was a page 
template, using a dtml (standard_html_header), calling a PT 
(menu_headers), calling a SQLMethod.

As you can see running the example and getting a look at the logs, 
viewing the PT index_html causes two transactions to be opened: the 
first one ends with a commit, the second one remains open (no commit or 
rollback), which is strange since every zope transaction 
(request/respose) should be the boundary also of a database transaction 
(either begin/commit or begin/rollback).

The problem is in the paflow.js javascript file, which is in the 
standard_html_header. The browser, behind the scene, after getting the 
index_html, tries to get the paflow.js. However, since this is not 
present, it raises a resource not found error, and sends back to the 
browser an error page (which is not shown to the user, since this error 
is silently captured by the browser). The error produces a 
standard_error_message page, which, by default, includes the 
standard_html_header, which calls the menu_html which calls the SQL 
Method (oh gawd!). Here is either the feature or the strange behaviour: 
this is the second transaction which is not closed (no commit/rollback 
at the end). Is this a known behaviour? Or is there something we are 
missing?

Regards
Marco