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 ?
Thanx ispaleny. I am using option 2. (create table, then insert). -Bheemsen
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:
Waittime > 300