Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We're using SQL Server 2000.

I'm wondering what the risks are if we use a NOLOCK hint within a READ COMMITTED transaction block, for example:

IF EXISTS(SELECT * FROM clients (NOLOCK) WHERE clientID = 23249)
        PRINT 'TODO Update client'
        PRINT 'TODO Insert client'


My concern is that the NOLOCK hint will allow a ditry read, despite the isolation level of the transaction block.

Is this possible?

PS: I know there is a debate to be had about why I am using a locking hint the in first place, but I would just like to know what the risks are in this scenario.

share|improve this question
Looks like you are doing an upsert. Even at readcommitted your code has a race condition. – Martin Smith Dec 8 '11 at 11:10
@Martin: I don't think I can do an upsert in SQL Server 2000. Can you explain the race condition though? I'm getting more and more worried :( – Ciaran Archer Dec 8 '11 at 11:57
UPSERT is just the name of the pattern. 2008 has MERGE that helps here. See this SO question for some answers that should work fine on 2000 – Martin Smith Dec 8 '11 at 12:00
See this for ideas too: – gbn Dec 8 '11 at 15:20
up vote 5 down vote accepted

The hint overrides the isolation level of the connection so you would indeed be allowing a dirty read from the clients table.

share|improve this answer
Eeek, was afraid of this. – Ciaran Archer Dec 8 '11 at 11:58

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.