如何通过SQLAlchemy在Python中操作SQL Server事务且不阻塞SSMS的表访问

问题根因 #

你遇到的阻塞问题核心来自df.to_sql的if_exists='replace'参数的默认执行逻辑：该模式会先执行DROP TABLE再重建目标表，过程中会持有高粒度的排他锁，加上大数据量插入时整个事务周期长，锁长期不释放，最终导致其他数据库访问被阻塞。你之前测试的调整连接池参数、设置更严格的SERIALIZABLE隔离级别等方案，都没有解决核心的长事务+高粒度锁的问题，因此没有效果。

可行解决方案 #

方案1：改用DELETE/TRUNCATE + 追加写入逻辑（完全符合你预期的SQL逻辑） #

不要使用if_exists='replace'，手动在事务内实现清空表+追加写入的逻辑，避免删表重建的高成本锁操作，代码示例如下：

from sqlalchemy import create_engine, text

# 用SQL Server默认的READ COMMITTED隔离级别即可，不要用更高的SERIALIZABLE
engine = create_engine(
    "mssql+pyodbc://server_name:password@user/database?driver=SQL+Server+Native+Client+11.0",
    isolation_level="READ COMMITTED"
)

table_name = "table_name"
schema_name = "db_schema"
with engine.begin() as conn:
    # 无外键约束优先用TRUNCATE，执行速度极快，持锁时间极短
    conn.execute(text(f"TRUNCATE TABLE [{schema_name}].[{table_name}]"))
    # 有外键约束时改用DELETE，速度稍慢但兼容约束
    # conn.execute(text(f"DELETE FROM [{schema_name}].[{table_name}]"))
    
    # 用append模式插入，chunksize设置为1000~10000的合理值，不要设为1导致插入过慢拉长事务周期
    df.to_sql(
        name=table_name,
        schema=schema_name,
        con=conn,
        if_exists="append",
        index=False,
        chunksize=1000
    )

方案2：开启数据库读提交快照隔离（无侵入解决读写阻塞） #

如果允许其他用户在你写入过程中读取旧版本的表数据，可以让DBA执行以下SQL开启数据库的READ_COMMITTED_SNAPSHOT选项：

ALTER DATABASE 你的数据库名称 SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

开启后读操作会走行版本快照，不需要请求共享锁，既不会被你的写入事务阻塞，也不会读到未提交的脏数据，不需要修改业务代码即可解决大部分读写阻塞问题。

额外说明 #

如果你需要同步更新目标表的结构，才需要用到if_exists='replace'，这种场景建议先在事务外完成表结构校验/重建，再在事务内执行数据写入，避免把表结构操作放到大数据量写入的长事务中放大阻塞影响。

内容的提问来源于stack exchange，提问作者Dmitrii Davs