Threading issues with SQL and C# where different threads get the same number, but shouldn't

Question

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

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

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

Answer 1

You should use database transactions:

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

A transaction ensures that two threads cannot access the “intermediate” state of the data. Imagine you’re thread X: You pick a number. As soon as you do this, the number belongs to you and no other thread can read it. All other threads have to wait for you to commit the transaction before they can read theirs. This gives you a chance to remove the number before committing the transaction, so they won’t see it.

Answer 2

You are looping forever. Inside this loop, you repeatedly do some very expensive operations, that may result in too many connections or other odd stuff floating around.

My suggestion is: create only one SqlConnection object. Do not loop each 0.5 seconds, but instead either loop slower or react to an external trigger. You can also reuse your SqlDataAdapter object for better performance.

With regards to threads, either use lock or use other synchronisation primitives to prevent race conditions. While individual static methods on the used library methods are thread-safe, that doesn't mean that between calls to them, threads interfere with your data objects.

Update: edited, because at first, the threading code was obfuscated.