"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:
>
>> 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:
>> >
>> >> The 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
>> >>
>> >>
>> >>
>>
>>
>>