built-in pooling does not reset session state for isolation level #337
Description
Describe the bug
setting the transaction isolation level on a connection carries over into new connections due to implicit connection pooling which seems to lack a means of establishing reset behaviors
To reproduce
import mssql_python
MSSQLPYTHON = (
"Server=mssql2022,1433;Database=test;UID=scott;PWD=tiger^5HHH;Encrypt=No"
)
def get_isolation_level(dbapi_connection):
cursor = dbapi_connection.cursor()
view_name = "sys.system_views"
cursor.execute(
(
"SELECT name FROM {} WHERE name IN "
"('dm_exec_sessions', 'dm_pdw_nodes_exec_sessions')"
).format(view_name)
)
row = cursor.fetchone()
view_name = f"sys.{row[0]}"
cursor.execute(
"""
SELECT CASE transaction_isolation_level
WHEN 0 THEN NULL
WHEN 1 THEN 'READ UNCOMMITTED'
WHEN 2 THEN 'READ COMMITTED'
WHEN 3 THEN 'REPEATABLE READ'
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT' END
AS TRANSACTION_ISOLATION_LEVEL
FROM {}
where session_id = @@SPID
""".format(
view_name
)
)
row = cursor.fetchone()
cursor.close()
return row[0].upper()
connection = mssql_python.connect(MSSQLPYTHON)
print(
f"New connection {connection}: "
f"isolation level: {get_isolation_level(connection)}"
)
cursor = connection.cursor()
cursor.execute(f"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
cursor.close()
print(
f"Changed isolation level for {connection}: "
f"now it's: {get_isolation_level(connection)}"
)
connection.close()
connection = mssql_python.connect(MSSQLPYTHON)
print(
f"Made another new connection {connection}: "
f"isolation level: {get_isolation_level(connection)}"
)
connection.close()
mssql_python.pooling(enabled=False)
connection = mssql_python.connect(MSSQLPYTHON)
print(
f"Disabled pooling and connected again: {connection}: "
f"isolation level: {get_isolation_level(connection)}"
)this outputs:
New connection <mssql_python.connection.Connection object at 0x7f2e7eb397f0>: isolation level: READ COMMITTED
Changed isolation level for <mssql_python.connection.Connection object at 0x7f2e7eb397f0>: now it's: SERIALIZABLE
Made another new connection <mssql_python.connection.Connection object at 0x7f2e7eb9d950>: isolation level: SERIALIZABLE
Disabled pooling and connected again: <mssql_python.connection.Connection object at 0x7f2e7eb9da90>: isolation level: READ COMMITTED
it is supposed to output:
New connection <mssql_python.connection.Connection object at 0x7f2e7eb397f0>: isolation level: READ COMMITTED
Changed isolation level for <mssql_python.connection.Connection object at 0x7f2e7eb397f0>: now it's: SERIALIZABLE
Made another new connection <mssql_python.connection.Connection object at 0x7f2e7eb9d950>: isolation level: READ COMMITTED
Disabled pooling and connected again: <mssql_python.connection.Connection object at 0x7f2e7eb9da90>: isolation level: READ COMMITTED
for connection pooling to work, everything on that session has to be reset between connections, or at least there needs to be a way to customize this. as an example look at PG bouncer server_reset_query.