使用pyodbc执行MERGE语句后SSMS查询无限执行问题求助

使用AWS Lambda实现API，通过Python 3.11将数据插入SQL Server数据库，代码执行成功并返回200状态码，但在SSMS中执行SELECT查询时出现无限执行的情况。

代码片段 #

conn_str = (
    f'Driver={{ODBC Driver 17 for SQL Server}};'
    f'Database=TMP;'
    f'Encrypt=no;'
)
data = [{
    "sfid" : "aws1123124",
    "lastmodifieddate" : "a551231",
    "comments" : "a11231",
    "keyword" : "a41231231",
    "productcode" : "s55234234",
    "serialNumber" : "d1123123"
}]
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute("BEGIN TRANSACTION")
try :
    for record in data:
            print("in record")
            print(record['sfid'])
            merge_sql = """
            MERGE INTO [dbo].[LocalDB] AS Target
            USING (SELECT ? AS sfid, ? AS lastmodifieddate, ? AS comments, ? AS keyword, ? AS productcode, ? AS serialNumber) AS Source
            ON Target.sfid = Source.sfid
            WHEN MATCHED THEN
                UPDATE SET 
                    lastmodifieddate = Source.lastmodifieddate, 
                    comments = Source.comments, 
                    keyword = Source.keyword, 
                    productcode = Source.productcode, 
                    serialNumber = Source.serialNumber
            WHEN NOT MATCHED THEN
                INSERT (sfid, lastmodifieddate, comments, keyword, productcode, serialNumber)
                VALUES (Source.sfid, Source.lastmodifieddate, Source.comments, Source.keyword, Source.productcode, Source.serialNumber);
            """
            cursor.execute(merge_sql, 
                           record['sfid'], 
                           record['lastmodifieddate'], 
                           record['comments'], 
                           record['keyword'],
                           record['productcode'],
                           record['serialNumber'])
    conn.commit()
except Exception as e:
    cursor.execute("ROLLBACK")
    print(f"An error occurred: {e}")
    raise e
finally:
    if cursor is not None:
        try:
            cursor.close()
        except Exception as ex:
            print(f"Error closing cursor: {ex}")
    
    if conn is not None:
        try:
            conn.close()
        except Exception as ex:
            print(f"Error closing connection: {ex}")

# TODO implement
return {
    'statusCode': 200,
    'body': json.dumps('Hello from Lambda!')
}

1. 修复事务与锁问题 #

• 移除手动开启的事务语句cursor.execute("BEGIN TRANSACTION")：pyodbc默认处于自动提交关闭状态，conn.commit()已经可以正确处理事务，手动开启事务若逻辑疏漏会导致事务未结束，持有表锁。
• 给LocalDB表的sfid字段添加主键或唯一非聚集索引：MERGE的ON条件依赖该字段匹配，无索引会触发全表扫描，引发锁等待。

2. 修正MERGE语句的潜在风险 #

• 检查字段类型匹配：如果lastmodifieddate是日期类型，传入字符串"a551231"会触发隐式类型转换，拖慢执行甚至阻塞，需确保参数类型与表字段一致。
• 可替换MERGE为UPDATE+INSERT语句测试，排除MERGE本身的问题：

  update_sql = """
  UPDATE [dbo].[LocalDB] 
  SET lastmodifieddate = ?, comments = ?, keyword = ?, productcode = ?, serialNumber = ?
  WHERE sfid = ?;
  """
  insert_sql = """
  INSERT INTO [dbo].[LocalDB] (sfid, lastmodifieddate, comments, keyword, productcode, serialNumber)
  VALUES (?, ?, ?, ?, ?, ?);
  """
  # 执行更新
  cursor.execute(update_sql, 
                 record['lastmodifieddate'], record['comments'], record['keyword'],
                 record['productcode'], record['serialNumber'], record['sfid'])
  # 若无更新则插入
  if cursor.rowcount == 0:
      cursor.execute(insert_sql, 
                     record['sfid'], record['lastmodifieddate'], record['comments'],
                     record['keyword'], record['productcode'], record['serialNumber'])
  

3. 优化Lambda资源释放 #

• 在连接字符串添加超时参数，避免连接挂起：

  conn_str = (
      f'Driver={{ODBC Driver 17 for SQL Server}};'
      f'Database=TMP;'
      f'Encrypt=no;'
      f'Connection Timeout=30;'
      f'Timeout=30;'
  )
  

• 确保连接和游标在finally块中彻底释放，Lambda执行环境复用可能导致残留连接持有锁。

4. 排查SQL Server阻塞进程 #

在SSMS执行以下查询，定位阻塞会话：

SELECT 
    session_id, 
    blocking_session_id, 
    wait_type, 
    wait_time, 
    sql_text = SUBSTRING(st.text, (er.statement_start_offset/2)+1, 
    ((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1)
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE blocking_session_id <> 0;

找到blocking_session_id对应的会话，用KILL <session_id>终止（生产环境需谨慎操作）。

内容的提问来源于stack exchange，提问作者강명준