SQL Server – Using IF EXISTS Before UPDATE or DELETE

ddldeletesql serversql-server-2016update

  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.

Best Answer

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.

Related Question