Post reply

Setting transaction isolation level inside SP

  • February 21, 2003 at 3:30 am

    #105441

    Setting transaction isolation level inside stored procedure to READUNCOMMITED:

    I have stored procedure which does lot of processing in transaction. It uses lot of tables & UDF's in it. Most queries are of select type. There are 3 tables which are heavily used inside SP, records are deleted, inserted and selected in a range of 300-500 records from these tables. My code in SP is something like this -

    BEGIN TRANSACTION

    SELECT OfficeId

    FROMUnitConfiguration

    .....

    .....

    COMMIT TRANSACTION

    I am sure that my two different transactions are never going to update or delete data that conflicts with other transaction. I want a way to instruct SQL 2000 lock manager that do not use locking mechanism for this entire transaction. In other words when I run this SP sql should not show any locked resources for this ProcessId under Enterprise manager Management -> Current activity -> lock/process Id.

    Thanks,

  • February 24, 2003 at 8:00 am

    This was removed by the editor as SPAM

  • February 25, 2003 at 3:46 pm

    #449752

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    This is the least restrictive available. You may see locks still in Enterprise manager Management -> Current activity -> lock/process Id but that is too do with the locking mechanism and doesn't mean you are having locking issues. (Its a bit much to try and explain here in a few sentences so i won't confuse you by attempting)

    Edited by - nmoore on 02/25/2003 3:47:03 PM

    Nigel Moore
    ======================

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply