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.
upsert
. Even atreadcommitted
your code has a race condition.upsert
in SQL Server 2000. Can you explain the race condition though? I'm getting more and more worried :(UPSERT
is just the name of the pattern. 2008 hasMERGE
that helps here. See this SO question for some answers that should work fine on 2000