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.

Ciaran Archer
  • 395
  • 2
  • 3
  • 10

1 Answers1

5

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

Mark Storey-Smith
  • 31,860
  • 9
  • 90
  • 125