Post reply

Locks on select statements why?

  • February 18, 2010 at 12:16 am

    #220713

    I am able to see lock on different tables during the execution of select statement. why?

    Select * from job where job_type=<type of job> and job_status='Ready

    And in below statment also we are able see dead locks on "Y" table

    update x set x.a=y.a,x.b=y.c,x.c=y.b

    from x join y on x.id=y.id

    where x.job_id=@injobid

    and x.description=@inCondetion1

    X table having 2,000,000 records

    Y having 180 records.

    As per the Microsoft SQL Server 2008 concern select statements and join tables(source tables) will not affect with any locks.

    But in my above two cases able to see row and page locks so, we are getting deadlocks.

    Kindly any one explain why locks are applying on source join tables.

    -KK

  • February 18, 2010 at 1:27 am

    #1120705

    koteswara kishore K. (2/18/2010)

    As per the Microsoft SQL Server 2008 concern select statements and join tables(source tables) will not affect with any locks.

    Locks get applied to any table that's read as part of a query.

    If you're having deadlock problems, switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

    DBCC TRACEON(1222,-1)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 18, 2010 at 1:33 am

    #1120710

    See if this helps

    http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

    Which is your isolation level?

  • February 18, 2010 at 1:58 am

    #1120720

    Read Committed Isolation Level

    --KK

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply