[Zope-DB] SQL Update statements crashing Zope

[Tim Edwards]

The problem was that the update statement was locking the table and causing
deadlock with subsequent select statements. We found that reducing the
connection pool size from 5 to 1 solved the problem.

Is there a way to have multiple physical connections open but avoid
concurrency problems like this?

-----Original Message-----
From: M.-A. Lemburg [mailto:mal at egenix.com]
Sent: Wednesday, 21 January 2004 3:51 AM
To: Tim Edwards
Cc: zope-db at zope.org
Subject: Re: [Zope-DB] SQL Update statements crashing Zope


Tim Edwards wrote:
> Hi,
> 
> We are having a serious problem with one of our Zope/Plone applications
> where an SQL Update statement seems to freeze the whole Zope instance. We
> have to stop the service and restart it. We are running Zope 2.6.2 on
> Windows 2000 connecting to an MS SQL 2000 server. We have tried both
mxODBC
> and ZODBC and both have the same issue.
> 
> The problem only occurs when the application makes an update statement -
> inserts, deletes and selects are fine. The problem seems to be that the
> update locks the table in the database (trying to open it using SQL Server
> Enterprise Manager gets a blank resultset) and never releases it. This
> problem occurs on all update statements in the application. Typing the
> update SQL manually into a ZSQL method seems to work fine.

Sounds like a bug in MS SQL Server or the MS ODBC driver. Locks
should always get released when a transaction finishes.

Another possibility is a dead lock situation caused by e.g.
a trigger or the update doing a sub-select. In that case,
the database should timeout after a while and present the
error to the application.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Jan 20 2004)
 >>> 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 ! ::::