Template for updlock-holdlock

Question

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?

Answer 1

Hi @George Menoutis ,

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.

Answer 2

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:

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

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