1
Reply

Threading issues with SQL and C# where different threads get the same number

anbu selvan

anbu selvan

Aug 18 2010 9:37 AM
4.8k

My scenario:

                     n number of records in table, threads trying to access the table. One has to get first number and delete it, others have to get the second, third, etc., one by one.

                    But the problem is some of the threads get the same number. How do I avoid this?

My code

private void Form1_Load(object sender, EventArgs e)
{
   
for (int j = 1; j >= 10; j++)
   
{
       
Thread.Sleep(1000);
       
ThreadStart StarterCon = delegate { this.Start_new(sno); };
       
Thread th = new Thread(StarterCon);
        th
.Start();
   
}
}

private void Start_new(int h)
{
   
try
   
{
       
for (; ; )
       
{
           
using (SqlConnection ObjConn = new SqlConnection(ConnectionString))
           
{
               
ObjConn.Open();
               
using (SqlDataAdapter ObjAda = new SqlDataAdapter("Select_BlockedNubmer", ObjConn))
               
{
                   
ObjAda.SelectCommand.CommandType = CommandType.StoredProcedure;
                   
SqlParameter parm;

                    parm
= ObjAda.SelectCommand.Parameters.Add("@id", SqlDbType.NVarChar);
                    parm
.Value = h;
                   
using (DataTable dtTable = new DataTable())
                   
{
                       
ObjAda.Fill(dtTable);
                   
}
               
}

               
ObjConn.Close();
           
}
           
Thread.Sleep(500);
       
}
   
}
   
catch { }
}

my stored procedure is

Create procedure [dbo].[Select_BlockedNubmer]
@id varchar(max)
as
begin
   
set rowcount 1
   
select * from BlockedNumber
   
delete from BlockedNumber
   
set rowcount 0
end




I tried the following stored procedure. It works fine but reading number is very very slow what i do:

ALTER procedure [dbo].[Select_BlockedNubmer]
@id varchar(max)
as
begin
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin transaction
set rowcount 1
select * from BlockedNumber
delete from BlockedNumber
set rowcount 0
commit transaction
end



Answers (1)