Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    1

    Question Unanswered: What might explain unreleased row locks?

    Problem:
    exclusive row (Ex_row) locks still lingering around after one would expect them to be released.

    Detail:
    We've configured select tables on an ASE 12.0 dataserver to use the datarow locking scheme. Our intent was to reduce contention on these high transaction tables. We've gone through the recommended checks and locking guidelines in the Performance and Tuning Guide and appeared to be on the right track, i.e. we no longer have blocking locks and perceived performance is better.

    The problem is that sometimes, after a heavy multi-threaded insert/update test on the tables in question, sp_lock shows many locks of type Ex_row still unreleased. This is still the case after the application driver has returned, seemingly with no errors. Note that this observation can only be made sometimes. The usual observation is that the lock count grows and shrinks as the driver application runs. When the driver program completes no locks are outstanding. We've investigated the uncommitted transaction theory and feel confident that this is not the case.

    We could really use some help coming up with other theories to explore.

    Thanks.

    p.s. see version number below and attached example of sp_lock results
    Adaptive Server Enterprise/12.0.0.2/P/SWR 9307 ROLLUP/Sun_svr4/OS 5.6/1660/32bit/FBO/Mon Dec 18 19:54:56 2000
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2002
    Location
    Argentina
    Posts
    78

    Cool

    In the TXT file, I can see two proccess running in trading DB.
    I think the problem here is that there is a transaction open or may be there is a deadlock.
    You can check that with this select:

    -- OPEN TRANSACTION
    SELECT substring(db_name(master..syslogshold.dbid),1,15) db_name,
    *,
    datediff(second, starttime, getdate()) segundos
    FROM master..syslogshold

    use trading
    go

    -- USERS IN DB ( if you don't like sp_who and need extra info)
    select substring(db_name(dbid),1,15) db_name,
    substring(suser_name(suid),1,15) login_name,
    substring(user_name(uid),1,15) user_name,
    substring(user_name(gid),1,15) group_name,
    hostname,
    program_name,
    hostprocess,
    spid,
    status,
    cmd,
    physical_io,
    cpu,
    memusage,
    blocked,
    time_blocked,
    tran_name,
    priority
    from master..sysprocesses
    where dbid = db_id()
    and suid <> 0
    order by physical_io desc,
    cpu desc,
    memusage desc


    You can execute sp_who, and try to kill some users to see what happen.
    Good Luck!!

    Sebastian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •