Home > ASE > Need to understand concepts about locking and blocking.

Need to understand concepts about locking and blocking.

Hi sybanva,
Could you please provide some insight on the locking and blocking i.e.
1. How to figure out the SQL text of spid which comes as an output of “sp_who”
2. How to check which table is locked and by which sp id
3. Suppose my procedure takes 2 min. everyday and suddenly one day it took 2 hrs. to complete. So what could have been occoured, how to check whether table used by procedure were locked or not, if yes then which spid was responsible for table locking and etc etc.

Many Thanks

Check the Sybase Wiki @ sybasewiki.com
Categories: ASE Tags:
  1. June 7th, 2011 at 18:32 | #1

    Hi All,

    Please find the response as below :

    first enable the trace flag 3604 , then take the output with sqltext as
    dbcc traceon(3604)
    dbcc sqltext(spid)

    you need to query for table object id in syslocks table for the locking information

    If you know the spid you can get lock details with
    sp_lock ‘spid’

    First you need to identify ,is there any blocking in the system, if there is no blocking you need to see the showplan, if its not using proper index you need to check about the how soon stats are updated on table which are used by stored procs.

    Please let me know if you have any more questions.

  1. No trackbacks yet.