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

Question

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

What are your actual requirements? Locking for the duration of the query is not very long... a query that immediately follows the first will not be locked. — Dale K, Mar 19 '23 at 22:36
And when you read the documentation about those lock types, what wasn't clear to you? — Dale K, Mar 19 '23 at 22:39
I don't want a situation where users get the same codes but also I'm afraid that if there are many people in the system then it might throw many users timeout errors unless I can lock specifically the rows of the selected rows. But so far I've only dealt with simple locks on simple selects where you can see what is locked. Here I'm not sure what will be locked exactly since it's calculating things on the way — pileup, Mar 19 '23 at 22:42
But if you don't want multiple users to see the same codes you need to lock the records until the first user has finished processing them? And thats not a database lock, thats user locking which you have to implement yourself. — Dale K, Mar 19 '23 at 22:43
Since I always SELECT the top rows, if many users use the script the locks won't help and I'll need to do something like: SELECT the rows that resulted in the above query with row lock, then for other users do the same and have a retry mechanism in case the codes are already in `Usage` code from the previous user? Is that what you meant by implementing it myself? — pileup, Mar 19 '23 at 22:49
In simple form, you have a locked column in the row (or a state column), and as part of a transaction you select the top X records and update them setting the locked column true, keeping an exclusive lock on all the rows for the duration of the transaction to avoid multiple users getting the same rows. The simplest lock is an exclusive table lock for the transaction, you can do a more granular lock, but I don't recall which off the top of my head (maybe an updlock, but thats where you check the docs). — Dale K, Mar 19 '23 at 22:51
I've never done something as complex. Do you have a starting point? Something to read that would direct me here? And is it something that I attach to this query using sql like a trigger, or a completely separate query/thing that I run after I get the rows in this query? My questions may sound ignorant but I just never dealt with that — pileup, Mar 19 '23 at 22:55
You're gonna have to do a bunch of reading, at a quick glance you could start here https://dba.stackexchange.com/questions/81791/strategies-for-checking-out-records-for-processing — Dale K, Mar 19 '23 at 22:59
How about you tell use what you are *actually* trying to achieve, what processing do you want to happen *after* locking these rows? Do you want to insert new rows, etc? Please be precise. Normally you would just insert or update in a single statement or batch, so we need to know what you are trying to do. Just saying "want locking" isn't helping. — Charlieface, Mar 20 '23 at 01:26
@Charlieface thank you, I updated the post with additional information. Dale K thanks for the link — pileup, Mar 20 '23 at 07:56
So how do the results from the above query affect what you are *actually* trying to do: the insert? If you just need to check whether those rows have been used, you can either use `UPDLOCK` or you can just do an `INSERT WHERE NOT EXISTS` and check the number of rows inserted afterwards (throwing an error if wrong) — Charlieface, Mar 20 '23 at 10:14
I should `UPDLOCK` the `Usage` table right? The issue is (I think) that if I `UPDLOCK` table `Usage` when many people are using the system, many will be getting errors about codes being in use because when the previous transaction is over, then it will attempt to insert the same codes. So I don't want to tell the users that the codes are in use, but try with new codes — pileup, Mar 20 '23 at 11:31
Like I said: **please tell us the ultimate logic you are trying to achieve** rather than talking about the way you think it should be implemented. Please explain the **full** steps you want to happen, including any errors or messages back to the user. — Charlieface, Mar 20 '23 at 12:04
@Charlieface I updated the post again with more information, please let me know if that's what you meant or you need more info — pileup, Mar 20 '23 at 12:20

Charlieface · Accepted Answer · 2023-03-21T21:05:32.847

2

This answer was substantially changed after further clarification.

Your insert query needs HOLDLOCK, UPDLOCK 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, UPDLOCK)
      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 HOLDLOCK locking hint, you can also set the isolation level for the whole statement. You still need the UPDLOCK though.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

edited Mar 21 '23 at 21:05

answered Mar 20 '23 at 13:10

Charlieface

52,284
6
19
43

1

No the code remains the same. You just need `HOLDLOCK` – Charlieface Mar 20 '23 at 15:50
thank you. Since I've never done such things I am not sure regarding the `@codes`. Is `@codes` referring to the Table Valued Parameter we created a step before? Then it means there is a typo and it should be `codes` instead of `code` when creating the Table Valued Parameter? – pileup Mar 20 '23 at 16:54
1

No not a typo. `CREATE TYPE` creates a table type, here called `CodeList` with one column `code`. Then when you pass a table valued parameter from your client application (C#, PHP, whatever?) you pass it as that table type, with the parameter name `@codes`. For example from C# you do it like this https://stackoverflow.com/a/66067309/14868997 – Charlieface Mar 20 '23 at 16:57
So this is what I don't understand: Why do I need the step of passing the selected user codes in a Table Valued Parameter? I already have what he wants from the frontend in the same request in an array. Something here confuses me, I must be missing something? – pileup Mar 20 '23 at 17:28
1

And how do you pass that array? In a table valued parameter. SQL Server does not have an array type. – Charlieface Mar 20 '23 at 17:31
The first query gives us an array of codes to insert after we get them according to the user's selection, so can I just use `where in` in the second query with the array of codes? – pileup Mar 20 '23 at 17:42
1

No because the results may have changed by the time the user runs the insert. Also the user may select only a few out of the results. You cannot just run the same query again. – Charlieface Mar 20 '23 at 17:45
But it all happens at the same time: The user selects the type and amount of each code, he isn't getting back a list of codes after that nor can he change his selection. So let's say the user chose to get 3 codes of `type_1` and 4 codes of `type_2`, I immediately at the same time need to fetch available codes, and insert them into the `Usage` table and only if it was successful I return the codes to the user. His selection was already made by that time – pileup Mar 20 '23 at 17:50
I was just thinking that if many users use the app then even though it takes split of a second, it still does it separately (the 2 queries) and I still need to lock it. But it all happens at the same request - the user selects amount and type of codes and either get everything he requested or the request fails because the codes are in use or there aren't enough codes by that split time we did the first query and second query. But maybe I'm being too paranoid and all I need is a lock on the `Usage` table? – pileup Mar 20 '23 at 18:26
1

This is why I asked you for what you are trying to achieve. This is quite different from what I assumed. Your actual insert is still unclear then: why do you need to select the `type` column? Why the `ORDER BY`? See also latest edit – Charlieface Mar 20 '23 at 21:20
Sorry about the confusion, in reality they wanted to save both code and type in the usage table, maybe easier for them to analyze data with simpler queries. I should've omitted this part in this question and only select the codes for clarification. Order by was simply for me to see the result more organized. So after a user choose what he wants, I have to check for available codes, insert to `usage` table if not exist and return codes to user if successful – pileup Mar 20 '23 at 23:02
Since I've never dealt with locks or lock hints. What will happen if for example 50 users try to get codes at the same time? The transaction locks the table. Will they get a time out error? Or these things take really short time that it can wait for all of them? – pileup Mar 20 '23 at 23:03
1

Assuming you have an index `Usage (code)` it will lock only those rows it finds. Rows that don't exist get a range lock from one row to the next. So it won't lock the whole table but might prevent others from doing this at the same time. Are all fifty really going to be doing this the same instant, that's nor usual? A handful of rows is usually quick, so unlikely to timeout, but deadlocks are still possible. Don't worry til you have a problem. – Charlieface Mar 20 '23 at 23:21
Thank you. And after I read this post: https://stackoverflow.com/questions/7843733/confused-about-updlock-holdlock, if I understand correctly, you put the HOLDLOCK on `usage` to prevent inserts into `usage`. But it doesn't block the selects themselves. So can't there be a situation where it selects the not exists part right before the insert, so that it makes the next insert valid? Even though by that time the code it selected was inserted and exists? – pileup Mar 21 '23 at 20:33
1

Think you might be right and an `UPDLOCK` might be needed also. – Charlieface Mar 21 '23 at 21:04
Thank you, I think I'll put both updlock and holdlock in two places - the select and the join. Maybe I'll ask another similar question however a more clear one? Very short explanation with shorter story. I think I have a simpler and better description – pileup Mar 21 '23 at 22:07

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

1 Answers1

Linked