[egenix-users] mxODBC Zope DA and unixODBC

[M.-A. Lemburg]

Hello,

I thought this might be interesting to some of you using our
mxODBC Zope DA product with Zope or Plone:

When using mxODBC Zope DA with the unixODBC ODBC manager providing
the connection to the ODBC driver, Zope appears to hang whenever a
long running query is initiated on one of the Zope connections.

This is due to the unixODBC manager applying a very restrictive
thread isolation protection which basically serializes all requests
to the drivers on a per process scope.

For references, have a look at these links:

    http://www.unixodbc.org/odbcinst.html
    http://archives.postgresql.org/pgsql-odbc/2004-03/msg00060.php

These are the available unixODBC Thread Levels (taken from the
__handle.c file of unixODBC):

    * Level 0 - Only the DM internal structures are protected
    * the driver is assumed to take care of it's self
    *
    * Level 1 - The driver is protected down to the statement level
    * each statement will be protected, and the same for the connect
    * level for connect functions, note that descriptors are considered
    * equal to statements when it comes to thread protection.
    *
    * Level 2 - The driver is protected at the connection level. only
    * one thread can be in a particular driver at one time
    *
    * Level 3 - The driver is protected at the env level, only one thing
    * at a time.
    *
    * By default the driver open connections with a lock level of 3,
    * this can be changed by adding the line
    *
    * Threading = N
    *
    * to the driver entry in odbcinst.ini, where N is the locking level
    * (0-3)

Fortunately, the fix is easy: just add the line

    Threading = 0

to the section in odbcinst.ini for the driver you are
using, e.g.

[PostgreSQL]
Description     = PostgreSQL driver for Linux & Win32
Driver          = /usr/local/lib/libodbcpsql.so
Setup           = /usr/local/lib/libodbcpsqlS.so
Threading       = 0

Note that "Threading = 0" is normally only allowed for ODBC drivers
which are fully multi-threaded, however, since the mxODBC Zope DA
already provides the necessary thread protection at the connection
level, you can get some added performance by disabling the additional
protection in the unixODBC manager.

The iODBC manager does not seem to have this problem. So this
is a second alternative to solve the problem.

Best Regards,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Oct 06 2005)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

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