unis-oa/backend/scripts/check_pool_reset.py

203 lines
7.6 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

#!/usr/bin/env python3
"""守住连接池那一刀关掉之后的前提:归还的连接一律不带没结束的事务。
``[database] pool_reset`` 默认 ``none`` = 连接还池时不再补一次 ``ROLLBACK``(``app/db.py``)。
少付的是往返:一次单据详情要开 7 条 Session(主线程 1 + 并发块 6),出厂口径每条都要
"Session 自己回滚一次 + 池子再补一次",实测 26 次真往返里 14 次是 ROLLBACK。
省下的这一刀只有在一种前提下才白拿:**借出连接的人出去时一定 commit 或 rollback 过**。
本仓库借连接只有两条路(ORM Session 的 ``close()``、``with engine.connect()/begin()``),
两条都自带收尾;这个脚本就是把这个前提钉住——跑完一批真实接口与读写分支,
只要有一次连接是"发过语句还没收尾"就回到池子,立刻判失败。
只读接口照 ``perf_guard`` 的清单打(不改数据),写与回滚两条分支用一张不存在的设置行,
``UPDATE`` 影响 0 行。::
cd backend && PYTHONPATH=. python3 scripts/check_pool_reset.py # 常用接口
cd backend && PYTHONPATH=. python3 scripts/check_pool_reset.py --all # 99 个只读接口全扫
"""
from __future__ import annotations
import os
import sys
import threading
import pymysql.connections as pc
from sqlalchemy import event, select, text
from app.db import SessionLocal, engine
from app.security import create_access_token
#: 会让 MySQL 开事务(或可能带着事务回去)的语句;``SET`` / DDL 自己就落盘,不算
_DIRTY_HEAD = ("SELECT", "INSERT", "UPDATE", "DELETE", "REPLACE", "SHOW", "CALL", "MERGE")
#: 默认扫这些(覆盖读、并发块、写分支);``--all`` 才走 perf_guard 的全清单
QUICK_PATHS = [
"/api/v1/auth/me",
"/api/v1/org/tree",
"/api/v1/org/persons?page=1&pageSize=20",
"/api/v1/work/created?page=1&pageSize=20",
"/api/v1/task/processing?page=1&pageSize=20",
"/api/v1/draft/types",
"/api/v1/admin/work/list?page=1&pageSize=20",
"/api/v1/admin/task/processing?page=1&pageSize=20",
"/api/v1/process/processes",
"/api/v1/process/dicts",
]
FAILS: list[str] = []
LOCK = threading.Lock()
#: dbapi 连接 id -> 自上次 commit/rollback 之后发过语句
DIRTY: dict[int, bool] = {}
BORROWED: list[bool] = [] # 每条连接归还时是否脏
#: 反向自检期间允许脏一次(探针必须抓到),抓到才算探针管用
EXPECT_DIRTY = False
NEGATIVE_CAUGHT = False
AVOIDED = [0] # 因为关掉 pool_reset 而省下的 ROLLBACK 次数
_ORIG = {name: getattr(pc.Connection, name) for name in ("query", "commit", "rollback")}
def _install_probe() -> None:
def query(self, sql, *args, **kwargs):
head = str(sql).lstrip()[:8].upper()
if head.startswith(_DIRTY_HEAD):
with LOCK:
DIRTY[id(self)] = True
return _ORIG["query"](self, sql, *args, **kwargs)
def commit(self, *args, **kwargs):
with LOCK:
DIRTY[id(self)] = False
return _ORIG["commit"](self, *args, **kwargs)
def rollback(self, *args, **kwargs):
with LOCK:
DIRTY[id(self)] = False
AVOIDED[0] += 1 # 这一刀是 Session/Connection 自己发的;池子那一刀已经没有了
return _ORIG["rollback"](self, *args, **kwargs)
pc.Connection.query = query
pc.Connection.commit = commit
pc.Connection.rollback = rollback
def _uninstall_probe() -> None:
for name, fn in _ORIG.items():
setattr(pc.Connection, name, fn)
@event.listens_for(engine, "checkin")
def _watch_checkin(dbapi_connection, connection_record, *args) -> None:
with LOCK:
dirty = DIRTY.pop(id(dbapi_connection), False)
BORROWED.append(bool(dirty))
if not dirty:
return
global NEGATIVE_CAUGHT
if EXPECT_DIRTY:
NEGATIVE_CAUGHT = True
return
FAILS.append("有一条连接带着没结束的事务回到池子(该 commit/rollback 的人没收尾)")
@event.listens_for(engine, "checkout")
def _watch_checkout(dbapi_connection, connection_record, *args) -> None:
with LOCK:
DIRTY.setdefault(id(dbapi_connection), False)
def drive(paths: list[str]) -> int:
from starlette.testclient import TestClient
from app import models
client = TestClient(app := __import__("app.main", fromlist=["app"]).app)
token, _ = create_access_token("1")
headers = {"Authorization": "Bearer " + token}
db = SessionLocal()
job = db.execute(select(models.Work.job).limit(1)).scalar_one_or_none()
work_id = db.execute(select(models.Work.id).limit(1)).scalar_one_or_none()
db.close()
paths = list(paths)
if job:
paths += [f"/api/v1/job/{job}", f"/api/v1/work/{work_id}"]
hits = 0
for path in paths:
response = client.get(path, headers=headers)
hits += 1
if response.status_code >= 500:
FAILS.append(f"{path} 返回 {response.status_code}")
# 写分支(提交)与回滚分支:一条不存在的设置行,影响 0 行,不碰任何数据
for commit_it in (True, False):
session = SessionLocal()
try:
session.execute(text("UPDATE oa_system_setting SET value = value WHERE `key` = '__no_such_key__'"))
session.commit() if commit_it else session.rollback()
finally:
session.close()
session = SessionLocal()
try:
session.execute(text("SELECT 1"))
session.rollback()
finally:
session.close()
return hits
def negative_probe() -> None:
"""反向自检:故意借一条裸连接、不收尾就还回去,探针必须叫。
这个脚本的全部价值都在"脏归还抓得住",所以它得上一次场。
"""
global EXPECT_DIRTY
EXPECT_DIRTY = True
try:
raw = engine.raw_connection()
try:
with raw.cursor() as cursor:
cursor.execute("SELECT 1")
cursor.fetchall()
finally:
raw.close() # 裸连接直接 close:不经过 Connection.close(),事务没人收尾
finally:
EXPECT_DIRTY = False
def main() -> int:
from app.config import settings
mode = str(getattr(settings, "db_pool_reset", "none") or "").strip().lower()
print(f"[database] pool_reset = {mode}(none = 归还时不补 ROLLBACK)")
paths = QUICK_PATHS
if "--all" in sys.argv:
sys.path.insert(0, os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "scripts"))
import perf_guard
db = SessionLocal()
paths = [url for _name, url in perf_guard.route_targets(db)]
db.close()
_install_probe()
try:
hits = drive(paths)
negative_probe()
finally:
_uninstall_probe()
dirty = sum(1 for flag in BORROWED if flag)
print(f"扫了 {hits} 个接口 + 提交/回滚两条分支:连接归还 {len(BORROWED)} 次,"
f"其中带着未结束事务的 {dirty} 次")
if not BORROWED:
FAILS.append("一次连接归还都没观察到,这个脚本等于没跑")
if not NEGATIVE_CAUGHT:
FAILS.append("反向自检没抓到故意造成的脏归还——这个探针不管用,别拿它当保证")
if FAILS:
print(f"\n不通过 {len(FAILS)} 条:")
for line in FAILS:
print(" -", line)
return 1
print(f"通过:{len(BORROWED) - 1} 次正常归还全部干净,反向自检那次脏归还被抓到;"
f"池子那一刀本来就用不上(这一轮自己收尾的 ROLLBACK {AVOIDED[0]} 次,一次都没多付)。")
return 0
if __name__ == "__main__":
raise SystemExit(main())