How to solve the concurrency conflict in ado.net

Question

Such a scenario: a certain amount of data to be inserted in a table,when reaches a threshold no longer insert, I simulated this scenario, in the case of multi-threaded (eg asp.net) appeared concurrent problems.

My question is how to solve of the concurrent problem, do not use the lock case

void Main()
{

   Enumerable.Range(0,20).ToList().ForEach(i=>{
       MockMulit();
   });

}
//Start a certain number of threads for concurrent simulation
void MockMulit()
{
  int threadCount=100;

  ClearData();//delete all data for test


  var tasks=new List<Task>(threadCount);
  Enumerable.Range(1,threadCount).ToList().ForEach(i=>{
     var j=i;
     tasks.Add(Task.Factory.StartNew(()=>T3(string.Format("Thread{0}-{1}",Thread.CurrentThread.ManagedThreadId,j))));
  });
  Task.WaitAll(tasks.ToArray());

  CountData().Dump();//show that the result
}

method one - concurrency very serious

void T1(string name)
{
        using(var conn=GetOpendConn())
        {
            var count=conn.Query<int>(@"select count(*)  from dbo.Down").Single();
            if(count<20)
            {
                conn.Execute(@"insert into dbo.Down (UserName) values (@UserName)",new{UserName=name});
            }
        }

}

method two - put the sql together can reduce concurrent, but still exist

void T2(string name)
{
   using(var conn=GetOpendConn())
   {
       conn.Execute(@"
                      if((select count(*)  from dbo.Down)<20)
                        begin
                          --WAITFOR DELAY '00:00:00.100';
                          insert into dbo.Down (UserName) values (@UserName)
                        end",new{UserName=name});
   }
}

method three - with lock destroy the concurrent,but i don't think it is a best solution

private static readonly object countLock=new object();
void T3(string name)
{
    lock(countLock)
    {
        using(var conn=GetOpendConn())
        {
            var count=conn.Query<int>(@"select count(*)  from dbo.Down").Single();
            if(count<20)
            conn.Execute(@"insert into dbo.Down (UserName) values (@UserName)",new{UserName=name});
        }
    }
}

other help method

//delete all data
void ClearData()
{
   using(var conn=GetOpendConn())
   {
       conn.Execute(@"delete from dbo.Down");
   }
}
//get count
int CountData()
{
   using(var conn=GetOpendConn())
   {
      return conn.Query<int>(@"select count(*)  from dbo.Down").Single();
   }
}
//get the opened connection
DbConnection GetOpendConn()
{
    var conn=new SqlConnection(@"Data Source=.;Integrated Security=SSPI;Initial Catalog=TestDemo;");
    if(conn.State!=ConnectionState.Open)
       conn.Open();
    return conn;
}

Answer 1

It sounds like you only want to insert into Down when there are less than 20 rows. If so: make that a single operation:

insert dbo.Down (UserName)
select @UserName
where (select count(1) from dbo.Down) < 20

select @@rowount -- 1 if we inserted, 0 otherwise

Alternatively, if you *need*you will need to use a transaction, ideally "Serializable", so that you get a key-range-lock - perhaps even adding (UPDLOCK) to the initial count, to ensure it takes an eager write lock (or blocks, rather than deadlocks). But: the single TSQL operation (as already illustrated is preferable. You could even make that more paranoid (although I'm not sure it needs it):

declare @count int

begin tran

insert dbo.Down (UserName)
select @UserName
where (select count(1) from dbo.Down (UPDLOCK)) < 20

set @count = @@rowount

commit tran

select @count -- 1 if we inserted, 0 otherwise