CODEWITHSUNDEEP
.netsql-server
fabriciorissetto
fabriciorissetto

Reputation: 10063

Side effects of enabling DTC in SQL Server

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!

Upvotes: 1

Views: 1645

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

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.

Upvotes: 3

Related Questions