[egenix-users] mxODBC and Microsoft SQL Server 2000 : An other case!

[M.-A. Lemburg]

Sébastien Walgraffe wrote:
> Hello,
> after seeing the same case described in the mailing list archives from
> september, I decided to post my problem too, because it seems really similar
> 
> Reminder :
> nwingfield at che-llp.com wrote :
> "We are having trouble with mxODBC and Microsoft SQL Server 2000, when two
> or more people run the same query at the same time.  This trouble is easily
> reproduced by opening two browser windows and loading a page that executes
> a lengthy SQL query in both windows simultaneously.  Exhibit A shown below
> is the most common, but Exhibit B is not hard to reproduce under the same
> circumstances.  Exhibits C and D are also fairly common, and though not
> directly SQL-related, suggest incomplete or corrupted recordsets.
> Sometimes one window will successfully load the page; sometimes neither
> will.  Never do both windows successfully load the page simultaneously.  I
> would appreciate any suggestions.[...]"
> 
> Here is our problem :
 >
> ...
 >
> What we did :
> 
> With all those problems, we had no other choice than supress ZSQL
> methods....
> 
> So we created a internal product for zope doing the following things :
> - open a new connection
> - writing sql with given parameters
> - getting results
> - closing connection
> With this, we can avoid all problems we got.
> 
> But with this solution, we don t have transaction and rollback
> possibilities. It s the only thing we found.
> Obviously, this his solution can be temporary, but not definitive.
> 
> Other solution?
> 
> Is there something else we could do? Like change something in the mxODBC
> driver to avoid all those problems?

I can only guess, but since your problems sound very similar, I
assume that the cause is the same...

These problems generally occur if you are using two or
more Z SQL Methods in a way that let's the SQL Server block,
e.g. you have an UPDATE statement followed by SELECT statement
in the same Zope transaction, but only if the Z SQL Methods use
two or more mxODBC Zope DA connections or a single mxODBC Zope
DA connection configured to use a connection pool of size 2 or
more.

The reason is that mxODBC Zope DA tries to make use of the
available resources as best as possible...

E.g. lets say you have configured
two Z SQL Methods using a single mxODBC Zope DA connection with
pool size 2. In the above example, the DA would give connection 1
to the first Z SQL Method and connection 2 to the second.

Since the two connections are each using their own physical
connection and thus their own database transaction context,
SQL Server issues a row lock for the UPDATEd rows and let's
the SELECT in the second method wait for that lock.

Unfortunately, both Z SQL Methods are used within a single
Zope transaction, so you get all kinds of weird errors from
SQL Server.

Other DAs like e.g. the ZODBC DA don't use connection pooling,
so you'll never see the problem there... but then they don't
even give you a chance to make use of parallelism which can
often be put to good use, esp. in read-only cases.

The solution to the problem is to either set the pool size in
the used mxODBC Zope DA connection to 1 (all Z SQL Methods using
this connection will then work with the same physical connection)
or to code your application in a Z SQL Method independent way
that makes sure that both statements are executed on the
same physical connection.

Hope that helps,
-- 
Marc-Andre Lemburg
eGenix.com

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