Azure Function未正常关闭连接，陷入休眠无法完成执行

我正在使用Python V1版本的Azure Function App处理HTTP请求，通过SQL输出绑定实现数据库UPSERT操作。目前遇到问题：函数未正常关闭连接，陷入休眠状态，无法按预期完成执行。

主函数代码（init.py） #

import logging
from datetime import datetime
import azure.functions as func

def main(
    req: func.HttpRequest,
    tabproductsOldCategory: func.Out[func.SqlRow],
    tabproducts: func.Out[func.SqlRow],
) -> func.HttpResponse:
    logging.info(
        "Python HTTP trigger and SQL output binding function processed a request."
    )

    try:
        products = []
        OldCategory = []
        req_body = req.get_json()
        for item in req_body:
            if "num_pd" in item.keys():
                products.append(item)

            elif "num_sq" in item.keys():

                OldCategory.append(item)

            else:
                logging.error("Missing necessary fields: num_pd or num_sq")
                return func.HttpResponse(
                    "Missing necessary fields: num_pd or num_sq", status_code=400
                )

        logging.info(f"Processed request body. Found {len(products)} products and {len(OldCategory)} OldCategory.")
        rows_products = None
        rows_OldCategory = None

        if len(products) > 0:
            rows_products = func.SqlRowList(
                map(lambda r: func.SqlRow.from_dict(r), products)
            )
            logging.info(rows_products)
        if len(OldCategory) > 0:
            rows_OldCategory = func.SqlRowList(
                map(lambda r: func.SqlRow.from_dict(r), OldCategory)
            )
            logging.info(rows_OldCategory)

    except Exception as e:
        logging.error(f"An error occurred: {e}")
        return func.HttpResponse(
            "An error occurred while processing your request.", status_code=500
        )

    try:

        if isinstance(rows_products, func.SqlRowList):
            logging.info("UPSERTING products")
            tabproducts.set(rows_products)
            logging.info("products UPSERTED")
        if isinstance(rows_OldCategory, func.SqlRowList):
            logging.info("UPSERTING OldCategory")
            tabproductsOldCategory.set(rows_OldCategory)
            logging.info("OldCategory UPSERTED")

        return func.HttpResponse(
            body=req.get_body(),
            status_code=201,
            mimetype="application/json",
        )
        
    except Exception as e:
        logging.info("An error occurred while upserting the rows into the SQL tables.")
        logging.error(f"Exception: {e}")
        return func.HttpResponse(f"Error processing request: {e}", status_code=400)

function.json配置 #

{
  "scriptFile": "__init__.py",
  "bindings": [
    {
      "authLevel": "function",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req",
      "methods": [
        "post"
      ]
    },
    {
      "type": "http",
      "direction": "out",
      "name": "$return"
    },
    {
      "name": "tabproductsOldCategory",
      "type": "sql",
      "direction": "out",
      "commandText": "external_source.tab_products_old_category",
      "connectionStringSetting": "SQLConnectionString"
    },
    {
      "name": "tabproducts",
      "type": "sql",
      "direction": "out",
      "commandText": "external_source.tab_products",
      "connectionStringSetting": "SQLConnectionString"
    }
  ]
}

host.json配置 #

{
  "version": "2.0",
  "functionTimeout": "00:05:00"
  },
  "logging": {
    "applicationInsights": {
      "samplingSettings": {
        "isEnabled": true,
        "excludedTypes": "Request"
      }
    },
    "fileLoggingMode": "debugOnly",
    "logLevel": {
      "Function.my-func": "Information",
      "default": "None"
    }
  },
  "extensionBundle": {
    "id": "Microsoft.Azure.Functions.ExtensionBundle",
    "version": "[4.*, 5.0.0)"
  }
}

解决思路 #

• 修正host.json语法错误：当前配置中functionTimeout字段后多了一个逗号，会导致配置解析异常，进而影响函数运行稳定性。修正后的配置参考：

  {
    "version": "2.0",
    "functionTimeout": "00:05:00",
    "logging": {
      "applicationInsights": {
        "samplingSettings": {
          "isEnabled": true,
          "excludedTypes": "Request"
        }
      },
      "fileLoggingMode": "debugOnly",
      "logLevel": {
        "Function.my-func": "Information",
        "default": "None"
      }
    },
    "extensionBundle": {
      "id": "Microsoft.Azure.Functions.ExtensionBundle",
      "version": "[4.*, 5.0.0)"
    }
  }
  

• 优化SQLRowList生成逻辑：map函数返回的是迭代器，直接传入SqlRowList可能导致迭代过程中出现未预期的问题。建议转换为列表后再传入，确保数据被正确序列化：

  rows_products = func.SqlRowList(
      list(map(lambda r: func.SqlRow.from_dict(r), products))
  )
  

  对OldCategory的处理做同样修改。

• 调整SQL连接字符串配置：在连接字符串中添加连接超时和命令超时参数，避免因数据库响应缓慢导致连接挂起。例如：

  Server=tcp:{server}.database.windows.net,1433;Initial Catalog={db};Persist Security Info=False;User ID={user};Password={pwd};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Command Timeout=60;
  

• 检查数据库端状态：登录数据库查看是否存在未提交的事务、锁等待或长时间运行的查询，这些情况会导致函数端连接无法正常释放。

• 升级扩展包版本：当前使用的扩展包范围是[4.*,5.0.0)，尝试升级到最新的稳定版，修复可能存在的SQL绑定连接管理bug。

• 限制批量数据大小：如果请求体包含大量数据，拆分批次进行UPSERT操作，避免单次数据库操作负载过高导致连接长时间占用。

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