SQL Server How to lock the selected rows in this query with sub query?

kd3sttzy 于 2023-03-22 发布在其他

关注(0)|答案(1)|浏览(112)

I'm using the following query to get number of unused codes - codes from AvailableCodes table that aren't on Usage table:

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

I want to lock the selected rows from reading but allow other codes to be read in case more than one user try to reach this code simultaneously.

But I'm not sure where to put the lock part, with(rowlock, updlock, holdlock) in a way that will lock only those selected rows from AvailableCodes for read but allow other rows to be read.

Update: To be more clear in what I actually need to happen: After the query above, I get an array of codes that I need to insert into the Usage table, for example: ['111', '222', '1232'] .

Then I need to insert them to Usage so that no other users will use these codes again. I insert them in bulk in same query after the above query.

Update 2:

What I'm trying to implement is a system where users can select number of discount codes of each type. For example they want 5 codes from type_1 , and 3 codes from type_2 , and 10 codes from type_3 .

And each code should not be used more than once.

So my query selects the available codes in bulk and inserts them in bulk

sql-server

来源：https://stackoverflow.com/questions/75785332/how-to-lock-the-selected-rows-in-this-query-with-sub-query

1条答案

按热度按时间

wwodge7n1#

This answer was substantially changed after further clarification.

Your insert query needs HOLDLOCK to ensure that the "emptiness" of the intended rows is locked.

Exactly what insert your logic you are trying to achieve is unclear, but you can do this in a single statement with no explicit transaction necessary, just a locking hint.

INSERT Usage (code)
SELECT code
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
  WHERE NOT EXISTS (SELECT 1
      FROM Usage usg WITH (HOLDLOCK)
      WHERE usg.code = avc.code
  )
) t
WHERE (cn1 >= 2 AND cn2 >= 1) AND
      ((type = 'type_1' AND rn <= 2) OR (type = 'type_3' AND rn = 1))
;

Instead of a locking hint, you can also set the isolation level for the whole statement.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Note that this will cause the same isolation level for all table references though.

赞(0）回复(0）举报 2023-03-22

我来回答

SQL Server How to lock the selected rows in this query with sub query?

1条答案

相关问题

热门标签

最新问答