process is blocked by the same process id

Question

Post reply

process is blocked by the same process id

pavan27

SSC Veteran

Points: 208
More actions
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?

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

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

paul_richardson SSCarpal Tunnel Points: 4594 More actions · Answer 1

paul_richardson

SSCarpal Tunnel

Points: 4594

August 31, 2007 at 9:13 am

#730813

it could be parallel processing.

Paul

pavan27 SSC Veteran Points: 208 More actions · Answer 2

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.

Erik Bitemo SSCrazy Points: 2062 More actions · Answer 3

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

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)

Murali-420858 Ten Centuries Points: 1076 More actions · Answer 6

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

Krishna Mohan Kasi Mr or Mrs. 500 Points: 593 More actions · Answer 7

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