3

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.

5

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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