Programmiererforum

by **Guest** » 15 Jan 2025, 17:32

Ich habe eine Webanwendung, die mit Python und Flask erstellt wurde, und ich versuche, SQL Alchemy zu verwenden, um das Verbindungspooling für meine Verbindungen zu SQL Server zu verwalten.
Seit der Implementierung hatten wir ein Ereignis, bei dem unser SQL Server Der Arbeitsspeicher war voll und eine Reihe von Anwendungen und Datenaktualisierungen in unserem Unternehmen stürzten ab. Daher prüfen wir jetzt ernsthaft, ob die Einrichtung des Verbindungspools zu dieser Überlastung geführt hat.
< stark>Ich habe festgestellt, dass, sobald ich a ausführe Bei einer Transaktion mit meiner SQL Alchemy Engine wird eine dauerhafte Sitzung auf dem SQL Server erstellt – was das gewünschte Verhalten zu sein scheint. Allerdings weist die Sitzung auch eine dauerhaft offene Transaktion auf – was nicht dem erwarteten Verhalten entspricht.
Ich glaube, ich befolge die Best Practices für die Transaktionsverwaltung in der Dokumentation. und ich kann in der pool.echo-Protokollierung sehen, dass die Transaktion am Ende des engine.begin()-Blocks festgeschrieben wird.

Code: Select all

import sqlalchemy as sa

conn_string = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=,
;DATABASE=;UID=;PWD=;Encrypt=yes;TrustServerCertificate=yes;Connection Timeout=10"

engine = sa.create_engine(f"mssql+pyodbc:///?odbc_connect={conn_string}"
, echo_pool=True
, echo=True)

with engine.begin() as conn:
result = conn.execute(sa.text("select top 1 * from SomeTable"))
print(result.all())

Dies ergibt die folgenden Echoprotokollierungszeilen:

Code: Select all

2025-01-15 09:47:49,391 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2025-01-15 09:47:49,393 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-01-15 09:47:49,428 INFO sqlalchemy.engine.Engine SELECT schema_name()
2025-01-15 09:47:49,429 INFO sqlalchemy.engine.Engine [generated in 0.00088s] ()
2025-01-15 09:47:49,531 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2025-01-15 09:47:49,532 INFO sqlalchemy.engine.Engine [generated in 0.00140s] ()
2025-01-15 09:47:49,563 INFO sqlalchemy.engine.Engine SELECT 1 FROM fn_listextendedproperty(default, default, default, default, default, default, default)
2025-01-15 09:47:49,565 INFO sqlalchemy.engine.Engine [generated in 0.00120s] ()
2025-01-15 09:47:49,624 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-01-15 09:47:49,626 INFO sqlalchemy.engine.Engine select top 1 * from SomeTable
2025-01-15 09:47:49,627 INFO sqlalchemy.engine.Engine [generated in 0.00135s] ()
[(1,)]
2025-01-15 09:47:49,660 INFO sqlalchemy.engine.Engine COMMIT

Mein Verständnis ist, dass die letzte Protokollierungszeile mit „COMMIT“ die Transaktion festschreibt (die in diesem Fall schreibgeschützt ist, in anderen Fällen jedoch auch eine Schreibtransaktion sein könnte). Wenn ich jedoch die aktiven Sitzungen auf SQL Server überprüfe, sehe ich die schlafende Sitzung mit einer offenen Transaktion.

Code: Select all

SELECT s.session_id, s.status, s.transaction_isolation_level, s.open_transaction_count,
st.transaction_id, s.row_count, s.login_time, s.last_request_start_time,
s.last_request_end_time, s.client_version, s.client_interface_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_tran_session_transactions st ON st.session_id = s.session_id
WHERE login_name='myUID' AND host_name='myHost'
AND EXISTS (
SELECT *
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)
AND NOT EXISTS (
SELECT *
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id
)

Meine Kollegen glauben, dass es sich hierbei um eine verwaiste Transaktion handelt und dass dies die Hauptursache für die Verlangsamung/Überlastung unseres Servers ist. Ich bin mir nicht sicher – ich denke, dass dies möglicherweise einfach die Art und Weise ist, wie SQL Alchemy den Verbindungspool implementiert, und dass die Verlangsamung möglicherweise durch zu viele davon verursacht wurde Diese Verbindungen öffnen sich über unsere vielen Bereitstellungsumgebungen hinweg, wo es am Ende viele Pools über separate Umgebungen hinweg gibt.
Meine Fragen sind dreifach:

Tut irgendjemand Haben Sie einen Einblick, was hier vor sich geht?
Sind diese scheinbar verwaisten Transaktionen normal/erwartbar?
Mache ich in meiner Konfiguration etwas falsch? oder eine Verwendung, die zu verwaisten Transaktionen führt?

Hier ist mein Tech-Stack:

Code: Select all

Flask==2.2.5
pyodbc==5.2.0
sqlalchemy==2.0.37
python==3.11.7

Experimente und Ergebnisse

Mir ist aufgefallen, dass eine neue Transaktions-ID angezeigt wird, wenn ich die Engine zum Ausführen einer weiteren Abfrage verwende in sys.dm_tran_session_transactions, und der open_transaction-Zähler für die Sitzung bleibt bei 1. Dies ist ein weiterer Punkt, der mich glauben lässt, dass dies möglicherweise nur die Art und Weise ist, wie SQL Alchemy die QueuePool-Verbindung handhabt.

Code: Select all

with engine.begin() as conn:
result = conn.execute(sa.text("select top 1 * from SchemaChangeHistory"))
print(result.all())

Code: Select all

2025-01-15 10:20:13,821 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-01-15 10:20:13,822 INFO sqlalchemy.engine.Engine select top 1 * from SchemaChangeHistory
2025-01-15 10:20:13,823 INFO sqlalchemy.engine.Engine [cached since 1944s ago] ()
[(1,)]
2025-01-15 10:20:13,905 INFO sqlalchemy.engine.Engine COMMIT

Das Ausführen von engine.dispose() nach der Verwendung der Engine löscht die Sitzung mit der offenen Transaktion . Ich könnte dies nach jeder Ausführung ausführen, aber würde das nicht den Zweck des Verbindungspoolings zunichtemachen?

Code: Select all

2025-01-15 10:24:49,344 INFO sqlalchemy.pool.impl.QueuePool Pool disposed. Pool size: 5  Connections in pool: 0 Current Overflow: -5 Current Checked out connections: 0
2025-01-15 10:24:49,345 INFO sqlalchemy.pool.impl.QueuePool Pool recreating

Die Verwendung der NullPool-Option führt zu unterschiedlichen Ergebnissen: keine verbleibenden offenen Sitzungen mit offenen Transaktionen. Mein Ersatzplan besteht lediglich darin, dies wieder zu verwenden, aber ich würde gerne dafür sorgen, dass das Verbindungs-Pooling eingerichtet wird und gut funktioniert, damit wir diese Vorteile nutzen können.

Code: Select all

import sqlalchemy as sa

engine = sa.create_engine(f"mssql+pyodbc:///?odbc_connect={os.environ.get('SQLCONNSTR_pe_DW')}"
, echo_pool=True
, echo=True
, poolclass=sa.pool.NullPool)

with engine.begin() as conn:
result = conn.execute(sa.text("select top 1 * from SchemaChangeHistory"))
print(result.all())

Code: Select all

2025-01-15 10:32:35,261 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2025-01-15 10:32:35,262 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-01-15 10:32:35,296 INFO sqlalchemy.engine.Engine SELECT schema_name()
2025-01-15 10:32:35,298 INFO sqlalchemy.engine.Engine [generated in 0.00267s] ()
2025-01-15 10:32:35,398 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2025-01-15 10:32:35,400 INFO sqlalchemy.engine.Engine [generated in 0.00206s] ()
2025-01-15 10:32:35,432 INFO sqlalchemy.engine.Engine SELECT 1 FROM fn_listextendedproperty(default, default, default, default, default, default, default)
2025-01-15 10:32:35,434 INFO sqlalchemy.engine.Engine [generated in 0.00239s] ()
2025-01-15 10:32:35,500 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-01-15 10:32:35,502 INFO sqlalchemy.engine.Engine select top 1 * from SomeTable
2025-01-15 10:32:35,503 INFO sqlalchemy.engine.Engine [generated in 0.00152s] ()
[(1,)]
2025-01-15 10:32:35,748 INFO sqlalchemy.engine.Engine COMMIT

Vorgeschlagener Weg nach vorn:
Entweder

Verwenden Sie NullPool als Poolklasse, um mir die Hände von all dem zu waschen (lol), oder
Umsetzen Sie das sorgfältig pool_recyle- und pool_use_lifo-Parameter zur Feinabstimmung der Lebensdauer meiner Verbindungspools, um sicherzustellen, dass es keine verbleibenden Verbindungen gibt, wenn sie nicht benötigt werden.

Programmiererforum

Warum werden offene Transaktionen in SQL Server in Leerlaufsitzungen angezeigt, wenn ich eine SQL Alchemy Engine und Ver

Post a reply

Expand view Topic review: Warum werden offene Transaktionen in SQL Server in Leerlaufsitzungen angezeigt, wenn ich eine SQL Alchemy Engine und Ver