Post reply

process is blocked by the same process id

  • August 31, 2007 at 8:48 am

    #118541

    Hi Experts,

     

    i am working on sql server 2005 database. one problem i am facing is that one process is blocked by same  process id?

    the application is calling some stored procedures and these procedures are creating some temporary tables (#temp),and inserting data from the base tables and updating these temp tables and at the end select some records from this temp tables and displaying back to the user.
     
    today we got so many blockings so we did restart the sql server and locks have been removed. now its running without any problem if at all this occurs again what remedies i should take to prevent the same.
     
    and what could be the problems that are causing these kind of problems?

  • August 31, 2007 at 9:13 am

    #730813

    it could be parallel processing.

    Paul

  • August 31, 2007 at 11:39 am

    #730848

    Hi Paul,

    i did not get you. could you please clarify it. how do i eliminate this problem.as far as my knowledge parallel processing is helps to run the query faster. but it is taking long time to execute it.

     

  • August 31, 2007 at 1:16 pm

    #730867

    Hi Pavan,

    Next time if it happens, find what causes the blocking, this way you may modify your script to avoid this situation. Here's a quite simple script for this task, you can enhance it:

    SELECT 
   rsc_dbid, -- database id
rsc_objid, -- object_id
rsc_type -- resource type, read BOL for more info
FROM master.sys.syslockinfo 
WHERE req_spid = <SPID>   
AND req_status = 3 -- waiting for a lock, that is, blocked

    Given the result set, you can find that which object is your process waiting for. Use db_name() and object_name() functions. If there's anything you don't understand, just read about syslockinfo in BOL, and if it remains unclear, put the question.

    Have fun!

    -- Erik http://blog.rollback.hu

  • September 1, 2007 at 2:49 am

    #730898

    Are you using any user defined types, xml or CLR types in the temp tables you are creating? SQL Server SP1 has introduced something what I call self-deadlocking transactions, but I've only seen these with the above datatypes. (examples on http://www.simple-talk.com/community/blogs/andras/archive/2006/06/09/859.aspx)

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • September 1, 2007 at 12:21 pm

    #730913

    Any of your processes or stored procedures using Cursors, While loops, or other RBAR?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 4, 2007 at 3:33 am

    #731138

    These are self-blockings and will release on its own quite after some time. I'm not sure if this still happen in sql2k5 but these are behaviour of SP4 on SQL 2000.

    Murali.a

  • September 4, 2007 at 3:58 am

    #731140

    Are u experiencing self blocking with system stored procedures.  Or custom stored procedures.  Sorry if the question is repeated again

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply