How may I change the default TRANSACTION ISOLATION LEVEL in SQL Server 2005?

Question

Hello All,

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 GLOBAL 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

Answer 1

What about:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

It will only apply to the current transaction.

Answer 2

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

Answer 3

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.

Answer 4

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