SQL Server Performance

Defining different types of transaction isolation level within SP

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Jan 11, 2010.

  1. atulgoswami New Member

    I just came across one stored procedure, where sequence of definfing the transaction isolation level is as follows
    1. Defined the transaction isolation level as "ReadUncommited"
    2. Then in one cursor operation, at starting within loop, it is defined as SNAPSHOT
    3 Then at the end within the loop inside cursor operation, it is defined as READCOMMITED"
    4. In cursor operation, it calls one Two stored procedures
    5. In nested stored procedure, again isolation level is set to SNAPSHOT at the beginning and at the end, defined as READ COMMITTED.

    This SP is used in one report and all transactions are performed on either temp table or table variable.

    I am just trying to understand, what could be reason to go for this kind of approach Or is there any scenario, where we have to opt for this kind of approach. Or it is just simply an overhead.
    Thanks in advance
  2. FrankKalis Moderator

    Seems like overhead. Setting the level at parent procedure to either UNCOMMITTED or SNAPSHOT and then in the same proc setting it back to READ COMMITTED should be sufficient.
  3. moh_hassan20 New Member

    from BOL:
    you can switch from one isolation level to another at any timeduring a transaction. The exception occurs when changing from anyisolation level to SNAPSHOT isolation. Doing this causes thetransaction to fail and roll back. However, you can change atransaction started in SNAPSHOT isolation to any other isolation level.
  4. atulgoswami New Member

    [quote user="moh_hassan20"]
    from BOL:
    you can switch from one isolation level to another at any time during a transaction. The exception occurs when changing from any isolation level to SNAPSHOT isolation. Doing this causes the transaction to fail and roll back. However, you can change a transaction started in SNAPSHOT isolation to any other isolation level.
    [/quote]
    Thanks
    But i guess this would be the case if you use BEGIN/COMMIT Transaction only and in my case, it wont affect. And it might be an overhead on the approach of switching to different transaction levels multiple times.
  5. moh_hassan20 New Member

    It may be that the designer of sp has his own reasons to switch bet. isolation level.
    I prefer , that every sp control its isolation level , not the parent sp
    As, you don't use BEGIN/COMMIT and you use that sp for reporting(report is valid at the time of printing) , so i prefer using Read Uncommited (or snapshot), and no need for switching bet. isolation levels[:)]

Share This Page