Linq 2 SQL - 经验心得-处理并发机制 (二)

本文链接：https://blog.csdn.net/niuniu23_1982/article/details/6875706

本文介绍了在项目中使用Linq2SQL进行级联更新时，如何处理乐观并发（不使用Timestamp和使用Timestamp）及悲观并发机制。详细探讨了在没有Timestamp列时的变更冲突，设置了Timestamp列后的更新行为，以及直接Attach对象时的限制条件。同时，提到了使用Transaction实现悲观并发的可能性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

首先要说明的是，在项目中，我们依赖于Linq2SQL做级联更新，级联更新是我们在业务层自己控制。

LINQ2SQL的Optimistic Concurrent（乐观并发）机制一，没有使用Timestamp(version列)

先看一段代码

private static void OptimisticConcurrentInColumn()
        {
            Product prod;
            using (NutShellDataContext db = new NutShellDataContext())
            {
                db.Log = Console.Out;
                var query = from p in db.Products
                            where p.ID == 1
                            select p;

                prod = query.First() as Product;
                prod.Description = "Widget";
                prod.Discontinued = true;

                var queryCust = from c in db.Customers
                                where c.ID == 1
                                select c;

                db.SubmitChanges();

                Console.WriteLine("Update product Successfully");

            }
            Console.ReadLine();
        }

在Product表中，没有version number的字段，也就是没有Timestamp. 当默认通过vs.net中的工具生成Product类之后，看看linq2sql是如何处理并发的。

UPDATE [dbo].[Product]
SET [Description] = @p3
WHERE ([ID] = @p0) AND ([Description] = @p1) AND ([Discontinued] = 1) AND ([LastSale] = @p2)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input VarChar (Size = 6; Prec = 0; Scale = 0) [Widget]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2007-1-1 0:00:00]
-- @p3: Input VarChar (Size = 7; Prec = 0; Scale = 0) [Widgets]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

生成的SQL比较有意思，所有列都在where条件中，这个就是LINQ2SQL在没有timestamp时用来做乐观竞争的机制。当一条记录如果在当前用户修改之前，别人已经修改了，LINQ2SQL会阻止当前用户的修改。

at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) Exception : System.Data.Linq.ChangeConflictException {"Row not found or changed."} is thrown.

这个竞争机制的设置，就在生成的实体，默认情况下，在Column中UpdateCheck属性是没有设置的，但是默认值为UpdateCheck=UpdateCheck.Always

如果要去掉这个竞争机制，可以改为UpdateCheck=UpdateCheck.Never

[Column(Storage = "_ID", DbType = "Int NOT NULL", IsPrimaryKey = true, UpdateCheck=UpdateCheck.Always)]
        public int ID
        {
            get
            {
                return this._ID;
            }
            set
            {
                if ((this._ID != value))
                {
                    this.OnIDChanging(value);
                    this.SendPropertyChanging();
                    this._ID = value;
                    this.SendPropertyChanged("ID");
                    this.OnIDChanged();
                }
            }
        }

LINQ2SQL的Optimistic Concurrent（乐观并发）机制二，使用Timestamp(version列)

这次我们在Customer表中使用version列，我们看看生成的SQL的区别

看段代码

private static void OptimisticConcurrentWithTimeStamp()
        {
            System.Data.Linq.Binary timestamp;
            
            using (NutShellDataContext db = new NutShellDataContext())
            {
                StringBuilder log = new StringBuilder();
                System.IO.StringWriter writer = new System.IO.StringWriter(log);
                db.Log = writer;

                var queryCust = from c in db.Customers
                                where c.ID == 1
                                select c;
                Customer customer = queryCust.FirstOrDefault();
                customer.Name = "Tom1";
                timestamp = customer.Timestamp;
                db.SubmitChanges();

                string results = log.ToString();
                Console.WriteLine("Update customer Successfully");

            }
            Console.ReadLine();
        }

看看SQL

SELECT TOP (1) [t0].[ID], [t0].[Name], [t0].[Timestamp]
FROM [dbo].[Customer] AS [t0]
WHERE [t0].[ID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

UPDATE [dbo].[Customer]
SET [Name] = @p2
WHERE ([ID] = @p0) AND ([Timestamp] = @p1)

SELECT [t1].[Timestamp]
FROM [dbo].[Customer] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[ID] = @p3)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input Timestamp (Size = 8; Prec = 0; Scale = 0) [SqlBinary(8)]
-- @p2: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Tom1]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

在update中，只有timestamp和主键被involve进了where条件中。只要有了timestamp列，自动生成的类中所有列都是UpdateCheck=UpdateCheck.Never，即使设为UpdateCheck=UpdateCheck.Always也不起作用，因为没有意义。

LINQ2SQL中的更新问题

上面那个例子中，我先取出一个customer,然后更新某一个字段，LINQ2SQL能智能判断当前对象中，哪个属性被修改了，然后生成SQL，而不是把所有列都更新了。

那如果我不是先取出对象，直接把一个对象Attach到LINQ2SQL的上下文中，会出现什么情况。

注意：想要直接Attach对象必需满足，要么有version列timestamp,要么没有UpdateCheck policy.否则会有Exception抛出

{"An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy."}

看段代码

private static void UpdateWithoutOriginalState()
        {
            using (NutShellDataContext db = new NutShellDataContext())
            {

                StringBuilder log = new StringBuilder();
                System.IO.StringWriter writer = new System.IO.StringWriter(log);
                db.Log = writer; ;
                Product prodModified = new Product();
                prodModified.ID = 1;
                prodModified.Description = "Widget";
                prodModified.LastSale = DateTime.Parse("2007-01-01 00:00:00.000");

                //If you want to attach enetity without original state, two options
                //1. Add version number
                //2. Remove update check policy, the default check policy is UpdateCheck.Always
                Product originalState = db.Products.GetOriginalEntityState(prodModified);
                if (originalState == null)
                    db.Products.Attach(prodModified, true);
                db.SubmitChanges();

                Console.WriteLine("Update product Successfully");

注意在Attach前，一般会检查一下到底有没有OriginalState,如果有的情况下，还Attach会出现Exception.

看看生成的SQL

UPDATE [dbo].[Product]
SET [Description] = @p1, [Discontinued] = @p2, [LastSale] = @p3
WHERE [ID] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input VarChar (Size = 6; Prec = 0; Scale = 0) [Widget]
-- @p2: Input Bit (Size = 0; Prec = 0; Scale = 0) [False]
-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2007-1-1 0:00:00]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1

有没有发现，所以字段都是会在更新的列表中。所以如果没有OriginalState,如果你对某个属性没有赋值就提交了，就会以NULL形式提交到数据库。

Pessimism concurrent 悲观并发机制

当然能使用悲观的并发机制，也就是使用transaction. 看段代码

private static void TransactionCase()
        {
            Purchase purchase;
            try
            {
                using (TransactionScope transaction = new TransactionScope(TransactionScopeOption.Required))
                {
                    using (NutShellDataContext db = new NutShellDataContext())
                    {

                        db.Log = Console.Out;

                        var queryItem = from pi in db.PurchaseItems
                                        where pi.ID == 1
                                        select pi;

                        PurchaseItem item = queryItem.FirstOrDefault();
                        PurchaseItem itemDuplicated = new PurchaseItem();
                        itemDuplicated.ID = item.ID;
                        itemDuplicated.Price = item.Price;
                        itemDuplicated.Detail = item.Detail;
                        itemDuplicated.PurchaseID = item.PurchaseID;

                        db.PurchaseItems.DeleteOnSubmit(item);
                        db.SubmitChanges();
                        Console.WriteLine("Press any key to continue...");
                        Console.ReadLine();

                        purchase = new Purchase();
                        purchase.ID = itemDuplicated.PurchaseID;
                        purchase.Price = 2100;
                        purchase.Date = Convert.ToDateTime("2006-01-02");
                        purchase.CustomerID = 1;
                        purchase.Description = "Holiday";

                        db.Purchases.Attach(purchase, true);
                        db.PurchaseItems.InsertOnSubmit(itemDuplicated);
                        db.SubmitChanges();
                    }

                    transaction.Complete();
                }
            }
            catch
            {
                Console.WriteLine("Exception is occured");
                Console.ReadLine();
            }

            Console.WriteLine("Attach Successfully");
            Console.ReadLine();
        }

使用了transaction scope来包装在事务中，其实在db.SubmitChanges()；linq2sql也会启动一个connection级别的事务，如果当前操作不在事务的环境中。