203 lines
7.6 KiB
Python
203 lines
7.6 KiB
Python
#!/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())
|