How do I set the primary key when I insert a record in SQL server?

If there is no particular inconvenience, it is recommended that the identity attribute be added to the PK column.

The exception to the problem is a deadlock detected and is occurring correctly.
So what you need is an implementation that will retry the action if this exception occurs.

First of all, SqlException.Number of the error is 1205, so we will catch it under the following conditions:

try
{
    // transaction processing
}
catch(SqlException ex)
{
    if (ex.Number==1205)
    {
        // retry processing
    }
    else
    {
        throw;
    }
}

The retry method may depend on the requirements, but the simplest method is through the for loop.

 // Number of retries
constint RETRY=5;
for (vari=0;i<RETRY;i++)
{
    try
    {
        // transaction processing

        break;
    }
    catch(SqlException ex)
    {
        if (ex.Number==1205&i<RETRY-1)
        {
            continue;
        }
        throw;
    }
}

It has nothing to do with the question, but SELECT has occurred in the following parts, so you should rewrite it to SQL such as SELECT COALESCE(MAX(id), 0)FROM....

int max=0;
try
{
    max=adapter.GetData().Max(x=>x.id);
}
catch (Exception e)
{
    max = 0;
}

Describes the concept of IsolationLevel.Serializable.

max=adapter.GetData().Max(x=>x.id);

You get the maximum value in , but of course you will refer to all the records.The transaction retrieves the shared lock for all referenced records.After that,

adapter.InsertQuery(max+1, "hello world.");

INSERT runs after retrieving the exclusive lock

Now, shared and exclusive locks have the following properties:

• New shared lock allowed during shared lock in another session
• No new exclusive lock during shared lock in another session, waiting to be released
• No new shared lock during exclusive lock in another session, waiting to be released
• No new exclusive lock during exclusive lock in another session, waiting to be released

As you can see, locking is only a waiting list, not a deadlock in itself.However, if you examine the behavior of the code in question based on the nature of this lock, you will see that the following conditions exist:

Multiple sessions run max=adapter.GetData().Max(x=>x.id), each holding a shared lock, and each holding a shared lock after Thread.Sleep(10000) and each holding an exclusive lock in adapter.InsertQuery(max+1, "hello world.").This is the deadlock.

Based on the above mechanisms of deadlock occurrence, we will consider countermeasures.

Reduce Consistency

Lowering the consistency level and using IsolationLevel.ReadCommitted will prevent you from continuing to maintain the shared lock.This allows for exclusive lock acquisition, eliminating deadlocks and allowing for the original primary key duplication error.If you encounter duplicate errors, try again.

See (SELECT) and insert (INSERT) simultaneously

Write SQL statements directly without using the TableAdapter feature.

Change the table design and automatically number it

Use the IDENTITY property.It is different from the sequence number proposed by Yukihane.