I made a procedure with a bunch of queries. One of them was:
insert into tableA
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?