Getting Started with Adobe After Effects - Part 6: Motion Blur
Ask
Ask questions, discuss or help others by answering
Related Posts · View All
SQL Server 146
TSQL 76
SSRS 70
SSIS 67
XML 57

Top Categories · View All
SQL Server 146
TSQL 76
SSRS 70
SSIS 67
XML 57

TSQL: Locking in delete statement

Oct 3 2011 2:51AM by Adam Tokarski   

Lets have a table with some columns (say col1, col2, col3, ...), then we need delete some records (as it's coming, for example, from clients application) in the way:

DELETE FROM table WHERE col1=100 AND col2 NOT IN (-1) AND col3 =200

It's wrote that way, because WHERE CLAUSE is concatenated in run mode.

So, problem is with transactions, when more than one person works with it. We got error 1205. And my question is: is that working that way, that the col2 is blocking whole table? When locking is made during delete? I was looking in execution plan, and it says, that there is seek predicate that Starts as [table].col2 > Scalar Operator((-1)) and ends as [table].col2 < Scalar Operator((-1)) .

Thanks in advance, Adam

Submitted under:  ·  ·  ·  · 


Adam Tokarski
58 · 3% · 1021

3 Replies

  • Hi Adam,

    Initially get the dead lock information like what object causes the deadlock and what type of lock it has.

    Have a look at below article.

    http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

    commented on Oct 10 2011 5:22AM
    Ramireddy
    2 · 40% · 12972
  • Hi Adam,

    sp_getapplock effectively serializes modifications, because only one connection can hold an exclusive application lock on the same resource. Other modifications to the same data will be forced to wait for that lock to be released, so there will be no collisions whatsoever.

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
    BEGIN TRANSACTION ; 
    DECLARE @ret INT ;
    SET @ret = NULL ;
    -- The @LockTimeout setting makes sp_getapplock
    -- wait for 10 seconds for other connections 
    -- to release the lock on col1 row value 100
    EXEC @ret = sp_getapplock @Resource = 'col1=100' ,
        @LockMode = 'Exclusive', @LockTimeout = 10000 ;
    -- sp_getapplock return code values are: 
    -- >= 0 (success), or < 0 (failure)
    IF @ret < 0 
        BEGIN ;
            RAISERROR('Failed to acquire lock', 16, 1) ;
            ROLLBACK ;
        END ;
    ELSE 
        BEGIN ;
            DELETE FROM table WHERE col1=100 AND col2 NOT IN (-1) AND col3 =200 ;
            IF @@ROWCOUNT = 0 
                BEGIN ;
                    RAISERROR('Record not found', 16, 1) ;
                END ;     
            COMMIT ;
        END ;
    
    commented on Oct 11 2011 1:22AM
    Robert Dennyson
    11 · 14% · 4430

Your Reply


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]