Entity Framework和SQL Server中的奇怪SaveChanges行为 [英] Strange SaveChanges behavior in Entity Framework and SQL Server

问题描述

我有一些代码，您可以检查项目

代码(在此控制器中，我正在发送文件以上传):

  [HttpPost]公共ActionResult UploadFiles(HttpPostedFileBase []文件，int?folderid，字符串描述){foreach(文件中的HttpPostedFileBase文件){如果(文件！= null){字符串fileName = Path.GetFileNameWithoutExtension(file.FileName);字符串fileExt = Path.GetExtension(file.FileName)?. Remove(0，1);诠释?extensionid = GetExtensionId(fileExt);如果(CheckFileExist(fileName，fileExt，folderid)){fileName = fileName + $"；({DateTime.Now.ToString("dd-MM-yy HH:mm:ss")})"；}文件dbFile = new File();dbFile.folderid = folderid;dbFile.displayname = fileName;dbFile.file_extensionid = extensionid;dbFile.file_content = GetFileBytes(文件);dbFile.description =说明；db.Files.Add(dbFile);}}db.SaveChanges();返回RedirectToAction("Partial_UnknownErrorToast"，"Toast")；} 

我想在数据库中创建扩展名(如果尚不存在).然后用 GetExtensionId :

 专用静态对象储物柜= new object();私人诠释?GetExtensionId(字符串名称){诠释?结果= null;锁(储物柜){var extItem = db.FileExtensions.FirstOrDefault(m => m.displayname ==名称);如果(extItem！= null)返回extItem.file_extensionid;var fileExtension =新的FileExtension(){显示名称=名称};db.FileExtensions.Add(fileExtension);db.SaveChanges();结果= fileExtension.file_extensionid;}返回结果；} 

在SQL Server数据库中，我对FileExtension的displayname列具有唯一的约束.

问题只有在我上传了几个具有相同扩展名的文件并且该扩展名在数据库中尚不存在时才开始.

如果我删除了 lock ，则在 GetExtensionId 中将出现关于唯一约束的 Exception .

也许出于某种原因， foreach 循环的下一次迭代会调用 GetExtensionId 而不等待?我不知道.但是只有当我设置 lock 时，我的代码才能正常工作.

如果您知道为什么会发生，请解释.

解决方案

这听起来像是一个简单的并发竞争条件.想象一下，有两个请求同时出现.他们都检查 FirstOrDefault ，该代码正确地说不".对彼此而言.然后他们都尝试插入；一赢，一失败，因为数据库已更改.EF围绕 SaveChanges 管理事务时，该事务并非从您最初查询数据时开始

lock 似乎可以通过阻止它们同时进入看似的代码来起作用，但这通常不是一个可靠的解决方案，因为它仅在单个进程中起作用，更不用说节点了.

所以:这里有一些选择:

• 您的代码可以检测到外键冲突异常，并从头开始重新检查(FirstOrDefault等)，这使成功情况下的事情变得简单(这将是大部分时间)，而在失败情况下的开销却并不惊人(只是一个例外和一个额外的数据库命中)-足够实用
• 您可以移动选择(如果存在)，如果不存在则插入".到事务内的数据库 中的单个操作(理想情况下是可序列化的隔离级别，和/或使用 UPDLOCK 提示)-这需要您自己编写TSQL，而不是依赖EF，但最大程度地减少了往返行程，并避免了写入检测故障并进行补偿".代码
• 您可以通过EF在事务内执行选择和可能的插入操作 -坦白地说，这很麻烦且杂乱:不要这样做(它再次需要可序列化的隔离级别，但是现在可序列化的交易跨越多个往返，如果规模较大，可能会开始影响锁定)

I have some code, you can check project github, error contains in UploadContoller method GetExtensionId.

Database diagram:

Code (in this controller I sending files to upload):

    [HttpPost]
    public ActionResult UploadFiles(HttpPostedFileBase[] files, int? folderid, string description)
    {
        foreach (HttpPostedFileBase file in files)
        {
            if (file != null)
            {
                string fileName = Path.GetFileNameWithoutExtension(file.FileName);
                string fileExt = Path.GetExtension(file.FileName)?.Remove(0, 1);
                
                int? extensionid = GetExtensionId(fileExt);
                
                if (CheckFileExist(fileName, fileExt, folderid))
                {
                    fileName = fileName + $" ({DateTime.Now.ToString("dd-MM-yy HH:mm:ss")})";
                }

                File dbFile = new File();
                dbFile.folderid = folderid;
                dbFile.displayname = fileName;
                dbFile.file_extensionid = extensionid;
                dbFile.file_content = GetFileBytes(file);
                dbFile.description = description;

                db.Files.Add(dbFile);
            }
        }
        db.SaveChanges();
        return RedirectToAction("Partial_UnknownErrorToast", "Toast");
    }

I want to create Extension in database if it not exist yet. And I do it with GetExtensionId:

    private static object locker = new object();
    private int? GetExtensionId(string name)
    {
        int? result = null;
        lock (locker)
        {
            var extItem = db.FileExtensions.FirstOrDefault(m => m.displayname == name);

            if (extItem != null) return extItem.file_extensionid;

            var fileExtension = new FileExtension()
            {
                displayname = name
            };
            db.FileExtensions.Add(fileExtension);
            db.SaveChanges();
            result = fileExtension.file_extensionid;
        }
        return result;
    }

In the SQL Server database I have unique constraint on displayname column of FileExtension.

Problem starts only if I uploading few files with the same extension and this extension not exist in database yet.

If I remove lock, in GetExtensionId will be Exception about unique constraint.

Maybe, for some reason, next iteration of foreach cycle calls GetExtensionId without waiting? I don't know. But only if I set lock my code works fine.

If you know why it happens please explain.

解决方案

This sounds like a simple concurrency race condition. Imagine two requests come in at once; they both check the FirstOrDefault, which correctly says "nope" for both. Then they both try and insert; one wins, one fails because the DB has changed. While EF manages transactions around SaveChanges, that transaction doesn't start from when you query the data initially

The lock appears to work, by preventing them getting into the looking code at the same time, but this is not a reliable solution for this in general, as it only works inside a single process, let alone node.

So: a few option here:

• your code could detect the foreign key violation exception and recheck from the start (FirstOrDefault etc), which keeps things simple in the success case (which is going to be the majority of the time) and not horribly expensive in the failure case (just an exception and an extra DB hit) - pragmatic enough
• you could move the "select if exists, insert if it doesn't" into a single operation inside the database inside a transaction (ideally serializable isolation level, and/or using the UPDLOCK hint) - this requires writing TSQL yourself, rather than relying on EF, but minimises round trips and avoids writing "detect failure and compensate" code
• you could perform the selects and possible inserts inside a transaction via EF - complicated and messy, frankly: don't do this (and it would again need to be serializable isolation level, but now the serializable transaction spans multiple round trips, which can start to impact locking, if at scale)

这篇关于Entity Framework和SQL Server中的奇怪SaveChanges行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！