CODEWITHSUNDEEP
c#sql-serverentity-frameworkasp.net-coretransactions
Timur Lemeshko
Timur Lemeshko

Reputation: 2879

Increment value with transaction

I'm developing asp.net core application with EF and MsSql. I want to increment some field and be shure its incremented correctly.

For example: tread1 increment value from 10 by 2, thread2 increment same field by 1. Result must be 13. But in fact I get 11, couse thread2 get data before thread1 commited and thread2 commited after thread1.

Example code:

internal class Program
    {
        private static void Main(string[] args)
        {
            try
            {
                using (var context = new MyContext())
                {
                    context.Database.Migrate();
                    context.Books.Add(new Book {Amount = 10});
                    context.SaveChanges();
                }


                var context1 = new MyContext();
                var context2 = new MyContext();

                var transaction1 = context1.Database.BeginTransaction();
                var transaction2 = context2.Database.BeginTransaction();

                var book1 = context1.Books.Single();
                var book2 = context2.Books.Single();

                book1.Amount += 2;
                //some other changes with entities here, so i need transactions

                book2.Amount += 1;
                //some other changes with entities here, so i need transactions

                context1.SaveChanges();
                transaction1.Commit();

                context2.SaveChanges();
                transaction2.Commit();
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }

            var result = new MyContext().Books.Single().Amount;
            Console.WriteLine($"Result: {result}");
            Console.ReadLine();
        }
    }

    public class MyContext : DbContext
    {
        public virtual DbSet<Book> Books { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=localhost\\SQLEXPRESS;Database=CashboxDomain1;Trusted_Connection=True;ConnectRetryCount=0");
        }
    }

    public class Book
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Amount { get; set; }
    }

My application is mutltithread and i need transactions.

Is there any way to get wright increment?

Performance is important for me. Is there any faster method then optimistic concurrency + reload data on error?

Upvotes: 0

Views: 560

Answers (2)

Chris Pratt
Chris Pratt

Reputation: 239440

While you can utilize locks/semaphores, that's going to actually be worse performance-wise than optimistic concurrency. The only way to make the previous work is by essentially creating a choke point where only one update can proceed at a time. Using optimistic concurrency, you can maintain parallelism, and for the most part, there won't actually be any issues. On the what should be relatively rare occasions there is a concurrency conflict, the worst of it is that you might need to refresh from the database and submit the update again. Unless your database is severely under-optimized, neither of those operations should take any significant time, and the performance impact overall will be relatively minimal. While it's theoretically possible that multiple loops may be required to finally get the update in, in practice this is going to virtually never happen. If the system is performant enough, concurrency conflicts are pretty rare to begin with (just not rare enough to discount completely); the odds of a single request hitting a conflict multiple times in a row, is infinitesimally small.

Upvotes: 1

Bruno Farias
Bruno Farias

Reputation: 913

Check out this article here: https://www.bogotobogo.com/CSharp/csharp_multithreading3.php

If you make use of the lock keyword to handle the concurrency issue you are having. Share the lock object between the two threads and you will make sure one is executed after the other.

Upvotes: 1

Related Questions