LINQ與TRANSACION問題

LINQ to SQL之DataContext於SubmitChanges函式執行時，就算不指定Transaction，DataContext都會自動啟動一個Transaction，在進行大量insert時一定會有鎖住的問題，造成效能的問題。

以下幾種方法可以解決這個問題:

» 覆載SubmitChanges函式，讓Transaction消失

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Text;
using System.Reflection;
 
namespace ConsoleApplication35
{
    class Program
    {
        static void Main(string[] args)
        {
            NorthwindDataContext context = new NorthwindDataContext();            
            var item = (from s1 in context.Customers where s1.CustomerID == "VINET" 
                        select s1).FirstOrDefault();
            if (item != null)
                item.ContactName = "VINET14";
            Console.ReadLine();
            context.DisableTransaction = true;
            context.SubmitChanges();
            Console.ReadLine();
        }
    }
 
    partial class NorthwindDataContext
    {
        public bool DisableTransaction { get; set; }
 
        private static MethodInfo _checkDispose = null;
        private static MethodInfo _checkNotInSubmitChanges = null;        
        private static MethodInfo _verifyTrackingEnabled = null;
        private static MethodInfo _acceptChanges = null;
        private static MethodInfo _submitChanges = null;
        private static FieldInfo _conflicts = null;
        private static FieldInfo _isInSubmitChanges = null;
        private static PropertyInfo _services = null;
        private static Type _changeProcessorType = null;
        private static ConstructorInfo _ci = null;
 
        static NorthwindDataContext()
        {
            _checkDispose = typeof(DataContext).GetMethod("CheckDispose", 
                         BindingFlags.NonPublic | BindingFlags.Instance);
            _checkNotInSubmitChanges = 
               typeof(DataContext).GetMethod("CheckNotInSubmitChanges", 
                      BindingFlags.NonPublic | BindingFlags.Instance);
            _verifyTrackingEnabled = typeof(DataContext).GetMethod("VerifyTrackingEnabled",
                                      BindingFlags.NonPublic | BindingFlags.Instance);
            _acceptChanges = typeof(DataContext).GetMethod("AcceptChanges", 
                             BindingFlags.NonPublic | BindingFlags.Instance);
            _conflicts = typeof(DataContext).GetField("conflicts", 
                              BindingFlags.NonPublic | BindingFlags.Instance);
            _isInSubmitChanges = typeof(DataContext).GetField("isInSubmitChanges", 
                                 BindingFlags.NonPublic | BindingFlags.Instance);
            _changeProcessorType = typeof(DataContext).Assembly.GetType(
                             "System.Data.Linq.ChangeProcessor");
            _services = typeof(DataContext).GetProperty("Services", 
                        BindingFlags.NonPublic | BindingFlags.Instance);
            _ci = _changeProcessorType.GetConstructor(
                       BindingFlags.NonPublic | BindingFlags.Instance, null,
                      new Type[]                      
            { typeof(DataContext).Assembly.GetType("System.Data.Linq.CommonDataServices"), 
              typeof(DataContext) }, null);
            _submitChanges = _changeProcessorType.GetMethod("SubmitChanges", 
                     BindingFlags.NonPublic | BindingFlags.Instance);
        }
 
        public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)        
        {
            if (DisableTransaction)
            {
                _checkDispose.Invoke(this, null);
                _checkNotInSubmitChanges.Invoke(this, null);
                _verifyTrackingEnabled.Invoke(this, null);
                ((ChangeConflictCollection)_conflicts.GetValue(this)).Clear();
                try
                {
                    _isInSubmitChanges.SetValue(this, true);
                    object processor = _ci.Invoke(new object[] 
                             { _services.GetValue(this, null), this });
                    _submitChanges.Invoke(processor, new object[] { failureMode });
                    _acceptChanges.Invoke(this, null);
                }
                finally
                {
                    _isInSubmitChanges.SetValue(this, false);
                }
            }
            else
                base.SubmitChanges(failureMode);
        }
    }
}

» 以TransactionScope 加上NOLOCK

ProductsNewViewData viewData = new ProductsNewViewData();

 using (var t = new TransactionScope(TransactionScopeOption.Required,

 new TransactionOptions { 

 IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted 

 }))

{

 viewData.Suppliers = northwind.Suppliers.ToList();

 viewData.Categories = northwind.Categories.ToList();

}

» 從LINQ呼叫Stored procedure, 在SP的SQL加上NOLOCK
» 設定DataContext level, 執行以下指令:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

參考:

http://blog.csdn.net/Code6421/archive/2008/05/22/2469460.aspx

http://www.infoq.com/news/2008/03/linq-nolock

http://www.infoq.com/news/2008/03/linq-nolock