Geek asks and answers

SQL Server: how to get an exclusive lock to prevent race conditions?

I have the following T-SQL code:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION T1_Test

    /*This is a dummy table used for "locking" 
    and it doesn't contain any meaningful data.*/        
    UPDATE lockTable 
        SET ID = 1    
        WHERE ID = 1

    DECLARE @Count TINYINT 

    SELECT @Count = COUNT(*)
    FROM debugSP 

    WAITFOR DELAY '00:00:5';

    INSERT INTO debugSP 
        (DateCreated, ClientId, Result)
    SELECT 
        GETDATE(), @@SPID, @Count

COMMIT TRANSACTION T1_Test

I use the hack "lock" marked by a comment to get an exclusive lock.

NOTE. Using TABLOCKX or UPDLOCK hints will not work because I violated ATOMIC -ity by splitting statements and adding the WAITFOR command in the middle for testing purposes. I don't want anything like this:

INSERT INTO debugSP (DateCreated, ClientId, Result)
SELECT GETDATE(), @@SPID, COUNT(*) 
FROM debugSP

This is the correct result after two simultaneous sessions (with a lock table)

Id DateCreated           ClientId Result
-- ----------------------- -------- ------
 1 2011-03-17 15:52:12.287       66      0
 2 2011-03-17 15:52:24.534       68      1

and this is the wrong result of running code with comment blocking

Id DateCreated           ClientId Result
-- ----------------------- -------- ------
 1 2011-03-17 15:52:43.128       66      0
 2 2011-03-17 15:52:46.341       68      0

Is there a better way to get an exclusive transaction lock without such hacks?

+3
source share
2 answers

, . , ? sp_getapplock , , , .

+7

WITH WITH (XLOCK, ROWLOCK) . Serializable , , , ( , isn , ).

+1

More articles:


All Articles