When I got the deadlock scenario I took the snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.

rsc_text                        ...

I get deadlock in the same stored procedure but at different locations each time, In the following query I got the deadlock last time:

select  distinct site_id, site_name, lower(su_id) as su_id,

    into #temp_su

    from     site_unit_v2...

Thanks for your replies!!

I am looking at all the replies you gave above.

In the mean while

Can you provide me some links to know the features, architecture and working of...

The issues is there was no node 2 involved

Deadlock encountered .... Printing deadlock information

2007-04-10 20:01:13.28 spid4

2007-04-10 20:01:13.28 spid4 Wait-for graph

2007-04-10 20:01:13.28...

Thanks Grant,

This stored Procedure has many temporary tables. Is there anyway to know more information about the table handled by the page.

So that I can restrict my search accordingly.

I...

Thanks for your replies!!

I looked at the dbcc input buffer it is just telling that it is an RPC event:

RPC Event: gsa_proc_homepage_cache_refresh;1

Nothing else pretty useful.

I went through the deadlock scenario...