Will an EXISTS query still lock a table?

Question

IF NOT EXISTS(SELECT * FROM MyTable WITH(nolock) WHERE Key = 'MyKey')
  INSERT MyTable(Key) Values('MyKey')

If The value does not exist in the table, does the query aquire a lock?

Answer 1

EXISTS normally will still acquire a lock. But you added a hint that told it not to, and so it won't.

Answer 2

From the docs:

READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock. For more information about lock behavior, see Lock Compatibility (Database Engine).

So it won't acquire a data lock, but it will still acquire a schema stability lock.

Answer 3

Using a NOLOCK hint will indeed prevent the row lock. Just a heads up though, this kind of 'lookup and insert' is riddled with problems. The operation is not atomic and two sessions trying to do it will cause a race condition when both find the key missing and both try to insert, resulting in one of them causing a duplicate key violation. Is it also suboptimal because the index seek occurs twice (once to lookup the key, once to locate the insert position). The optimal and correct solution is to actually try to insert and recover from the duplicate key error if already exists.

Answer 4

That code is vulnerable to error. Instead you could try:

Put a unique index on the table so that it's not possible to insert multiple rows that conflict, and then just insert. A conflict generates an error, which you'd need to handle.

Or, if conflicts are an expected condition and not the exception, then you'll want to make the insert/check atomic:

insert MyTable( [Key] ) 
  select 'MyKey' 
  where not exists ( 
    select * 
    from MyTable 
    where [Key] = 'MyKey' 
)

Also, note that (nolock) and Read Uncommitted do not produce accurate results, by design. It's OK for reporting and such, but dangerous to act on your data based on a decision that uses (nolock).