Azure Cosmos DB存储过程调试 排查执行卡住、响应过大报错

问题现象 #

调用DocumentClient.ExecuteStoredProcedureAsync<object>(...)执行存储过程时抛出如下错误：

Error: Resulting message would be too large because of \"Body\". Return from script with current message and use continuation token to call the script again or modify your script.

在Azure Portal中复现调用时，存储过程持续数分钟无返回，F12开发者工具网络面板无报错记录。

涉及存储过程代码 #

function (contacts, companyReference, propertyReference) {
    var context = getContext();
    var collection = context.getCollection();
    var response = context.getResponse();
    var messageThreads = [];
    if (!contacts) throw new Error("contacts cannot be null");
    if (contacts.length == 0) {
        response.setBody(JSON.stringify(messageThreads));
    }
    var contactsLength = contacts.length - 1;
    var count = 0;
    GetContactMessages(contacts[count], callback);
    function GetContactMessages(cnt, callback) {
        var qry = 'SELECT r.Subject, r.Message, r.Participants, r.FromAddress, r.ToAddress, r.ThreadID, r.Sent, r.AttachmentUrls, r.id FROM root r WHERE r.DocType = 1 AND r.ToAddress.CompanyReference = "' + companyReference + '" AND r.ToAddress.ContactReference = "' + cnt.Item1 + '" AND r.ToAddress.ContactReferenceType = ' + cnt.Item2;
        if (cnt.ContactReferenceType === 3) {
            //owner contact type. return messages which are either about the owner or about a specific property.
            qry = qry + ' AND (r.EntityType = 0 OR (r.EntityType = 2 AND r.EntityReference = "' + propertyReference + '"))';
        }
        var accept = collection.queryDocuments(collection.getSelfLink(), qry, { pageSize: 1000 }, callback);
        if (!accept) throw "Unable to read messages, abort ";
    }
    function MessageExists(messages, messageId) {
        for (var i = 0; i < messages.length; i++) {
            if (messages[i].MessageID == messageId) {
                return true;
            }
        }
        return false;
    }
    function callback(err, documents, responseOptions) {
        if (err) throw new Error("Error" + err.message);
        var messages = documents;
        for (var i = 0; i < messages.length; i++) {
            var message = messages[i];
            var threadFound = false;
            var alreadyMatched = false;
            var matched = false;
            for (var j = 0; j < messageThreads.length; j++) {
                if (messageThreads[j].ThreadID == message.ThreadID) {
                    var thread = messageThreads[j];
                    if (message.Sent < thread.ThreadStartDate) {
                        thread.ThreadStartDate = message.Sent;
                    }
                    if (!MessageExists(thread.Messages, message.id)) {
                        thread.Messages.push({ MessageID: message.id, MessageDate: message.Sent, Sender: message.FromAddress.DisplayName, Message: message.Message, AttachmentUrls: message.AttachmentUrls });
                    }
                    threadFound = true;
                    break;
                }
            }
            if (!threadFound) {
                messageThreads.push({
                    Subject: message.Subject,
                    ThreadID: message.ThreadID,
                    Participants: message.Participants,
                    ThreadStartDate: message.Sent,
                    Messages: [{ MessageID: message.id, MessageDate: message.Sent, Sender: message.FromAddress.DisplayName, Message: message.Message, AttachmentUrls: message.AttachmentUrls }]
                });
            }
        }
        count++;
        if (count > contactsLength) {
            response.setBody(JSON.stringify(messageThreads));
        }
        else {
            GetContactMessages(contacts[count], callback);
        }
    }
}

调用传入参数 #

• 分区键值：e8fd4796-ee13-4fb1-9417-23f1fb6c86af
• contacts参数：[{ContactReferenceType:3, ContactReference:"872730"}, {ContactReferenceType:4, ContactReference:"872734"}]
• companyReference参数：e8fd4796-ee13-4fb1-9417-23f1fb6c86af
• propertyReference参数：229113

故障定位与调试方法 #

先排查显性代码bug #

1. 参数属性名不匹配：传入的contacts对象属性名是ContactReference、ContactReferenceType，但存储过程拼接SQL时取的是cnt.Item1、cnt.Item2，这两个属性不存在，拼接出的SQL条件会变成r.ToAddress.ContactReference = "undefined"，直接导致查询扫描分区内所有DocType=1的文档，拉取的数据量远超预期，既容易触发响应大小上限，也会因为长时间全量扫描表现为执行无响应。
2. 逻辑分支缺失终止：当contacts.length == 0时，调用response.setBody后没有加return终止执行，后续代码会继续尝试读取contacts[0]触发数组越界。

通用存储过程调试手段 #

Cosmos DB存储过程本身不支持单步调试，用以下方法定位卡点：

• 埋点打标：在每个关键节点（函数入口、SQL拼接完成后、查询回调入口、循环计数更新处）主动抛错，把当前执行位置、关键变量值（比如拼接后的SQL、当前count值、已拉取文档数、结果集大小）放到错误信息里返回，逐段缩小排查范围。第一次埋点直接放在GetContactMessages里输出拼接完成的qry，立刻就能发现属性名不匹配的问题。
• 缩量测试：测试时先把pageSize从1000改成1，只传1个contact参数，验证单条查询逻辑通顺后再逐步放大数据量。
• 单独校验SQL：把埋点拿到的拼接SQL直接放到门户的数据资源管理器里执行，检查返回结果、请求RU消耗是否符合预期，确认有没有出现全表扫描、过滤条件不生效的问题。
• 核对服务限制：Cosmos DB存储过程单次执行最长超时为5秒，返回响应体大小不能超过2MB，一旦触发阈值服务端会终止执行，门户没有做对应的错误提示时就会表现为一直加载无返回。

响应过大问题修复 #

修复参数问题后如果仍报响应过大错误，说明最终聚合的messageThreads大小超过2MB限制，必须实现分批续传逻辑：

• 单次存储过程执行只处理固定数量的文档，达到大小阈值时，把已处理的结果、查询延续令牌、当前处理到的contact索引打包返回。
• 客户端收到返回后判断是否存在未完成的续传标记，如果有就带着标记和上次的处理进度再次调用存储过程，直到所有数据处理完成，最后合并所有批次的结果即可。

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