SQL Server distributed Transactions vs. local transactions

SQL Server distributed Transactions vs. local transactions

@ (SQL Server)

Background: There were a lot of deadlocks in the project before, and after the troubleshooting, many deadlocks were found because of 序列化隔离级别 the result, the deadlock was reduced after the development was optimized for business and SQL, but no follow-up research was done. Recently there are many projects deadlock and timeout, especially the work flow and to do this piece, the same discovery is the existence of serialization, so for this point to consult and answer the relevant information.

I. Why does it appear serializable (serialization)

If the transaction class is called in our program 分布式事务 , the isolation level of the transaction is the default serializable , and the session at the serialized level is present in the database.

If it is called 本地事务 , then the isolation level defaults to Read committed .

Two. The difference between a distributed transaction and a local transaction

本地事务: System.Data.Common.DbTransaction only local transaction operations can be made and multiple database connections cannot be established across instance operations.

分布式事务: System.Transactions.TransactionScope in the same transaction, multiple database connections can be established, cross-library or cross-instance operations, and the MSDTC service needs to be turned on.

There are several common types of distributed transactions 使用场景 :

1. There are multiple SqlConnection (multiple database connections) in TransactionScope that connect to the same instance of SQL Server不同数据库
2. Multiple SqlConnection (multiple database connections) connections in TransactionScope不同的SqlServer实例
3. SQL statements executed by SqlConnection (database connection) in TransactionScope are used in theLinkedServer（链接服务器）

See the usage scenarios of distributed transactions above, in addition to personalization in our application to use these scenarios, most of the other transactions do not need to invoke distributed transactions, so in general our database does not appear the serialization isolation level.

Three. 2 Types of transaction ISOLATION level differences

Read committedIn contrast to the Serializable default isolation level for local transactions and distributed transactions, the difference between the 2 isolation levels is that the range of locks is different during SQL execution.

Generally speaking, the serializable level, the scope of the lock is larger, so it will lead to more blocking, and thus the probability of deadlock becomes higher. However, the data consistency is the highest at the serializable level, which means that non-repeatable reading, phantom reading, etc. can be avoided.

For our application, the non-repeatable read or the effect of Phantom read almost no, so do not deliberately to avoid these problems, using the default read committed to prevent dirty read, so unless the scene conforms to the distributed transaction, the general use of local transactions can be.

Four. Summary recommendations

For the use of distributed transactions, it is possible that prior development did not notice its usage scenarios and transaction isolation levels, many transactions do not require distributed transactions, only local transactions can be satisfied, and those that need to be distributed in the use of the situation can be 修改其默认的隔离级别 因为序列化隔离级别的优势我们并不需要，相反其并发性差的弱势却是我们需要避免的 .

To modify the distributed transaction Isolation Level code:

TransactionOptions transactionOption = new TransactionOptions(); transactionOption.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; //设置隔离级别using (TransactionScope scope = new TransactionScope(transactionOption)) {}  //然后这儿加上transactionOption参数

To see if a serialization session exists in the database

select * from sys.dm_exec_sessions where transaction_isolation_level = 4

Appendix

Https://www.cnblogs.com/xcj26/archive/2013/12/23/3469373.html
Http://www.360doc.com/content/14/1016/10/19715875_417357952.shtml
https://technet.microsoft.com/zh-cn/library/ms177403 (v=sql.105). aspx
https://www.bbsmax.com/A/x9J2llEd6o/
https://www.bbsmax.com/A/RnJWrAPrzq/
Https://msdn.microsoft.com/zh-cn/library/system.transactions.transactionscope

SQL Server distributed Transactions vs. local transactions