[egenix-users] mxODBC and Microsoft SQL Server 2000

M.-A. Lemburg mal at lemburg.com
Tue Sep 30 21:42:19 CEST 2003


nwingfield at che-llp.com wrote:
> We have achieved a temporary working solution by the following means:
> 1.  Enabled "Close cursor on COMMIT" setting in SQL Server setup to comply
> with SQL 92 standards
> 2.  Replaced Microsoft's SQL Server driver with an alternative from
> DataDirect
> 3.  Checked "Use Auto-Commit" on the mxODBC properties tab
> 4.  Reduced connection pool size to 1 on the mxODBC properties tab
> 5.  Disabled connection pooling in ODBC setup on the Windows server itself
> 
> The service pack had no effect on the problem.
 >
> There are two problems with this setup.
> 1.  We do not wish to use auto-commit
> 2.  We must be able to increase the connection pool size to a greater
> number

That's understandable, however, the fact that the service pack did
not solve the problems suggests that there may some other cause
preventing multiple statements from working at the same time
in your case (you are the first one to have reported such a problem).

Could you give more detail about the kind of database design your
are using (if needed, in private mail) ? E.g. usage of special
data types, two connections introducing implicit row level locks,
etc.

Please also provide information on how you access the Zope DA connections,
i.e. only via Z SQL Methods, directly, or using mix of both.

> However, if we disable "Use Auto-Commit," the following error is prone to
> occur:
> ...
>   Module Script (Python), line 4, in getPrevQuantities
>    - <PythonScript at /earthfare/customers/getPrevQuantities used for
> /earthfare/customers/ath_fs>
>    - Line 4
>   Module Shared.DC.ZRDB.DA, line 428, in __call__
>   Module Products.mxODBCZopeDA.ZopeDA, line 1330, in query
>   Module Products.mxODBCZopeDA.ZopeDA, line 1214, in run_cursor_callback
>   Module Products.mxODBCZopeDA.ZopeDA, line 1329, in <lambda>
>   Module Products.mxODBCZopeDA.ZopeDA, line 884, in errorhandler
> ProgrammingError: ('HY000', 0, '[DataDirect][ODBC SQL Server driver]You
> cannot have more than one statement active, when SQL_AUTOCOMMIT is false.',
> 5911)
 >
> Furthermore, when using a connection size greater than 1, we found that
> lock escalation in SQL Server would prevent even a single user from
> executing certain pages.  Zope would spin indefinitely, never rendering the
> page because of competing database locks.  Perhaps this could be remedied
> by revisiting our design?

 From your descriptions it seems that you have long running SQL
queries that are locking each other via implicit locks. These
could be explicit in the way your application works or could
be introduced by triggers you have on the tables.

-- 
Marc-Andre Lemburg
eGenix.com

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



More information about the egenix-users mailing list