CODEWITHSUNDEEP
sqlsql-server-2008
P.Brian.Mackey
P.Brian.Mackey

Reputation: 44285

No lock runs of the same query

                if exists (select 1 from schema.TableName (nolock) where Id = @id) 
                update schema.TableName set DocumentXML = @documentXml, ClientDocumentGUID=@clientDocGuid, Incomplete = @incomplete where Id = @id
                else
                insert into schema.TableName
                select @id, @templateId, @clientVisitGuid, @clientGuid, @chartGuid, @scmDocumentGuid, @clientDocGuid, @incomplete, getdate(), @createdByGuid, @documentXml

I have a C# program that runs the query above. I have situations coming up where rows are being inserted twice. I think the issue is this query. The idea is that the query can be run twice given the same @Id. The first time should be an insert, the second time should be an update.

Note the query has a (no lock). Does this mean that the queries are not necessarily run in a FIFO fashion? I believe the two row problem can only arise if I run this query asynchronously.

Upvotes: 1

Views: 301

Answers (3)

Sebastian Meine
Sebastian Meine

Reputation: 11813

Using the NOLOCK hint causes SQL Server to ignore all locks. That means a query might end up reading values in the middle of them being changed, which causes utter garbage to be returned.

However, removing the NOLOCK hint is not enough. You need to make sure that all your statements are executed within a transaction either in the calling application or within a procedure. If you go with a procedure review this first: How to rollback in procedures

After you wrapped the statements in a transaction you also need to make sure that the IF EXISTS statement is taking an update or exclusive lock on the row to prevent someone else from inserting a row in between your statements, so you need an UPDLOCK or an XLOCK hint. As the row potentially does not exist yet, this is also only possible in the serializable transaction isolation level.

After you have implemented all this you still are wasting reads with this setup. That could potentially become a performance problem later.

--

Long story short: As JoshBerke suggested, you should really use the MERGE statement in this case. This will prevent your issues while being faster and easier to maintain. However, it requires 2008 or later. See http://msdn.microsoft.com/en-us/library/bb510625.aspx for details.

Upvotes: 0

JoshBerke
JoshBerke

Reputation: 67108

If you have two requests processing at the same time they will both insert since your ignoring all locks on the first table. I like using merge in these cases. Or remove your no lock and ensure your transaction isolation level is snapshot or serialized.

Upvotes: 1

Z .
Z .

Reputation: 12837

depending on the transaction isolation level queries could be running in parallel...

Upvotes: 0

Related Questions