Template for updlock-holdlock

Question

question

GeorgeMenoutis-7690 asked Oct 15, '21 MelissaMa-msft commented Oct 25, '21

Template for updlock-holdlock

I made a procedure with a bunch of queries. One of them was:
insert into tableA
select ....
where not exists (select .... from tableA)

ie an "insert or ignore" statement.

Today I found in the logs a PK violation error. It didn't make sense that the query would do something like that, because of the not exists check as well as actually having a distinct in the data. Then I realized it was a concurrency issue - this procedure fired 30 times within the same second.

In such cases, I realize the go-to answer is to use (updlock,holdlock). What I want to ask is how to modify an entire procedure with only one high-concurrency-danger query. What I did is the following:

 create proc A as
 begin
 begin try
 begin transaction
 -- no isolation level commands = default = read committed
    
 some queries
    
 set transaction isolation level serializable -- more readable than holdlock
    
 --high concurrency query:
 insert into tableA
 select ....
 where not exists (select from tableA with (updlock))

 set transaction isolation level read committed -- back to normal
    
 other queries
    
 end of try-tran block

Is this a sensible design?

sql-server-transact-sql

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-10-15T06:46:59.377Z

MelissaMa-msft answered Oct 15, '21 MelissaMa-msft commented Oct 25, '21

Hi @GeorgeMenoutis-7690,

Welcome to Microsoft Q&A!

insert into tableA
select ....
where not exists (select .... from tableA)

Could you please double check above statement or provide the complete statement?

Per my understanding, you could include another table or data set to make this statement more logical.

In addition, you could use set transaction isolation level serializable and updlock in your statement if you face any concurrency issue.

You could also use WITH (UPDLOCK, SERIALIZABLE) instead.

Best regards,
Melissa

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GeorgeMenoutis-7690 · Oct 15, 2021 at 11:48 AM

Respectfully, that didn't help at all. The logic of the query is fine. The solutions you mention are already included in my solution - are you intending to agree?

0 ·

MelissaMa-msft GeorgeMenoutis-7690 · Oct 25, 2021 at 02:18 AM

Hi @GeorgeMenoutis-7690,

Thanks for your update.

In my opinion, it could be better to enhance your query like below:

 insert into tableA
 select ....
 from TableB
 where not exists (select .... from tableA)

Besides, yes, I agree with you about using set transaction isolation level serializable and updlock in your statement if you face any concurrency issue.

But you could have a performance test in a testing environment from your side and check whether any issue appears since it may cause deadlock as mentioned by Erland.

Best regards,
Melissa

0 ·

Answer 2 · 2021-10-15T21:48:15.643Z

ErlandSommarskog answered Oct 15, '21

I am not going say ay or nay. It looks like a candidate, but you will need to test. I can see two possible issues:

The throughput is hampered.
You get issue with deadlocks. (Serializable is prone to cause deadlocks.)

You will to conduct a stress test to see how it works out.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question