[Zope-DB] Transactional problem with eGenix mxODBC Zope DA 1.0.6 and SQL Server 2000

[M.-A. Lemburg]

Dieter Maurer wrote:
> M.-A. Lemburg wrote at 2003-10-29 17:54 +0100:
>  > ...
>  > 2. in some situations, mxODBC Zope DA will return two different
>  >     physical connections to a single request; since the two physical
>  >     connections each run their own transactions, you can observe the
>  >     locking mentioned above within the Zope request, thus blocking
>  >     the request until the database lock times out
> 
> In my view, this is a severe bug. It separates Zope transactions from
> database transactions.

Not really: both connections register themselves with the
Zope transaction logic.

> Beside the blocking issue covered in this thread, it also means
> that changes made in the first transaction are not seen in the
> second transaction. This can lead to surprises and even wrong
> data put into the database.

True, although it depends on the application and the transaction
isolation level you use on the connection. Unfortunately, these
things often depend on the database backend. So far, only SQL
Server seems to have a problem with this.

>  > ...
>  > Problem 2 will be fixed in version 1.0.7 where we'll add an
>  > option that forces usage of only one physical connection per
>  > request.
> 
> Are you sure, you want an option for this?
> In my view, you should use different DA instances when you need
> independent transactions in the same request.
> If the same DA instance is used several times in a single request,
> then all these uses should always be performed in the same transaction.

I've thought about that too -- it is not as severe as it
may seem in cases where you don't write data into the
database.

We'll probably make the new behaviour the default and provide
an option to revert to the old one in the quest to make
the Zope DA as hassle-free as possible.

Thanks,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Oct 29 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::