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?
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 isCreate procedure [dbo].[Select_BlockedNubmer]@id varchar(max)asbegin set rowcount 1 select * from BlockedNumber delete from BlockedNumber set rowcount 0endI 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)asbeginSET TRANSACTION ISOLATION LEVEL SERIALIZABLEbegin transactionset rowcount 1select * from BlockedNumberdelete from BlockedNumberset rowcount 0commit transactionend
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)asbegin set rowcount 1 select * from BlockedNumber delete from BlockedNumber set rowcount 0end
ALTER procedure [dbo].[Select_BlockedNubmer]@id varchar(max)asbeginSET TRANSACTION ISOLATION LEVEL SERIALIZABLEbegin transactionset rowcount 1select * from BlockedNumberdelete from BlockedNumberset rowcount 0commit transactionend