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

首先要说明的是,在项目中,我们依赖于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级别的事务,如果当前操作不在事务的环境中。



阅读更多
个人分类: 项目分享-Alliance
上一篇Linq 2 SQL - 经验之准备和工具 (一)
下一篇Linq 2 SQL - 经验心得-常见问题 (三)
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭