Strange SaveChanges behavior in Entity Framework and SQL Server

Question

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.

Answer 1

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)