Entity Framework Core and transaction usage

I am using Entity Framework Core 3.1.8 with SQL Server 2016.

Consider following example (simplified for clarity):

Database table is defined as follows:

CREATE TABLE [dbo].[Product]
(
    [Id] INT IDENTITY(1,1) NOT NULL , 
    [ProductName] NVARCHAR(500) NOT NULL,
    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED (Id ASC) WITH (FILLFACTOR=80),
    CONSTRAINT [UQ_ProductName] UNIQUE NONCLUSTERED (ProductName ASC) WITH (FILLFACTOR=80) 
)

And following C# program:

using System;
using System.Linq;
using System.Reflection;

namespace CcTest
{
    class Program
    {
        static int Main(string[] args)
        {
            Product product =  null;

            string newProductName = "Basketball";

            using (CcTestContext context = new CcTestContext())
            using (var transaction = context.Database.BeginTransaction())
            {
                try
                {
                    product = context.Product.Where(p => p.ProductName == newProductName).SingleOrDefault();
                    if (product is null)
                    {
                        product = new Product { ProductName = newProductName };
                        context.Product.Add(product);
                        context.SaveChanges();
                        transaction.Commit();
                    }
                }
                catch(Exception ex)
                {
                    transaction.Rollback();
                }

            }

            if (product is null)
                return -1;
            else
                return product.Id;
        }
    }
}

Everything works as expected during testing - new product is inserted into the table if it didn't already exist. So I never expect [UQ_ProductName] constraint to be hit because everything is done as a single transaction.

However, in reality this code is a part of the business logic connected to Web API. What happened was that 10 instances of this code using the same new product name got executed almost simultaneously (execution time was the same within one hundredth of a second, we save it in the log table). One of them succeeded (new product name got inserted into the table) but the rest of them failed with following exception:

Violation of UNIQUE KEY constraint 'UQ_ProductName'. Cannot insert duplicate key in object 'dbo.Product'. The duplicate key value is (Basketball). The statement has been terminated.

Why did this happen? Isn't this exactly what my use of transaction was supposed to prevent? That is I think checking whether row with such value already exists and inserting if it doesn't should have been an atomic operation. After the first API call was executed and row was inserted the rest of API calls should have detected value already exists and not tried to insert a duplicate.

Can someone explain if there is an error in my implementation? Clearly it is not working the way I expect.

from Recent Questions - Stack Overflow https://ift.tt/3ijq6ds
https://ift.tt/eA8V8J