如何在带子查询的未使用折扣码查询中锁定选中行？

问题描述 #

我使用以下SQL查询获取未被使用的折扣码（即AvailableCodes表中不在Usage表内的码）：

SELECT code, type
FROM
(
  SELECT avc.code, avc.type,
         COUNT(CASE WHEN avc.type = 'type_1' THEN 1 END) OVER () cn1,
         COUNT(CASE WHEN avc.type = 'type_3' THEN 1 END) OVER () cn2,
         ROW_NUMBER() OVER (PARTITION BY avc.type ORDER BY avc.id) rn
  FROM  AvailableCodes avc left JOIN Usage usg
  ON usg.code = avc.code
  WHERE usg.code IS NULL
) t
WHERE (cn1 >= 2 AND cn2 >= 1) AND
      ((type = 'type_1' AND rn <= 2) OR (type = 'type_3' AND rn <= 1))
ORDER BY type, code

我的需求是：

• 锁定查询选中的行，阻止其他用户读取这些行，但允许读取其他未被选中的码，避免多用户同时获取同一批码
• 后续将查询到的码（比如['111', '222', '1232']）批量插入到Usage表，确保这些码不会被重复使用
• 实际场景支持用户自定义不同类型折扣码的数量（例如5个type_1、3个type_2、10个type_3），每个码仅能使用一次

我不确定with(rowlock, updlock, holdlock)该放在何处，才能仅锁定AvailableCodes表中选中的行，同时不影响其他行的读取。

解决方案 #

1. 正确的锁语法位置 #

只需将表提示直接附加到AvailableCodes表的引用后，即可仅锁定符合条件的行。修改后的查询如下：

SELECT code, type
FROM
(
  SELECT avc.code, avc.type,
         COUNT(CASE WHEN avc.type = 'type_1' THEN 1 END) OVER () cn1,
         COUNT(CASE WHEN avc.type = 'type_3' THEN 1 END) OVER () cn2,
         ROW_NUMBER() OVER (PARTITION BY avc.type ORDER BY avc.id) rn
  FROM  AvailableCodes avc WITH (ROWLOCK, UPDLOCK, HOLDLOCK)
  LEFT JOIN Usage usg
  ON usg.code = avc.code
  WHERE usg.code IS NULL
) t
WHERE (cn1 >= 2 AND cn2 >= 1) AND
      ((type = 'type_1' AND rn <= 2) OR (type = 'type_3' AND rn <= 1))
ORDER BY type, code

2. 锁参数说明 #

• ROWLOCK：强制使用行级锁，避免锁升级为页锁或表锁，确保仅锁定选中的目标行，不影响其他行的正常读取
• UPDLOCK：添加更新锁，其他事务可以读取这些行但无法添加更新锁或修改，防止并发事务同时选中同一批码
• HOLDLOCK：将锁持有至事务结束，确保从查询到插入Usage表的整个流程中，目标行始终被锁定，避免中间被其他事务抢占

3. 完整原子性事务流程 #

为了保证操作的原子性（要么全成功要么全失败），必须将查询和插入操作放在同一个事务中：

BEGIN TRANSACTION

-- 第一步：查询并锁定目标折扣码，存入临时表
DECLARE @SelectedCodes TABLE (code VARCHAR(50), type VARCHAR(50))

INSERT INTO @SelectedCodes (code, type)
SELECT code, type
FROM
(
  SELECT avc.code, avc.type,
         COUNT(CASE WHEN avc.type = 'type_1' THEN 1 END) OVER () cn1,
         COUNT(CASE WHEN avc.type = 'type_3' THEN 1 END) OVER () cn2,
         ROW_NUMBER() OVER (PARTITION BY avc.type ORDER BY avc.id) rn
  FROM  AvailableCodes avc WITH (ROWLOCK, UPDLOCK, HOLDLOCK)
  LEFT JOIN Usage usg
  ON usg.code = avc.code
  WHERE usg.code IS NULL
) t
WHERE (cn1 >= 2 AND cn2 >= 1) AND
      ((type = 'type_1' AND rn <= 2) OR (type = 'type_3' AND rn <= 1))
ORDER BY type, code

-- 第二步：批量插入到Usage表（根据业务需求补充用户ID、创建时间等字段）
INSERT INTO Usage (code, type, user_id, created_time)
SELECT code, type, '当前用户ID', GETDATE()
FROM @SelectedCodes

-- 提交事务，释放锁
COMMIT TRANSACTION

4. 扩展到自定义数量场景 #

如果需要支持用户指定不同类型的折扣码数量，可以通过参数化查询实现：

BEGIN TRANSACTION

-- 定义用户需要的各类型数量
DECLARE @Type1Count INT = 5
DECLARE @Type2Count INT = 3
DECLARE @Type3Count INT = 10

DECLARE @SelectedCodes TABLE (code VARCHAR(50), type VARCHAR(50))

-- 查询并锁定对应数量的折扣码
INSERT INTO @SelectedCodes (code, type)
SELECT code, type
FROM
(
  SELECT avc.code, avc.type,
         ROW_NUMBER() OVER (PARTITION BY avc.type ORDER BY avc.id) rn
  FROM  AvailableCodes avc WITH (ROWLOCK, UPDLOCK, HOLDLOCK)
  LEFT JOIN Usage usg
  ON usg.code = avc.code
  WHERE usg.code IS NULL
    AND avc.type IN ('type_1', 'type_2', 'type_3')
) t
WHERE 
  (type = 'type_1' AND rn <= @Type1Count)
  OR (type = 'type_2' AND rn <= @Type2Count)
  OR (type = 'type_3' AND rn <= @Type3Count)

-- 可选：检查是否获取到足够数量的码，不足则回滚事务
IF (SELECT COUNT(*) FROM @SelectedCodes WHERE type = 'type_1') < @Type1Count
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR('type_1类型可用折扣码不足', 16, 1)
END
IF (SELECT COUNT(*) FROM @SelectedCodes WHERE type = 'type_2') < @Type2Count
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR('type_2类型可用折扣码不足', 16, 1)
END
IF (SELECT COUNT(*) FROM @SelectedCodes WHERE type = 'type_3') < @Type3Count
BEGIN
  ROLLBACK TRANSACTION
  RAISERROR('type_3类型可用折扣码不足', 16, 1)
END

-- 批量插入使用记录
INSERT INTO Usage (code, type, user_id, created_time)
SELECT code, type, '当前用户ID', GETDATE()
FROM @SelectedCodes

COMMIT TRANSACTION

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