Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    8

    Unanswered: help for "lock sleep"

    My database contains server "lock sleep" for over a day and I look for sp_who and sysprocesses and found out that come from cron job dump database script. I would like to know if "lock sleep" an O.K for database server? Does the blocking spid display under sp_who blk column? How to identify the blocking need to be kill? should I kill the process in database level with "kill spid" or kill in unix level "kill pid"? Any other system tables I can get the information for current locking status?

    Any solutions will be appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Try to shutdown and restart your backup server

  3. #3
    Join Date
    Feb 2004
    Posts
    8
    Thanks for the solution.

    Actually I would like to know what is "lock sleep" and why it happens? What is the steps to identify which lock is affetced the server and how to clean it?

  4. #4
    Join Date
    Mar 2009
    Location
    Malolos City, Philippines
    Posts
    2

    Registered User

    please tell about the root of lock sleep, cause it hangs the process.
    the only solution i know is to kill the process of specific spid that block the process of another spid which is indicated at blk_spid
    Last edited by joegarochoa; 05-28-09 at 01:23.

  5. #5
    Join Date
    May 2008
    Location
    Cambridge
    Posts
    26
    From Q10.1.1: Sybase Technical News - Volume 3, Number 2
    lock sleep - The process is waiting for a lock to be granted.

    Killing the pid at the Unix level should result in the ASE spid dying (tho' not always). In your case, at the Unix level, the pid is most likely for a sybmultbuf process; very occasionally these do hang.

    > Any other system tables I can get the information for current locking
    > status?

    The standard Sybase sp_lock will give you detailed lock information.

    If you've got the MDA (monitoring) tables installed then try the following SP's for helping to analyse blocking:
    sp__mon_sql2 (run sp__mon_sql2 1 to trace the OS process and other info)
    sp__block

    Hope that helps.

    Bob
    Last edited by harq; 06-02-09 at 18:58.
    Bob Holmes
    Sybase ASE/Replication Server Administrator
    Digital Data Safe - managed database services
    Email: bob.holmes@ddsafe.co.uk

Posting Permissions

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