locked
Entity Frameowork Code First Concurreny Control RRS feed

  • Question

  • Hi, There

    I’m using the EF code first to persist the data by following the sequence: filter => remove => add, run the attached sample, sometime I noticed there was no existing record in the database, sometime there are two records in the database, what I thought/expected is - every time the filter should have one existing record return. Also, there are some exceptions raised up during saving the change.

    As I known, EF by default uses the Read Committed isolation level to execute the transaction, I think that means during the filtering, the shared lock is put on the resource, but why I can observe that there is not existing record or two existing records after filtering, the remove and add operation together should be an atomic operation, right? If I’m right, there should be only and just one record after filtering.

    Is there anything I missed? How to handle this case correctly?

    Please help.

    Script: ========

    CREATE TABLE [dbo].[Customers](
     [FirstName] [nvarchar](20) NOT NULL,
     [LastName] [nvarchar](60) NULL,
     [Company] [nvarchar](250) NULL,
     [Telephone] [nvarchar](20) NULL,
     [LastUpdated] [datetime] NULL
    ) ON [PRIMARY]

    Code ========

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Practices.Unity;
    using System.Transactions;
    using HibernatingRhinos.Profiler.Appender.EntityFramework;
    using System.Data.Entity.Validation;
    using System.Threading.Tasks;
    using System.Threading;
    using System.Data.Entity.ModelConfiguration;
    using System.Data.Entity;
    using System.Data.Entity.ModelConfiguration.Conventions;

    namespace EFOptimisticConcurrency
    {
        public abstract class Entity
        {
            public int Id { get; set; }
        }

        public class Customer
            : Entity
        {
            public string FirstName { get; set; }

            /// <summary>
            /// Get or set the surname of this customer
            /// </summary>
            public string LastName { get; set; }


            /// <summary>
            /// Get or set the telephone
            /// </summary>
            public string Telephone { get; set; }

            /// <summary>
            /// Get or set the company name
            /// </summary>
            public string Company { get; set; }

            public DateTime LastUpdated { get; set; }
        }

        class CustomerEntityConfiguration
            : EntityTypeConfiguration<Customer>
        {
            /// <summary>
            /// Create a new instance of customer entity configuration
            /// </summary>
            public CustomerEntityConfiguration()
            {
                //configure keys and properties
                this.HasKey(c => c.FirstName);

                this.Ignore(c => c.Id);

                this.Property(c => c.FirstName)
                    .HasMaxLength(20)
                    .IsRequired();

                this.Property(c => c.LastName)
                    .HasMaxLength(60)
                    .IsRequired();

                this.Property(c => c.Company)
                    .HasMaxLength(250);

                //this.Property(c => c.LastUpdated).IsConcurrencyToken();

                this.Property(c => c.Telephone)
                    .HasMaxLength(20);
                this.ToTable("Customers");
            }
        }

        public class CustomerContext : DbContext
        {
            public DbSet<Customer> Customers { get; set; }

            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
                modelBuilder.Configurations.Add(new CustomerEntityConfiguration()); ;
            }
        }

        public class Program
        {
            public static volatile int showStopper = 0;
            static void Main(string[] args)
            {
                var color = Console.ForegroundColor;
                EntityFrameworkProfiler.Initialize();
                Task.Factory.StartNew(() =>
                    {
                        while (true)
                        {
                            Customer customer = new Customer();
                            customer.FirstName = "FirstName";
                            customer.LastName = "Last " + new Random().Next(0, 10000).ToString();
                            customer.Telephone = "686868";
                            customer.Company = "MyCity";
                            customer.LastUpdated = DateTime.Now;

                            if (showStopper == 2)
                            {
                                Console.ReadLine();
                                showStopper = 0;
                            }
                            try
                            {
                                Console.WriteLine("Start the Store => " + customer.LastName + " , " + customer.LastUpdated.ToString());
                                {
                                    int i = 0;
                                    using (var customerConext = new CustomerContext())
                                    {
                                        Console.WriteLine("Start the filter 1 => " + customer.Telephone + " , " + customer.LastUpdated.ToString());
                                        var matched = customerConext.Customers.Where(c => c.Telephone == "686868" && c.LastUpdated < customer.LastUpdated);
                                        foreach (var hit in matched)
                                        {
                                            i++;
                                            customerConext.Customers.Remove(hit);
                                        }

                                        if (i == 2)
                                        {
                                            Console.WriteLine("1 - 2 exist, has the problem now");
                                            showStopper = 2;
                                        }
                                        else if (i == 0)
                                        {
                                            Console.WriteLine("1 - 0 exist, has the problem now");
                                            showStopper = 2;
                                        }

                                        Console.WriteLine("Start Adding 1 => " + customer.LastName + " , " + customer.LastUpdated.ToString());

                                        try
                                        {
                                            customerConext.Customers.Add(customer);
                                            customerConext.SaveChanges();        
                                            Console.WriteLine("SaveChanges 1 => " + customer.LastName + " , " + customer.LastUpdated.ToString());
                                        }
                                        catch (Exception ex)
                                        {
                                            Console.WriteLine("Exception 1 : " + ex.Message + " => " + customer.LastName + " , " + customer.LastUpdated);
                                            if (ex.InnerException != null)
                                            {
                                                Console.WriteLine("Inner Exception 2 : " + ex.InnerException.Message + " => " + customer.LastName + " , " + customer.LastUpdated);
                                            }
                                        }
                                    }

                                  
                                }
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine("Exception 1 " + ex.Message);
                                if (ex.InnerException != null)
                                {
                                    Console.WriteLine(ex.InnerException.Message);
                                }
                                showStopper = 2;
                            }
                        }
                    });


                Thread.Sleep(10000);

                Task.Factory.StartNew(() =>
                {
                    while (true)
                    {
                        Console.ForegroundColor = color;
                        try
                        {
                            Customer customer = new Customer();
                            customer.FirstName = "FirstName";
                            customer.LastName = "Last " + new Random().Next(0, 10000).ToString();
                            customer.Telephone = "686868";
                            customer.Company = "MyCity2";
                            customer.LastUpdated = DateTime.Now;

                            if (showStopper == 3)
                            {
                                Console.ReadLine();
                                showStopper = 0;
                            }

                            Console.WriteLine("Start the store 2 => " + customer.LastName + " , " + customer.LastUpdated.ToString());
                            {
                                int i = 0;
                                using (var customerConext = new CustomerContext())
                                {
                                    Console.WriteLine("Start the filter 2 => " + customer.Telephone + " , " + customer.LastUpdated.ToString());
                                    var matched = customerConext.Customers.Where(c => c.Telephone == "686868" && c.LastUpdated < customer.LastUpdated);
                                    foreach (var hit in matched)
                                    {
                                        i++;
                                        customerConext.Customers.Remove(hit);
                                    }

                                    Console.WriteLine("Start Adding 2 => " + customer.LastName + " , " + customer.LastUpdated.ToString());
                                    try
                                    {
                                        customerConext.Customers.Add(customer);
                                        customerConext.SaveChanges();
                                        Console.WriteLine("SaveChanges 2 => " + customer.LastName + " , " + customer.LastUpdated.ToString());
                                    }
                                    catch (Exception ex)
                                    {
                                        Console.WriteLine("Exception 2 : " + ex.Message + " => " + customer.LastName + " , " + customer.LastUpdated);
                                        if (ex.InnerException != null)
                                        {
                                            Console.WriteLine("Inner Exception 2 : " + ex.InnerException.Message + " => " + customer.LastName + " , " + customer.LastUpdated);
                                        }

                                        showStopper = 2;
                                    }
                                }

                                if (i == 2)
                                {
                                    Console.WriteLine("1 - 2 exist, has the problem now");
                                    showStopper = 2;

                                }
                                else if (i == 0)
                                {
                                    Console.WriteLine("1 - 0 exist, has the problem now");
                                    showStopper = 2;
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("Exception 2 " + ex.Message);
                            if (ex.InnerException != null)
                            {
                                Console.WriteLine(ex.InnerException.Message);
                            }  
                        }
                    }
                });


                Console.WriteLine("PRESS ANY KEY TO END");
                Console.ReadLine();
            }
        }
    }


    SAY NEVER


    • Edited by XianZhong Ni Sunday, November 11, 2012 5:56 AM EDIT
    Sunday, November 11, 2012 5:45 AM

All replies

  • Hi XianZhong,

    Welcome to the MSDN forum.

    I am trying to involve another expert into this thread. Please wait for the response.

    Sorry for any inconvenience.

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, November 13, 2012 9:10 AM
  • Alexander, thanks for the help.

    SAY NEVER

    Wednesday, November 14, 2012 2:10 AM
  • Hi,

    I don't have given this a close look but

    1) I would use SQL Server Profiler to see the actual statements running server side

    2) I suspect that :
    - the resolution of the client side datetime is AFAIK much higher than on the SQL Server Side (20 ns vs 0.3 ms) so you likely have some truncation or rounding when saved causing perhaps not retrieving the last row
    - using new Random().Next return only the first number of a random sequence and as the seed is time based by default, it's possible you'll get the same sequence and the same number if not enough time elapsed between two new Random() calls making IMO possible to insert duplicates

    Looking at the SQL trace should allow to confirm (or not this). IMO the issue is rather in the generated SQL statements rather than a problem with  atomicity...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".



    Wednesday, November 14, 2012 11:43 AM
  • Hi,

    I was only able to reproduce the problem once. Then, when I wanted to debug that further, it would no longer repro.

    Is this happening all the time for you?

    Thanks,

    Cathy Miller

    Thursday, November 15, 2012 9:18 PM
    Answerer
  • Hi,

    I was only able to reproduce the problem once. Then, when I wanted to debug that further, it would no longer repro.

    Is this happening all the time for you?

    Thanks,

    Cathy Miller

    Hi, Miller

    Thanks for the help.  

    I can reproduce it every time.

    Change the statement if (showStopper == 3)  to (showStopper == 2) may make it easriler for the reproducing.After the program stopped, i can see no/two records in the table:

    FirstName  LastName Company Telephone LastUpdated
    FirstName  Last 4241 MyCity2 686868 2012-11-17 10:33:54.567
    FirstName  Last 6193 MyCity 686868 2012-11-17 10:33:54.597


    From the EF profiler, I can catch the sql statement as belows, delete and insert operation is in one transaction scope.

    Again, thanks

    -- statement #1

    begin transaction with isolation level: ReadCommitted

    -- statement #2

    delete [dbo].[Customers]

    where  ([FirstName] = 'FirstName' /* @0 */)

    -- statement #3

    insert [dbo].[Customers]

           ([FirstName],

            [LastName],

            [Telephone],

            [Company],

            [LastUpdated])

    values ('FirstName' /* @0 */,

            'Last 7829' /* @1 */,

            '686868' /* @2 */,

            'MyCity' /* @3 */,

            '2012-11-17T10:33:54.00' /* @4 */)

    -- statement #4

    commit transaction



    SAY NEVER

    Saturday, November 17, 2012 2:43 AM
  • Hi,

    I don't have given this a close look but

    1) I would use SQL Server Profiler to see the actual statements running server side

    2) I suspect that :
    - the resolution of the client side datetime is AFAIK much higher than on the SQL Server Side (20 ns vs 0.3 ms) so you likely have some truncation or rounding when saved causing perhaps not retrieving the last row
    - using new Random().Next return only the first number of a random sequence and as the seed is time based by default, it's possible you'll get the same sequence and the same number if not enough time elapsed between two new Random() calls making IMO possible to insert duplicates

    Looking at the SQL trace should allow to confirm (or not this). IMO the issue is rather in the generated SQL statements rather than a problem with  atomicity...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".



    Hi, Scribe

    Thanks for the helps.

    Yes, I also suspected it’s the precision issue caused by the datetime, datetime may be not enough to be used the concurrency token, but here it has nothing to do with the random function.

    You’re right, I just cannot understand the things displayed, pre my understanding, the delete/insert should be in one transaction.



    SAY NEVER

    Saturday, November 17, 2012 2:55 AM
  • Hi,

    From a support perspective this is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.

    If Microsoft determines that a problem is the result of a defect in a Microsoft product, you will not be charged for that incident.

    Thanks,

    Cathy

    Monday, November 26, 2012 9:59 PM
    Answerer