SQL Server Performance

explain LCK_M_S

Discussion in 'T-SQL Performance Tuning for Developers' started by devteam, Feb 23, 2006.

  1. devteam New Member

    Hi,

    I am using sql server 2000. while running my application, some times the row is locked.
    When the row is locked, i have used dbcc opentran command in the query analyzer, the below shown reslt is displayed.

    Oldest active transaction:
    SPID (server process ID) : 64
    UID (user ID) : 1
    Name : implicit_transaction
    LSN : (1607:65:1)
    Start time : Feb 24 2006 12:24:06:560PM

    so can u tell me, 64 is the blocking process ?

    and i have used dbcc inputbuffer(64), it displays a query... is this query blocking the other processes?

    i have checked the lock type is LCK_M_S ... why this lock is occured? what are situations they are occured?

    how to prevent it?

    thanks
    devteam, Feb 23, 2006
    #2

  2. FrankKalis Moderator

    to quote BOL

    quote:
    Shared Locks
    Shared (S) locks allow concurrent transactions to read (SELECT) a resource. No other transactions can modify the data while shared (S) locks exist on the resource. Shared (S) locks on a resource are released as soon as the data has been read, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    FrankKalis, Feb 23, 2006
    #2

Share This Page