Post reply

How to set transaction isolation for the database?

  • March 4, 2009 at 1:16 pm

    #131492

    Can anyone tell me how to set the transaction isolation level for the database? I want a whole database to operate at the SERIALIZABLE level.

    I know I can set it per-connection with

    SET TRANSACTION ISOLATION LEVEL = 'SERIALIZABLE';

    but in this case I'd like any transaction inside this database to operate at the SERIALIZABLE level. This particular database is high-risk (i.e. financial data) but the load is generally quite light - end-users could easily wait a few extra seconds (more likely, fractions of a second) for their data in order to guarantee transaction serialization.

    Is there anyway to set this option by default for any connection that touches this data? I'd vastly prefer not to have to set it for every connection made to this DB. I've googled and read BOL and nothing I've found says how to set this beyond an individual connection.

    At worst I'd be happy to set this as the default for the whole instance and not just this database.

    Thanks!

  • March 4, 2009 at 1:41 pm

    #953566

    You cannot force an isolation level (except when you put a filegroup or db in read only mode).

    Putting everything is serializable isolation level will raise the risk of lock timeouts and deadlocks!

    If you still would want everything in a particular isolation level, encapsulate it in a stored procedure ! and process everithing withing (or starting from ) that proc.

    You could then set the isolation level at the start of that sproc to the one you want it to use.

    create proc usp_myproc

    as

    begin

    set nocount on

    set transaction isolation level ....

    .... put the rest of your code here ...

    end

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • March 4, 2009 at 1:55 pm

    #953582

    Yeah, setting the isolation level in each proc is what I've been doing. I was hoping there was a better way.

    Oh well, thanks for the confirmation.

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

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