2
  1. Before I write an update statement, should I check if it exists?

    Someone told me to do this

    1. So it avoids uselessly writing to the transaction log for value that already exists,

    2. And only takes a Shared lock allowing other reads versus an Exclusive lock.
      They said imagine a transaction running like this multiple times for different values, you would be taking exclusive locks all the time rather than shared locks.

    3. Coworker mentioned, looking at lock compatibility chart, there cannot be two update locks when searching, however there can be two shared locks. So when "searching for rows to update" when they're no values to update, a fake update lock can block a true update. Can someone invalidate this claim?

      if not exists 
      (
          select FavoriteColor 
          from dbo.Person 
          where Name = 'Bob' 
          and FavoriteColor = 'Green'
      )
      update dbo.Person
      set FavoriteColor = 'Green'
      where Name = 'Bob'
      
  2. Same question, except for delete now, should I check if it exists?

    if exists 
    (
        select FavoriteColor 
        from dbo.Person 
        where Name = 'Bob' 
        and FavoriteColor = 'Green'
    )
    delete dbo.Person
    where Name = 'Bob' and FavoriteColor = 'Green'
    

We use SQL Server 2016.

2
  • It just depends on a number of factors. Is the table very large? Running a yes/no operation is much easier than attempting locks and stuff.
    – clifton_h
    Sep 30, 2017 at 22:23
  • it has around 200 million records, 50Gb table
    – user129291
    Sep 30, 2017 at 22:29

1 Answer 1

7

Generally, this pattern is more efficient and far less likely to lead to deadlocks or other concurrency issues:

UPDATE dbo.Person
  SET FavoriteColor = 'Green'
  WHERE Name = 'Bob'
  AND COALESCE(FavoriteColor, '') <> 'Green';

DELETE dbo.Person
  WHERE Name = 'Bob' 
  AND FavoriteColor = 'Green';

...simply because you only have to check for the row once. You might also write the UPDATE as:

UPDATE dbo.Person
  SET FavoriteColor = 'Green'
  WHERE Name = 'Bob'
  AND (FavoriteColor IS NULL OR FavoriteColor <> 'Green');

...which may be more index-friendly in some cases.

Exclusive locks for write operations are not taken until just before a row to modify is actually updated. While SQL Server is searching for rows to update, it uses update locks, which do not conflict with concurrent reads. The update lock is released immediately if SQL Server determines that the row being checked does not qualify for the update.

The only reason I can think of using the if exists method is if there are UPDATE/DELETE triggers in the table that you want to avoid being fired, especially if you have INSTEAD OF triggers which can take some action before any update or delete is actually attempted.

It is normally best to write DML such that only rows that require a change are affected.

3
  • think a reason coworker mentioned, looking at lock compatibility chart, there cannot be two update locks when searching, however there can be two shared locks. So when "searching for rows to update" when they're no values to update, a fake update lock can block a true update. aboutsqlserver.com/2011/04/14/…, can someone invalidate this claim,, place in answer above? Thank you
    – user129291
    Oct 3, 2017 at 16:41
  • @Forestlake45 You want us to prove a negative? Wouldn't it be easier to get your co-worker to prove that that does happen? Also, from a comment on the link you referenced: "(S) lock is compatible with (U) lock – so session that reads (SELECT) the data could read the row with (U) lock acquired. It would not be able to read the row in case if (U) lock has been converted to (X) lock (e.g. data has been modified)." Oct 3, 2017 at 22:34
  • I recently tested this very thing with 50 concurrent threads hitting the same table for 1000 inserts/updates each and found no performance or stability hit from removing the EXISTS check, but I would imagine the structure of your table and the nature of the updates would be a factor and you should test against your own db as ymmv. Oct 6, 2017 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.