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


    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?

  2. FrankKalis Moderator

    to quote BOL

    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
    Heute schon gebloggt?http://www.insidesql.de/blogs

Share This Page