[egenix-users] mxODBCZope locking

[Chui Tey]

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
   ZopeMxODBC: tpc_begin (guessing only)
   ZopeMxODBC: UPDATE TABLEB (LOCATION, LOCATIONTYPE)
               SET LOCATIONNAME = 'STREET'
               WHERE LOCATIONTYPE = 1234
   ZopeMxODBC: more INSERTS, UPDATES ...
   AnotherApp: select from TABLEB where TABLEB.LOCATIONTYPE = 1234
               (this is blocked by ZopeMxODBC)
   Zope      : tpc_finish (no error)
   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.

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