@scottpletcher  I stand corrected, and you're right about the Read Committed....However, I still can't understand how the update is locking on key values specified that don't exist.  That...

• This reply was modified 4 years ago by  rwest 44523.
• This reply was modified 4 years ago by  rwest 44523.

@ScottPetcher

This is a READ COMMITTED transaction.  The lock should only be held for the duration of the statement, not the entire transaction.  So that doesn't seem to answer why the...

Again, how exactly do you lock a record that doesn't exist?  And even if it was possible, why would SQL Server throw a key lock out there for record it...

• This reply was modified 4 years ago by  rwest 44523.

You should see it different, because that row in the update DOESN'T EXIST.  How can there be a lock on a non-existent row?  On an insert, the row exists....big difference.

So you're ok with this breaking every rule of ACID in which SQL Server says a record doesn't exist but won't let another transaction insert it?

That was done on purpose to show that right after the update to the record that doesn't exist, the open transaction has a key lock on a record that doesn't...

The best of all worlds:

use [msdb]

Declare @weekDay Table
(
  mask int
, maskValue varchar(32)
);

Insert Into @weekDay
  Select...