Tuesday, March 20, 2012

Database Engine default Isolation Level

Is there a way to change the Database Engine Default Isolation level?
The default is: Read committed (Database Engine default level)
I would like to move it to: Read uncommitted (the lowest level where
transactions are isolated only enough to ensure that physically corrupt data
is not read)
I would like to be able to change it on a database level...or a server
level.
ThanksThat sounds like a very bad idea to me. Why would you want to do that?
And with Read Uncommitted you can definitely get "logically" corrupt
data, you can get incomplete data, you can get an error (when pages are
moved during the dirty query access). I think you are seriously
underestimating the possible side effects of dirty reads. If you are not
using a read-only database, then I would not recommend it.
If you are on SQL Server 2005, BOL mentions this:
In SQL Server 2005, you can also minimize locking contention while
protecting transactions
from dirty reads of uncommitted data modifications using either:
* The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT
database option set to ON.
* The SNAPSHOT isolation level.
Maybe you should take a look at that.
--
Gert-Jan
SQL Server MVP
Sintel Silverblade wrote:
> Is there a way to change the Database Engine Default Isolation level?
> The default is: Read committed (Database Engine default level)
> I would like to move it to: Read uncommitted (the lowest level where
> transactions are isolated only enough to ensure that physically corrupt data
> is not read)
> I would like to be able to change it on a database level...or a server
> level.
> Thanks

No comments:

Post a Comment