Transaction Isolation Level based on Login

  • Loundy

    SSCertifiable

    Points: 6080

    Hi All,

    Is it possible to set the transaction isolation level for a specific login? I.E. If a support team connects to a DB all queries they write use READ UNCOMMITTED?

    Cheers,

    [font="Times New Roman"]There's no kill switch on awesome![/font]
  • seth delconte

    SSCertifiable

    Points: 6388

    You could set up a login trigger to fire a proc on login that contained appropriate logic. However, any other procs that were executed by a login may have code in them that resets the isolation level...

    http://stackoverflow.com/questions/3685584/in-sql-server-2005-is-it-possible-to-set-transaction-isolation-level-on-a-per-u

    _________________________________
    seth delconte
    http://sqlkeys.com

  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    If this is to make it so that support staff don't block application usage I would recommend you start with education. Even without blocking "select * from dbo.reallybigtable" can cause performance issues. If you're in a situation where there are some queries that support needs to run that can't be run without significant blocking add statements to templates for that query that will handle it (I would use nolock).

    If modifying the isolation level upon login is what you need to do you may also want to consider source application as a method of identifying which sessions should have which isolation level. And you'll want to keep in mind whether or not the application does passthrough authentication so if support is using the app they're still using the proper isolation level.

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    I'll second, third, and fourth that...education, education, education!

    If that is a lost cause, I know it was in some shops where I have been, you might look into enabling READ_COMMITTED_SNAPSHOT isolation. Do your homework though and make sure your tempdb is setup correctly to handle it, and that you monitor the version store.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Loundy

    SSCertifiable

    Points: 6080

    Thanks for the responses guys, i agree with the education route - unfortunately our offshore support teams sometimes "forget" to stick to process :S

    [font="Times New Roman"]There's no kill switch on awesome![/font]
  • Jeffrey Williams

    SSC Guru

    Points: 88324

    Another option is to not allow them to write their own queries. Give them access to only execute stored procedures that you have vetted.

    If they need something else, then they would have to request a new procedure be built which would have the necessary protections built in.

    Or, you could provide a copy of the production system they can run their queries against. In most cases, they wouldn't need access to real-time data. If they need access to real-time data you could setup replication and grant them access to the replicated database.

    If they don't need real-time access you could use database snapshots (Enterprise Edition), mirroring with database snapshots, backup/restore to another server or SAN replication/mirroring/snapshots.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 6 posts - 1 through 6 (of 6 total)

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