Почему открытые транзакции отображаются в SQL Server во время простоя сеансов, когда я использую SQL Alchemy Engine и пу ⇐ Python

[Anonymous]

У меня есть веб-приложение, созданное с помощью Python и Flask, и я пытаюсь использовать SQL Alchemy для управления пулом соединений для моих подключений к SQL Server.
После реализации этого у нас произошло событие, когда наш SQL Server исчерпана память, и множество приложений и обновлений данных в нашей компании вышли из строя, поэтому теперь мы очень серьезно изучаем, не привела ли настройка пула соединений к этой перегрузке.
< Strong>Я обнаружил, что как только я выполняю транзакция с моим SQL Alchemy Engine создает постоянный сеанс на SQL Server, что, по-видимому, является желаемым поведением. Однако в сеансе также есть постоянная открытая транзакция, что не является ожидаемым поведением.
Я считаю, что следую рекомендациям по управлению транзакциями, изложенным в документации, и в журнале Pool.echo я вижу, что транзакция фиксируется в конце блока engine.begin().

Код: Выделить всё

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())

Это дает следующие строки эхо-журнала:

Код: Выделить всё

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

Насколько я понимаю, последняя строка журнала с «COMMIT» фиксирует транзакцию (которая в этом случае доступна только для чтения, но в других случаях может быть транзакцией записи). Однако, когда я проверяю активные сеансы на SQL Server, я вижу спящий сеанс с 1 открытой транзакцией.

Код: Выделить всё

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
)

Мои коллеги считают, что это потерянная транзакция, и что это основная причина замедления работы/перегрузки нашего сервера. Я не уверен – я думаю, что это может быть просто способ реализации пула соединений в SQL Alchemy, и что замедление могло быть вызвано наличием слишком большого количества эти соединения открываются во многих наших средах развертывания, где в конечном итоге возникает множество пулов в отдельных средах.
У меня три вопроса:

• Кто-нибудь есть какое-то представление о том, что здесь происходит?
• Являются ли эти явно потерянные транзакции нормальными/ожидаемыми?
• Я делаю что-то не так в своей конфигурации или использовать это, что приводит к потерянным транзакциям?

Вот мой технологический стек:

Код: Выделить всё

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

Эксперименты и результаты

• Я заметил, что если я использую движок для выполнения другого запроса, появляется новый идентификатор транзакции в sys.dm_tran_session_transactions, а счетчик open_transaction в сеансе остается равным 1. Это еще один момент, который заставляет меня полагать, что это может быть просто способ, которым SQL Alchemy обрабатывает соединение QueuePool.
  

  Код: Выделить всё

  with engine.begin() as conn:
  result = conn.execute(sa.text("select top 1 * from SchemaChangeHistory"))
  print(result.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
  

  
• Запуск engine.dispose() после использования движка очищает сеанс с открытой транзакцией . Я мог бы запускать это после каждого выполнения, но не помешает ли это созданию пула соединений?

Код: Выделить всё

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

• Использование параметра NullPool дает разные результаты: отсутствие затяжных открытых сеансов с открытыми транзакциями. Мой запасной план — просто вернуться к этому использованию, но мне бы хотелось, чтобы пул соединений был настроен и работал хорошо, чтобы мы могли пользоваться этими преимуществами.

Код: Выделить всё

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())

Код: Выделить всё

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

Предлагаемый путь вперед:
Либо

• Использовать NullPool в качестве класса пула, чтобы умывать руки от всего этого (смеется), или
• Тщательно внедрить параметрыpool_recyle иpool_use_lifo, чтобы точно настроить время жизни моих пулов соединений, гарантируя отсутствие затяжных соединений, когда они не нужны.

Подробнее здесь: https://stackoverflow.com/questions/793 ... en-i-use-a