[Zope-DB] SQL Update statements crashing Zope

[M.-A. Lemburg]

Tim Edwards wrote:
> 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.

... so you basically have one physical connection and everything
is serialized.

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

Depends on the kinds of statements that are involved and
how they depend on each other.

It sometimes helps to use a different transaction isolation
level on the connections (at the cost of slight inconsistencies
in the output due to dirty reads/writes).

Something else to try is rewriting some of the SQL, e.g. I recently
came across a strange case where SELECT COUNT(ID) ... on a table
with an ID primary key column would lock the table,
while SELECT COUNT(*) ... would not (they both result in the same
value, of course).

> -----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 21 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 ! ::::