[Zope-DB] Transactional problem with eGenix mxODBC Zope DA 1.0.6 and SQL Server 2000

[M.-A. Lemburg]

Nicolas Lehuen wrote:
> Hi,
> 
> We're running Zope 2.6.2 (with the embedded Python 2.1.3) on Windows 2000 and connect to our SQL Server 2000 server through ODBC. All server and driver versions are the latest available. We've bought the Zope DA version 1.0.6 after a 3 weeks evaluation.
> 
> The trouble is that the Zope process hangs when we try to call to an INSERT, UPDATE or DELETE ZSQL Method from a Zope Python Script and then call, say, a template page. This trouble disappears if we set autocommit on the Zope DA object.

The problem is twofold and so far we have only observed it with
SQL Server setups:

1. insert, update and delete put exclusive locks on the tables in question,
    blocking all other connections until they have completed

2. in some situations, mxODBC Zope DA will return two different
    physical connections to a single request; since the two physical
    connections each run their own transactions, you can observe the
    locking mentioned above within the Zope request, thus blocking
    the request until the database lock times out

Problem 1 is something you can only fix by carefully designing the
database application. You may be able to configure SQL Server to
not lock in such situations, but there's nothing much you can do
from the Zope side of things, other than switch off transactions
altogether.

Problem 2 will be fixed in version 1.0.7 where we'll add an
option that forces usage of only one physical connection per
request. As temporary work-around, you can set the pool size
to 1.

> In fact we ran into this problem while replacing the old Zope ODBC DA by the mxODBC Zope DA. All was working perfectly before (maybe because the old DA was automatically using a DA), but we decided to replace it for three reasons :
> 
> - consistency : we need true transactions ; we sometimes have to call two ZSQL Method and make sure both are executed in the same transaction, or none in case of failure. With autocommit, this would not be possible.
> - performance : mxODBC Zope DA provide connection pooling and true multi-threading.
> - capabilities : the 'fetch last available resultset option' allows us to insert rows and fetch the last autogenerated key in one single request, i.e. by 'INSERT INTO ... (...) VALUES (...) SELECT @@IDENTITY'.
> 
> Let me give an example : let's say we have those objects in a directory :
> 
> - db (a mxODBC Zope DA connection object with a max count of physical connections of 10 and 'fetch last available resultset' on)
> - zsql1 (an insert ZSQL method)
> - script1 (a python script)
> - index_html (a TAL template)
> 
> script1 typically contains, amongst other things :
> 
> context.zsql1(param1='value1',param2='value2')
> return context.index_html()
> 
> 1) If we set autocommit to off (the default setting), and run the script, the Zope process hangs while holding physical connections to the database. We have to restart the Zope process.
> 2) If we set autocommit to on, all is running perfectly, except that we don't want to set autocommit to on, because of consistencies issues.
> 3) If we replace the last line by :
> 	return 'foobar'
>    the two requests are processed and 'foobar' is displayed, i.e. all is running perfectly, except that the script doesn't do what we want it to do.
> 
> Does anybody see where the problem could be ?
> 
> Best regards,
> 
> Nicolas Lehuen
> CTO
> The CRM Company
> http://www.thecrmcompany.com/
> mailto:nicolas.lehuen at thecrmcompany.com
> Tél. +33 (0)1 55 77 28 16
> Fax. +33 (0)1 55 77 28 30
> 
> 
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db

-- 
Marc-Andre Lemburg
eGenix.com

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