Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do transactional settings affect SQL Server?

MST->DTC->SQL SERVER

How do transactional settings affect SQL Server?

by  TomSark  Posted    (Edited  )
MTS Default SQL Server
Transaction SQL Server Enlisted In Transaction
Setting Isolation Level DTC Type
---------------------------------------------------------------------------------------------
Requires New Serializable Yes Explicit
Requires Serializable Yes Explicit
Supports (1) Serializable Yes Explicit
Supports (2) Read Committed No Autocommit
Does Not Support Read Committed No Autocommit

(1) If the object is involved in a transaction
(2) If the object is not involved in a transaction

Why did I put this together?
For components involved in transactions, MTS by default uses the serializable isolation level on SQL Server, which could cause a concurrency problems such as blocked processes and deadlocks. If not absolutely necessary to use the serializable isolation level, developers should set the SQL Server isolation level to read committed so that they can obtain higher concurrency (fewer blocked transactions, fewer deadlocks).
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top