Before I write an update statement, should I check if it exists?
Someone told me to do this
So it avoids uselessly writing to the transaction log for value that already exists,
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.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'
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.