SSMS批量写入时随机出现Fractional truncation与死锁问题求助

问题概述 #

需将过去8年的数据按季度写入数据库，每季度分为9批执行。处理32个季度（8×4）的数据时，会随机在某一环节报错，出现大量「Fractional truncation」错误，还夹杂一次死锁错误。因数据量庞大，无法定位具体报错行，但手动重跑报错批次却能成功，无法缩小问题范围。整个处理流程耗时数日，无法全程监控并手动重试。

具体流程 #

从SQL Server的Table A读取数据到Python，生成预测的X、Y列后写入Table B（Table A基础上新增X、Y、Z时间戳列）。

已排查内容 #

• Table A与Table B的重叠列类型完全一致
• Python中已校验空值、极值、字段长度，并用正则验证浮点数是否适配decimal类型，X、Y列已确保适配decimal(7,2)
• 手动重跑批次可成功，推测问题并非数据本身导致

疑问 #

1. Fractional truncation错误的成因是什么？
2. 死锁错误是否相关？开发服务器仅少数人有权限，几乎无人读取该表，是否是批量写入时SQL Server后台操作引发冲突？

附加信息 #

• 每季度约850万行、14列数据

报错信息 #

Exception has occurred: DBAPIError x
(pyodbc.Error) ('01S07', '[01S07] [Microsoft][ODBC Driver 17 for SQL Server] Fractional truncation (0) (SQLExecute); [01S07] [Microsoft][ODBC Driver 17 for SQL Server] Fractional truncation (0) (SQLExecute); [01S07] [Microsoft][ODBC Driver 17 for SQL Server] Fractional truncation (0) (SQLExecute); [01S07] [Microsoft][ODBC Driver 17 for SQL Server] Fractional truncation (0) (SQLExecute); [01S07] [Microsoft][ODBC Driver 17 for SQL Server] Fractional truncation (0) (SQLExecute); [01S07] [Microsoft][ODBC Driver 17 for SQL Server] Fractional truncation (0) (SQLExecute); [01S07] [Microsoft][ODBC Driver 17 for SQL Server] Fractional truncation (0) (SQLExecute); [01S07] [Microsoft][ODBC Driver 17 for SQL Server] Transaction (Process ID 517) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205); (SQLExecute); [01S07] [Microsoft][ODBC Driver 17 for SQL Server] Fractional truncation (0) (SQLExecute); ...

数据库写入脚本 #

def write_to_db(
    data,
    table,
    database,
    cred=cred,
    server=server,
    schema=schema,
    if_exists="append",
    index=False,
    chunksize=100,
):
    """
    用途：将数据写入SSMS数据库系统。
    输入：
        data -- 包含待写入数据的DataFrame
        table -- 目标表名
        database -- 登录的数据库
        cred -- 登录凭证文件路径
        server -- 操作的SSMS服务器
        schema -- 写入使用的Schema
        if_exists -- 处理同名表的方式：
        - Append：追加新行
        - Replace：替换整张表
        - Truncate：删除所有行后追加新行（不删除表）
        index, chunksize -- 传递给pandas方法
    输出：
        None
    """
    user, passw = get_info(cred)

    cn_str = (
        "Driver={ODBC Driver 17 for SQL Server};"
        f"Server={server},1433;"
        f"Database={database};"
        f"UID={user};"
        f"PWD={passw}"
    )

    connection_uri = (
        "mssql+pyodbc:///?"
        f"odbc_connect={urllib.parse.quote_plus(cn_str)}"
        "&autocommit=true"
    )
    engine = sa.create_engine(connection_uri, fast_executemany=True)

    # 检查表是否存在
    if if_exists in ["truncate", "append"]:
        with pyodbc.connect(cn_str) as con:
            with con.cursor() as cursor:
                if not cursor.tables(table=table, tableType="TABLE").fetchone():  # noqa
                    raise ValueError(
                        f"Object [{database}].[{schema}].[{table}] "
                        "不存在"  # noqa
                    )

    # 按需截断表
    if if_exists == "truncate":
        with pyodbc.connect(cn_str) as con:
            with con.cursor() as cursor:
                query = f"truncate table [{database}].[{schema}].[{table}]"
                cursor.execute(query)
                if_exists = "append"

    data.to_sql(
        table,
        engine,
        index=index,
        schema=schema,
        if_exists=if_exists,
        chunksize=chunksize,
    )
    return None

环境信息 #

• Python 3.10.10
• sqlalchemy 1.4.47
• Microsoft SQL Server 2019

表结构DDL #

源表Table A

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [YourSchemaName].[YourSourceTable](
    [A] [bigint] NOT NULL,
    [B] [bigint] NULL,
    [C] [varchar](25) NOT NULL,
    [D] [int] NOT NULL,
    [E] [int] NOT NULL,
    [F] [decimal](18, 2) NOT NULL,
    [G] [decimal](18, 2) NOT NULL,
    [H] [tinyint] NOT NULL,
    [I] [tinyint] NOT NULL,
    [J] [varchar](6) NOT NULL,
    [K] [varchar](4) NOT NULL,
    [L] [int] NULL,
    [M] [varchar](20) NULL,
    [N] [varchar](30) NULL,
    [O] [varchar](6) NULL,
    [P] [varchar](50) NULL,
    [Q] [decimal](15, 12) NULL,
    [R] [decimal](15, 2) NULL,
    [S] [bit] NOT NULL,
    [T] [date] NOT NULL,
    [U] [varchar](50) NOT NULL,
 CONSTRAINT [PK_AnonymousTable] PRIMARY KEY CLUSTERED 
(
    [A] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [YourSchemaName].[YourSourceTable] ADD  DEFAULT ('TypeA') FOR [U]
GO

目标表Table B

USE [yourdatabase]
GO

/****** Object:  Table [python].[AVM]    Script Date: 18-8-2023 13:37:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [yourschema].[yourtable](
    A [bigint] NOT NULL,
    V [date] NOT NULL,
    W [decimal](7, 2) NOT NULL,
    X [int] NOT NULL,
    Y [int] NOT NULL,
    Z [int] NOT NULL,
    H [tinyint] NOT NULL,
    I [tinyint] NOT NULL,
    G [decimal](18, 2) NOT NULL,
    C [varchar](25) NOT NULL,
    D [int] NOT NULL,
    F [decimal](18, 2) NOT NULL,
    J [varchar](6) NOT NULL,
    K [varchar](4) NOT NULL,
    ZZ [date] NOT NULL
) ON [PRIMARY]
GO

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