[egenix-users] mxODBCZope locking

[M.-A. Lemburg]

Chui Tey wrote:
> Hi,
> 
> We are experiencing serious locking problems with using
> the mxODBCZopeDA in conjunction with SQL Server 2000
> when executing complex business logic.
> 
> The problem is not exhibited with the free but slow ZODBCDA.
 >
> The sequence is:
> 
>    Zope      : tpc_begin

Are you calling the transaction logic outside the standard
Zope request handler ?

>    ZopeMxODBC: tpc_begin (guessing only)
>    ZopeMxODBC: UPDATE TABLEB (LOCATION, LOCATIONTYPE)
>                SET LOCATIONNAME = 'STREET'
>                WHERE LOCATIONTYPE = 1234

Depending on the settings you are using for transaction
isolation, this will cause an implicit row lock.

>    ZopeMxODBC: more INSERTS, UPDATES ...
>    AnotherApp: select from TABLEB where TABLEB.LOCATIONTYPE = 1234
>                (this is blocked by ZopeMxODBC)
>    Zope      : tpc_finish (no error)

If you are calling tpc_finish without first calling
tpc_vote, the DA will not be asked to finish up by Zope
and either rollback or commit the transaction.
See the code in ./lib/python/Shared/DC/ZRDB/TM.py for
details.

>    AnotherApp: (blocked until times out)
>    ZopMxODBC : still holds locks on the table until Zope is shut down
> 
> ZopeMxODBC has failed to commit or rollback. Consequently, database locks is
> held
> indefinitely by ZopeMxODBC
> 
> We have set connection pooling to 1. However, the
> problem still occurs.

Side note: you should switch off connection pooling in
the MS ODBC manager and enable connection pooling in the
Zope DA instead to avoid problems.

> Does anybody have any suggestions?
> 
> We have switched back to using ZODBCDA. If we can't solve this we
> will have to consider writing our own database adapter.
> 
> Chui Tey
> Software Engineer
> Advanced Data Integration

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Sep 15 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 ! ::::