deadlock on tempdb..sysindexes

Greetings,
I have stored procedure dumping resultset into created temporary table.
Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
Problem is I that can not change stored procedure and not sure how to stop
locking sysindexes since stored proc will take between 5-15 seconds to run
depending on date range supplied.
I would appreciate any suggestions how to resolve this issue
PS: Code goes like this
create table #temp(...)
insert into #temp
execute sp_StoredProc
--
SaxonThe problem appears to be that the stored procedure is executing within a
transaction. This is unavoidable, since the INSERT...EXEC statement starts
a transaction prior to executing the procedure. To avoid the deadlocks, you
MUST alter the stored procedure. If you can't alter it, then make a copy
and alter that. Change the procedure so that it executes an INSERT
statement into the temp table. (If a temp table is created before executing
the stored procedure, it is available within the body of the stored
procedure.) This will eliminate the transaction
You should avoid creating, altering or deleting temporary objects within a
transaction. This includes both tables, indexes and constraints. You
should avoid executing procedures within a transaction. For this reason, I
generally avoid INSERT...EXEC.
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
> Greetings,
> I have stored procedure dumping resultset into created temporary table.
> Every 15-30 minutes we have deadlock and always on sysindexes in tempdb.
> Problem is I that can not change stored procedure and not sure how to stop
> locking sysindexes since stored proc will take between 5-15 seconds to run
> depending on date range supplied.
> I would appreciate any suggestions how to resolve this issue
> PS: Code goes like this
> create table #temp(...)
> insert into #temp
> execute sp_StoredProc
> --
> Saxon|||Thanks Brian,
so basically if I create temp table and call stored proc to insert into
table instead of using INSERT... EXEC it would not cause deadlock since no
transactions would be started.
PS: Why inserting into temp table would hold lock on sysindexes anyway? I
tried to find some info on that but no luck.
Regards
Saxon
"Brian Selzer" wrote:

> The problem appears to be that the stored procedure is executing within a
> transaction. This is unavoidable, since the INSERT...EXEC statement start
s
> a transaction prior to executing the procedure. To avoid the deadlocks, y
ou
> MUST alter the stored procedure. If you can't alter it, then make a copy
> and alter that. Change the procedure so that it executes an INSERT
> statement into the temp table. (If a temp table is created before executi
ng
> the stored procedure, it is available within the body of the stored
> procedure.) This will eliminate the transaction
> You should avoid creating, altering or deleting temporary objects within a
> transaction. This includes both tables, indexes and constraints. You
> should avoid executing procedures within a transaction. For this reason,
I
> generally avoid INSERT...EXEC.
>
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:0E06645B-1552-4B8A-BC79-5B888D9D9D7B@.microsoft.com...
>
>|||The lock isn't caused by inserting, it's caused by creating, altering, or
deleting a temporary object within the procedure! The problem is that
normally, when a procedure runs, any transactions must be explicitly started
within the body of the proc. INSERT...EXEC wraps the procedure call in a
transaction. There are several articles on MSDN about lock contention and
blocking--some cite concurrency issues with tempdb. (There are fixes for
that in SP4.)
"Saxon" <Saxon@.discussions.microsoft.com> wrote in message
news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
> Thanks Brian,
> so basically if I create temp table and call stored proc to insert into
> table instead of using INSERT... EXEC it would not cause deadlock since no
> transactions would be started.
> PS: Why inserting into temp table would hold lock on sysindexes anyway? I
> tried to find some info on that but no luck.
> Regards
> --
> Saxon
>
> "Brian Selzer" wrote:
>|||Thank you kindly Brian.
Much appreciated.
Regards
Saxon
"Brian Selzer" wrote:

> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||Thanks for this useful description of the problem.
If we creates at temporary table in the procedure and fill data into it with
a function, will that cause a transaction too?
create table #temp(...)
insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
"Brian Selzer" wrote:

> The lock isn't caused by inserting, it's caused by creating, altering, or
> deleting a temporary object within the procedure! The problem is that
> normally, when a procedure runs, any transactions must be explicitly start
ed
> within the body of the proc. INSERT...EXEC wraps the procedure call in a
> transaction. There are several articles on MSDN about lock contention and
> blocking--some cite concurrency issues with tempdb. (There are fixes for
> that in SP4.)
> "Saxon" <Saxon@.discussions.microsoft.com> wrote in message
> news:5E9210DE-4BA6-4376-AEFD-1E7A2B55A041@.microsoft.com...
>
>|||On Wed, 23 Nov 2005 03:36:11 -0800, winther wrote:

>Thanks for this useful description of the problem.
>If we creates at temporary table in the procedure and fill data into it wit
h
>a function, will that cause a transaction too?
>create table #temp(...)
>insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
Hi winther,
Yes. Every modification is automatically part of a transaction. If you
didn't start one explicitly, it will be started implicitly.
If SET IMPLICIT_TRANSACTION is OFF, the implicitly started transaction
will also be implicitly committed after each statement. With this
setting to ON, the server waits for an explicit COMMIT or ROLLBACK to
end the transaction.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||There will be a transaction, but it won't put a lock on sysindexes because
the create table occurs apart from the transaction started for the insert.
"winther" <winther@.discussions.microsoft.com> wrote in message
news:0A8FC1D4-1571-45B8-91A8-29656995C285@.microsoft.com...
> Thanks for this useful description of the problem.
> If we creates at temporary table in the procedure and fill data into it
> with
> a function, will that cause a transaction too?
> create table #temp(...)
> insert into #temp SELECT x, y FROM (udf_MyTableFunction1)
>
> "Brian Selzer" wrote:
>

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