1

I have a large legacy database in which DTC is disabled and I need to enable it to use Transaction Isolation Level "READ UNCOMMITTED", but I'm afraid of the side effects of enabling it.

This is needed for a specific query and won't be used anywhere else.

Does anyone know if this could bring us any problems on the existing legacy systems?

Details:

I'm trying to use TransactionScope in my .NET application with isolation level "READ UNCOMMITTED" in order to not lock my query. This query searches a register that is locked in the database by a transaction of another application.

Maybe there is a way to get it working without changing the configuration of DTC. Any help will be appreciated!

Community
  • 1
  • 1
fabriciorissetto
  • 9,475
  • 5
  • 65
  • 73
  • I am no expert on DTC, but I don't think DTC is required simply for READ UNCOMMITTED directly through a single connection, SQL Server should support that transaction level natively without DTC. Are you querying a linked server? If are not opening multiple connections from the same client, and you are only querying one database within a transaction directly(not through linked server), then generally DTC is not required. What error are you getting? – AaronLS Sep 26 '15 at 02:08
  • Hi @AaronLS, when I enable the READ UNCOMMITTED transaction level I get the following error: *Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool* – fabriciorissetto Sep 26 '15 at 02:25
  • I'm not using a linked server and I am openning a new connection from another application – fabriciorissetto Sep 26 '15 at 02:27
  • 1
    Are you using TransactionScope in the application? You can specify the isolation level without it in order to avoid the MSDTC dependency. – Dan Guzman Sep 26 '15 at 02:34

1 Answers1

3

TransactionScope will automatically promote/enlist a transaction to a distributed transaction as needed, which requires MSDTC to be running and properly configured. To remove the dependency on MSDTC for isolated queries, try one of the following:

  • Use a SqlTransaction with System.Data.IsolationLevel.ReadUncommitted
  • Execute a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED query on the connection before running queries
  • Specify a NOLOCK locking hint in queries

Note that READ UNCOMMITED will result in dirty reads (including missed or duplicated data) so it should be used only in cases where concurrency benefits outweigh data integrity. An alternative is to turn on the READ_COMMITTED_SNAPSHOT database option so that row versioning instead of locking is used to provide integrity in the default READ COMMITTED isolation level. See Read committed Snapshot VS Snapshot Isolation Level.

Community
  • 1
  • 1
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • @AaronLS, I added clarification to my answer as I didn't intend to imply it is always a distributed transaction. My main point was advise against using TransactionScope solely for the purpose of the database transaction isolation level. – Dan Guzman Sep 26 '15 at 20:23
  • Thanks Dan! Only the two first steps were necessary – fabriciorissetto Sep 28 '15 at 16:08
  • @fabriciorissetto, sorry I wasn't clear but only one of those suggestions is needed. I edited my answer. – Dan Guzman Sep 29 '15 at 00:08