Have you identified which table is being locked and which processes are attempting to lock the same table?

If SQL Server is locking one of the tables in your query (not tempdb), that means that it is performing a table-scan. Try tuning the indexes to avoid the table-scan.
Also, you can put locking hints inside the query. To start, try WITH (NOLOCK) to see if the query completes. If these are OLTP tables, you cannot use WITH (NOLOCK) as a permanent solution. But it will get you pointed in the right direction on how to prevent unneccessary locks.

>Hi,
>
>Actually, I have increase my tempdb size to 100MB, even delete it
>and restart my server, but my query still lock my other table.
>How do I solve the problem as sometime the query work, sometimes
>it don't, we are trying to change the SP way of coding too.
>Thank
>
>>>Hi,
>>>
>>>Thank for your advise.
>>>Please kindly explain why the tempdb is giving the problem ?
>>
>>V,
>>
>>Your query contains two derived tables that are identical. SQL Server will choose an eager spool for one copy of the derived table so that both joins can pull from it in the query plan. The spooled table is stored in tempdb.
>>
>>Also, the intermediate results from aggregation functions are stored in tempdb.