关于在AWS Lambda中使用sqlalchemy未显示关闭session的坑

目录

现象：

问题原因：

解决方案：

---

现象：

两次Lambda Function执行的时候，查询同一张表的数据，通过request_id和source字段查询。第一次查询时能正确返回数据，但是后续的查询不能查询到数据，返回的数据一直为None。当Lambda容器销毁后，下次请求重新创建容器后，复现上面的场景。

第一次查询

请求参数：

{
  "requestId":"e3bf23d4-38f9-4736-923c-7db35a0cacc5",
  "source":"test"
}

请求的SQL

SELECT
  dw_async_write_request.id AS dw_async_write_request_id,
  dw_async_write_request.STATUS AS dw_async_write_request_status,
  dw_async_write_request.failed_reason AS dw_async_write_request_failed_reason,
  dw_async_write_request.created_on AS dw_async_write_request_created_on,
  dw_async_write_request.updated_on AS dw_async_write_request_updated_on,
  dw_async_write_request.operator AS dw_async_write_request_operator,
  dw_async_write_request.schema_name AS dw_async_write_request_schema_name,
  dw_async_write_request.table_name AS dw_async_write_request_table_name,
  dw_async_write_request.write_operate_type AS dw_async_write_request_write_operate_type,
  dw_async_write_request.source_json AS dw_async_write_request_source_json,
  dw_async_write_request.COMMENT AS dw_async_write_request_comment,
  dw_async_write_request.ext_info AS dw_async_write_request_ext_info,
  dw_async_write_request.version AS dw_async_write_request_version,
  dw_async_write_request.old_data AS dw_async_write_request_old_data,
  dw_async_write_request.new_data AS dw_async_write_request_new_data,
  dw_async_write_request.request_id AS dw_async_write_request_request_id,
  dw_async_write_request.source AS dw_async_write_request_source 
FROM
  dw_async_write_request 
WHERE
  dw_async_write_request.request_id = % (request_id_1) s 
  AND dw_async_write_request.source = % (source_1) s

结果：第一次能正确查询去数据对象

第二次查询

请求参数：

{
  "requestId":"cc9d29a7-5f36-4292-9514-82bb81f5098b",
  "source":"test"
}

请求的SQL：

SELECT
  dw_async_write_request.id AS dw_async_write_request_id,
  dw_async_write_request.STATUS AS dw_async_write_request_status,
  dw_async_write_request.failed_reason AS dw_async_write_request_failed_reason,
  dw_async_write_request.created_on AS dw_async_write_request_created_on,
  dw_async_write_request.updated_on AS dw_async_write_request_updated_on,
  dw_async_write_request.operator AS dw_async_write_request_operator,
  dw_async_write_request.schema_name AS dw_async_write_request_schema_name,
  dw_async_write_request.table_name AS dw_async_write_request_table_name,
  dw_async_write_request.write_operate_type AS dw_async_write_request_write_operate_type,
  dw_async_write_request.source_json AS dw_async_write_request_source_json,
  dw_async_write_request.COMMENT AS dw_async_write_request_comment,
  dw_async_write_request.ext_info AS dw_async_write_request_ext_info,
  dw_async_write_request.version AS dw_async_write_request_version,
  dw_async_write_request.old_data AS dw_async_write_request_old_data,
  dw_async_write_request.new_data AS dw_async_write_request_new_data,
  dw_async_write_request.request_id AS dw_async_write_request_request_id,
  dw_async_write_request.source AS dw_async_write_request_source 
FROM
  dw_async_write_request 
WHERE
  dw_async_write_request.request_id = % (request_id_1) s 
  AND dw_async_write_request.source = % (source_1) s

可以看到第一次和第二次请求的SQL内容是一致的，只是传入的参数不一致。

结果：第二次返回的结果为None，但是数据库里面是存在的

问题原因：

对sqlalchemy的session管理不规范，每次请求后没有手动关闭session，session上下文冲突导致的。

解决方案：

当每次lambda函数执行后，不管结果如何对session进行显示的关闭，下次请求重新开启一个session

def lambda_handler(event, context):
    records = event['Records']
    subscribe_configs: list[EventSubscribeConfig] = db.query_event_subscribe_config()
    try:
        for record in records:
            if record['eventSource'] == 'aws:sqs':
                body = json.loads(record['body'])
                # 业务逻辑
    finally:
       session.remove() #显示关闭session