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

M.-A. Lemburg mal at lemburg.com
Wed Oct 22 14:55:28 CEST 2003


Sébastien Walgraffe wrote:
> Thanks for your response (so fast ^^)
> 
> But this seems not to be a solution for us because :
> 
> - We already used a pool size of 1 (in fact we installed new version of
> MxODBC hoping that pool size would change those problems, but it didn t
> change...)
> - We don t have connection errors only on update followed by select... We
> also have the same problems on selects (loading a lot of rows from different
> tables for example)
> 
> We have a single physical connection, but when we think at it, we don t
> understand how will parralelism could with different users, because if it
> works like a queue, other users ll have timeouts or errors
> Example :
> User A ask a huge amount of data from DB, requiring many queries
> User B ask a simple information
> ->
> Connection open for User A
> ->
> Transaction for A blocking the physical connection for B
> ->
> Timout or error for B
> 
> Any clue?

The problem you describe is a little different than the
one I tried to explain in my previous mail:

The Zope DA manages the connection using two layers:

1. a logical one per Zope Connection object

2. a physical one which pools the physical database connections


If you set pool size to 1, only one physical connection is
opened for the database.

Now in your situation (and probably others as well, which is
why I'm writing this here), Zope thread 1 will ask for
a physical connection and receive it from the pool
manager. Zope will then start a transaction on that
connection, locking it against use by any other Zope thread.
Once the transaction is started, Z SQL Methods in that
Zope thread can happily use the connection for e.g.
long running SELECTs.

Since Zope is typcially running using more than one thread,
a second request to the Zope server will usually result
in Zope thread 2 to receive this request. Zope thread
2 will then ask the DA for a connection and it will return
the same physical connection object as it did for Zope thread
1 (it will of course try to find a non-used one, but falls
back to passing back a currently busy one).

This is where you run into the blocking problem: Zope
will try to start a transaction on this physical connection
in Zope thread 2 as well. However, the transaction is
already in use by Zope thread 1 and the physical connection
managed by the Zope DA will wait for Zope thread 1
to release the lock before it lets Zope thread 2
use it for its own purposes.

The solution to this problem is easy: set pool size to
at least the number of threads you expect Zope to be
running.

However, keep in mind that when setting the pool size
to more than 1, you can run into the locking problem
I mentioned in my previous mail. This is only solvable by
using a program design that carefully pays attention to row
level locking and transaction isolation in the database
server and can vary between database backends.

-- 
Marc-Andre Lemburg
eGenix.com

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