存储过程自锁问题：批量插入后去重操作超时锁表求助

问题背景 #

我们通过存储过程向table1插入数据，之后需要执行去重操作保证数据唯一性。但处理20K+条记录时，去重的DELETE语句会导致表锁定直至超时，少量数据则无此问题。

原插入语句 #

INSERT INTO table1 (momt, message_log_id, sender, receiver, msgdata, smsc_id, sms_type, coding, dlr_mask, dlr_url, validity, boxc_id, carrier_id, destination)
    SELECT 'mt', @messageBatchKey, @sender, rtrim(ltrim(e.country_code))+e.sub_user_number, msgdata = 
    CASE
        WHEN s.lang = 'english' AND A.sms_limit = '1' THEN LEFT(@messageValuesms, 160)
        WHEN s.lang = 'english' AND A.sms_limit = '2' THEN LEFT(@messageValuesms, 360)
        WHEN s.lang = 'spanish' THEN (SELECT message from message_log_lang where message_log_id = @messageBatchKey and language = 'ES')
        /***WHEN s.lang = 'spanish' THEN LEFT(@messageValuesp, 160)***/
        WHEN s.lang = 'creole' THEN LEFT(@messageValuecr, 160)
        ELSE LEFT(@messageValuesms, 160)
    END
    , @smscID, '2', '0', '3', 
    @dlr_url + 'sub_id=' + CAST(s.sub_id AS varchar(50)) + '&carrierID=' + ltrim(rtrim(CAST(e.sub_carrier_id AS varchar(10)))), NULL,
    'inspironMT', c.carrier_id, 
    CASE
        WHEN @accountDestination <> 3 THEN @accountDestination
        ELSE 0
    END
    FROM 
        SUBSCRIPTION s 
    INNER JOIN 
        #distinctSubscriptionKeys d ON s.sub_id = d.distinctSubscriptionKey 
    INNER JOIN 
        PHONENUMBERS e ON s.sub_id = e.sub_id
    INNER JOIN 
        CARRIERS c ON e.sub_carrier_id = c.carrier_id
    INNER JOIN 
        ACCOUNTS A ON s.account_id = a.account_id
    WHERE 
        e.comm_type = 't' 
        AND s.active = '1'
        AND a.sms_send_method = '0'
        AND s.send_email IN ('0', '2')
        AND c.active = 1
        AND (rtrim(ltrim(e.country_code)) = 1 OR rtrim(ltrim(e.country_code)) = '')
        /*AND c.carrier_id IN 
        (
        SELECT carrier_id
        FROM CarrierDestinationProperties
        WHERE send_method = 1
        AND (destination = @accountDestination OR @accountDestination = 3)
        )
        */  
        AND ((e.sub_carrier_id = '5' OR e.sub_carrier_id = '4')
             OR
             ((NOT 0 = (SELECT COUNT(*) FROM #subgroups)
               AND 0 = (SELECT COUNT(a.sub_group_id) 
                        FROM Wens.dbo.ACCOUNT_SUB_GROUPS AS a 
                        INNER JOIN #subgroups AS b ON a.sub_group_id = b.subgroupKey
                        WHERE (a.account_id = @accountKey) 
                          AND (a.active = '1') 
                          AND (a.sms_send_method = '1')))           
    ))
    ORDER BY 
        d.distinctSubscriptionKey

原去重语句 #

DELETE FROM table1
WHERE sql_id NOT IN (SELECT MIN(sql_id)
                     FROM table1
                     WHERE message_log_id = @messageBatchKey
                     GROUP BY receiver);

优化方案 #

核心解决思路：插入阶段直接避免重复 #

完全可以在插入的SELECT语句中通过分组逻辑过滤重复的receiver记录，从根源上省去后续的去重DELETE操作，这是解决锁表问题最有效的方案。

修改后的插入语句（用窗口函数去重）

通过ROW_NUMBER()窗口函数按receiver分组，为每组记录编号，只保留每组的第一条记录（可根据业务需求调整排序规则，比如保留最小/最大sub_id的记录）：

INSERT INTO table1 (momt, message_log_id, sender, receiver, msgdata, smsc_id, sms_type, coding, dlr_mask, dlr_url, validity, boxc_id, carrier_id, destination)
SELECT 
    momt, message_log_id, sender, receiver, msgdata, smsc_id, sms_type, coding, dlr_mask, dlr_url, validity, boxc_id, carrier_id, destination
FROM (
    SELECT 
        'mt' AS momt, 
        @messageBatchKey AS message_log_id, 
        @sender AS sender, 
        rtrim(ltrim(e.country_code))+e.sub_user_number AS receiver, 
        CASE
            WHEN s.lang = 'english' AND A.sms_limit = '1' THEN LEFT(@messageValuesms, 160)
            WHEN s.lang = 'english' AND A.sms_limit = '2' THEN LEFT(@messageValuesms, 360)
            WHEN s.lang = 'spanish' THEN (SELECT message from message_log_lang where message_log_id = @messageBatchKey and language = 'ES')
            /***WHEN s.lang = 'spanish' THEN LEFT(@messageValuesp, 160)***/
            WHEN s.lang = 'creole' THEN LEFT(@messageValuecr, 160)
            ELSE LEFT(@messageValuesms, 160)
        END AS msgdata,
        @smscID AS smsc_id, 
        '2' AS sms_type, 
        '0' AS coding, 
        '3' AS dlr_mask,
        @dlr_url + 'sub_id=' + CAST(s.sub_id AS varchar(50)) + '&carrierID=' + ltrim(rtrim(CAST(e.sub_carrier_id AS varchar(10)))) AS dlr_url, 
        NULL AS validity,
        'inspironMT' AS boxc_id, 
        c.carrier_id AS carrier_id, 
        CASE
            WHEN @accountDestination <> 3 THEN @accountDestination
            ELSE 0
        END AS destination,
        -- 按receiver分组，为每组记录编号，排序规则可根据业务调整
        ROW_NUMBER() OVER (PARTITION BY rtrim(ltrim(e.country_code))+e.sub_user_number ORDER BY s.sub_id) AS rn
    FROM 
        SUBSCRIPTION s 
    INNER JOIN 
        #distinctSubscriptionKeys d ON s.sub_id = d.distinctSubscriptionKey 
    INNER JOIN 
        PHONENUMBERS e ON s.sub_id = e.sub_id
    INNER JOIN 
        CARRIERS c ON e.sub_carrier_id = c.carrier_id
    INNER JOIN 
        ACCOUNTS A ON s.account_id = a.account_id
    WHERE 
        e.comm_type = 't' 
        AND s.active = '1'
        AND a.sms_send_method = '0'
        AND s.send_email IN ('0', '2')
        AND c.active = 1
        AND (rtrim(ltrim(e.country_code)) = 1 OR rtrim(ltrim(e.country_code)) = '')
        /*AND c.carrier_id IN 
        (
        SELECT carrier_id
        FROM CarrierDestinationProperties
        WHERE send_method = 1
        AND (destination = @accountDestination OR @accountDestination = 3)
        )
        */  
        AND ((e.sub_carrier_id = '5' OR e.sub_carrier_id = '4')
             OR
             ((NOT 0 = (SELECT COUNT(*) FROM #subgroups)
               AND 0 = (SELECT COUNT(a.sub_group_id) 
                        FROM Wens.dbo.ACCOUNT_SUB_GROUPS AS a 
                        INNER JOIN #subgroups AS b ON a.sub_group_id = b.subgroupKey
                        WHERE (a.account_id = @accountKey) 
                          AND (a.active = '1') 
                          AND (a.sms_send_method = '1')))           
    ))
) AS temp
WHERE rn = 1 -- 只保留每组的第一条记录
ORDER BY receiver;

备选方案：优化原有去重逻辑（若无法修改插入语句） #

如果必须保留原插入流程，可通过以下方式优化去重操作，减少锁表时间：

1. 改用CTE+窗口函数删除
   避免NOT IN可能带来的性能问题，同时更高效地定位重复记录：

   WITH CTE AS (
       SELECT 
           sql_id,
           ROW_NUMBER() OVER (PARTITION BY receiver ORDER BY sql_id) AS rn
       FROM table1
       WHERE message_log_id = @messageBatchKey
   )
   DELETE FROM CTE WHERE rn > 1;
   

2. 分批删除重复记录
   针对大数量重复数据，每次删除小批量记录，避免长时间锁表：

   WHILE 1 = 1
   BEGIN
       DELETE TOP (1000) FROM table1
       WHERE sql_id IN (
           SELECT sql_id
           FROM (
               SELECT 
                   sql_id,
                   ROW_NUMBER() OVER (PARTITION BY receiver ORDER BY sql_id) AS rn
               FROM table1
               WHERE message_log_id = @messageBatchKey
           ) AS temp
           WHERE rn > 1
       )
       IF @@ROWCOUNT = 0 BREAK;
   END
   

索引优化建议 #

• 为table1创建复合索引(message_log_id, receiver, sql_id)，大幅提升去重查询的效率
• 检查插入查询中关联表的索引：
  • 为SUBSCRIPTION创建(sub_id, account_id, active, send_email, lang)复合索引，加速JOIN和WHERE条件过滤
  • 为PHONENUMBERS创建(sub_id, comm_type, country_code, sub_user_number, sub_carrier_id)复合索引

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