Take the 2-minute tour ×
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. It's 100% free, no registration required.

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:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
IF EXISTS(SELECT * FROM clients (NOLOCK) WHERE clientID = 23249)
    BEGIN
        PRINT 'TODO Update client'
    END
ELSE
    BEGIN
        PRINT 'TODO Insert client'
    END

COMMIT TRANSACTION

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: dba.stackexchange.com/q/8351/630 –  gbn Dec 8 '11 at 15:20
add comment

1 Answer

up vote 4 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
add comment

Your Answer

 
discard

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.