4

I know the default TRANSACTION ISOLATION LEVEL in SQL Server is "read committed". If I want to change it to "READ UNCOMMITTED", how may i make this configuration change?

note: I cannot use SET TRANSACTION ISOLATION LEVEL, which only apply in the current session. I cannot add NOLOCK in the queries because there are thousands of queries involved.

Thanks

Thanks for your answer. We are ok with reading dirty rows. Update is not a problem in our case as well. but, I really want to change this default config of isolation level. Please kindly help.

I cannot "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED". I must make the global change.

We have carefully reviewed both snapshot isolation level. They cannot be used in our situation.

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
Rodger Shao
  • 49
  • 1
  • 1
  • 2
  • 2
    I am very curious as to what specific problem you are running into where read uncommitted is though to be your only solution? I am assuming some kind of lock contention, but there are many ways to resolve such problems. – Roatin Marth Nov 04 '09 at 00:29
  • I came across this looking to know if it is possible, because I am wondering if somebody may have done this to us. Does anybody know? – Joshua Jan 13 '11 at 17:39

3 Answers3

4

I really do not think you should set that at a global level. You should be setting this pretty carefully because you can end up with a lot of different problems:

  • Lost Updates
  • Non repeatable reads
  • Dirty reads
  • Phantom reads

There is no way to set this at a database or a server level - it can only be set at a connection level.

The best you can do at a database level is to set the ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT properties. Read more here:

http://msdn.microsoft.com/en-us/library/tcbchxcb%28VS.80%29.aspx

Raj More
  • 47,048
  • 33
  • 131
  • 198
0

What about:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

It will only apply to the current transaction.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • That will apply to not only the current transaction, but the duration of the connection. See http://msdn.microsoft.com/en-us/library/ms173763(v=SQL.90).aspx for reference. – Scott Markwell Oct 20 '10 at 20:58
0

For SQL Server, you can only set transaction isolation level on session(connection) level but not on global level different from MySQL. So, every time you log out SQL Server, transaction isolation level is reset to READ COMMITTED which is default transaction isolation level.

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129