Dblocks with temporary table

Hi,

I am using temp table in my stored procedure. The temp table is first created and rows are inserted. Then I am selecting data from same temp table. Everything is inside the stored procedure. This stored procedure is called from a Java (EJB) program with at least 100 threads at the same time.

But when the Java program runs and calls this stored procedure, it is resulting in many dblocks. Can any one explain why this is happening ? I appreciate any help on this.

Does each thread create its own copy of temp table ?
Is temdb locked in this process ?
Do I need to drop the tamp table at the end ? ( I am not dropping now)
What are the other alternatives ?

I am on SQL Server 7, windows 2000/NT.

Thanx..
-BheemsenThe temp table is first created and rows are inserted...

Which approach do you use?

1. select into
2. create table + insert

Select into locks system tables in MSSQL7.|||Thanx ispaleny. I am using the 2 option.
i.e. create table, then insert, then select.

-Bheemsen|||I use MSSQL2k.

#tables are created unique for each thread
#tables created in scope of SP exist only in scope of SP|||RE:
Thanx ispaleny. I am using option 2. (create table, then insert). -Bheemsen

Question I
Since it isn't a simple select into locking issue, what are the server's wait states like when the issue presents itself? Also, what kinds of locks are being issued and in what proportion, and is the Java app possibly spawning multiple connections (rather than reusing when possible) and / or not closing out connections when done with them?

wait states data gathering example:

Select
Spid,
Waittime,
Lastwaittype,
Waitresource
From
Master..Sysprocesses
Where
Waittime > 300