SQL Server Performance

Stored Procedure Deadlocking

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by kdefilip, Sep 7, 2012.

  1. kdefilip Member

    Hi all
    I have a stored procedure which when executed is deadlocking. The stored procured has set:
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    It creates several "table varialable" temp tables and one local temp table.
    It also has several begin/end blocks within the SP.
    In addition it calls another SP which executes some code and functions as well which also has the same ISOLATION level set in that sp.

    My question is, theis ISOLATION level is set per the current connection, but what is the effect of the several begin/end blocks on the isolation level and further, the effect on the ISOLATION level of the called sp with the additional functions; is that ISOLATION level ever broken or reset due to these situations.

Share This Page