Using (NOLOCK) in a READ COMMITTED transaction block

Question

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.

Answer 1

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