Connection busy error

Zope
1

HI,

I am using sqlalchemy version 1.4.51 and zope version 5.8.3, seeing that there is a connection busy issue, when i use sqlalchemy with zope in a multi threaded environment.

I use Products.sqlalchemy.da library, which gives me the same thread local session, when i inspect using this line session.bind.pool.status

Pool size: 20 Connections in pool: 0 Current Overflow: -19 Current Checked out connections: 1 --> on multi threaded environment the checked out connection is always remains as 1.

Installed library:
zope --> 5.8.3
sqlalchemy --> 1.4.51
Products.Sqlalchemy.da --> 2.2
z3c.sqlalchemy --> 2.1.1
zope.sqlalchemy --> 3.1
pyodbc --> 4.0.35
mssql --> ODBC 17 driver.

Traceback:
Module sqlalchemy.orm.query, line 2824, in first

Module sqlalchemy.orm.query, line 2916, in _iter

Module sqlalchemy.orm.session, line 1717, in execute

Module sqlalchemy.engine.base, line 1710, in _execute_20

Module sqlalchemy.sql.elements, line 334, in _execute_on_connection

Module sqlalchemy.engine.base, line 1577, in _execute_clauseelement

Module sqlalchemy.engine.base, line 1953, in _execute_context

Module sqlalchemy.engine.base, line 2134, in _handle_dbapi_exception

Module sqlalchemy.util.compat, line 211, in raise_

Module sqlalchemy.engine.base, line 1910, in _execute_context

Module sqlalchemy.engine.default, line 736, in do_execute

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]Connection is busy with results for another command (0) (SQLExecDirectW)')

Is there anything i need to look upon,

code:

from Products.SQLAlchemyDA.da import lookup_sa_wrapper

wrapper = lookup_sa_wrapper('name') --> name is the key it returns z3c.sqlalchemy wrapper

session = wrapper.session

Could you please help here.

2

Could this be relevant?
https://docs.sqlalchemy.org/en/14/dialects/mssql.html#enabling-snapshot-isolation

Enabling Snapshot Isolation

SQL Server has a default transaction isolation mode that locks entire tables, and causes even mildly concurrent applications to have long held locks and frequent deadlocks. Enabling snapshot isolation for the database as a whole is recommended for modern levels of concurrency support. This is accomplished via the following ALTER DATABASE commands executed at the SQL prompt:

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase SET READ_COMMITTED_SNAPSHOT ON

Background on SQL Server snapshot isolation is available at Enabling Row Versioning-Based Isolation Levels | Microsoft Learn.

3

Thanks for the help,

scoped_session are thread local session which uses threading.local, the instance of scoped_session is session object session = Session().

In Products.sqlalchemy.da it give the thread local session(session instance), which i attach it every web request and connection pool takes care managing the session

Da gives a thread local session, but the connection pool is current checkout connection is 1 and if i close the session(session.close) which zope.sqlalchemy takes care, why the thread local session which is having connection pool size of 5 is not giving the connection to multi threaded request it is kind of releasing one.

4

Sorry, have no specific experience with MSSQL, but have seen elsewhere that a scoped session must be set up using a lock in addition to threading.local. Not sure if you are already doing this.

See the code (and eleddy's priceless comment) in this post:

5

Thanks for the help

6

@Buvanesh If you found a solution, can you explain what you did so that others can be helped in the future?

7

hi @mtrebron,

The issue was happening z3c.sqlalchemy package.

on the latest changes on z3c.sqlalchemy packages which is zope wrapper has caused connection busy issue, raised a concern there and it has resolved the session on calling upon every web request call, same thread local session was been attached and the connection pool which is responsible for managing the connection for multi threaded environment, one session was attached for subsequent request, the due the session was fetching the result set, other request has to wait until it completes, now on every web call we are call the scoped session maker and connection pool will take care of managing the session multi threaded environment.

2 Likes
8

That was my bad :sweat_smile:

See:

2 Likes