linqtodb use connection.BeginTransactionAsync inside asp.net core

Question

I'm using linqtodb with great success inside a asp.net 6.0 api. But now i'm at a point where it looks like i need to use transactions and it looks like i'm misunderstanding a few things there. i'm getting the _connection object as an injected object in the service

the error i get:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action\u00601 wrapCloseInAction)\r\n   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action\u00601 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock
...
...
"message":"Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

the code in question is:

...
    await _connection.BeginTransactionAsync(System.Data.IsolationLevel.Serializable);
                    sql = "SELECT ISNULL(MAX(CAST(Code AS INT)),0) FROM [@COR_DIA_AKM_LOTAT]";
                        var maxCode = await _connection.ExecuteAsync<int>(sql) + 1;
                        string newCode = maxCode.ToString("00000000");
                        sql = "INSERT INTO [@COR_DIA_AKM_LOTAT] (Code, Name, U_DocEntry, U_LineNum, U_LotNum, U_LotQty, U_ItemCode, U_Typ, U_DeletedF, U_LotCode) VALUES (@newCode, @newCode, @docEntry, @lineNum, @lot, @qty, @itemCode, 'F', 'N', '')";
                    await Task.Delay(10000);
                    await _connection.ExecuteAsync(sql,
                            new DataParameter { Name = "@newCode", Value = newCode },
                            new DataParameter { Name = "@qty", Value = l.qty },
                            new DataParameter { Name = "@docEntry", Value = updatePos.docEntry },
                            new DataParameter { Name = "@lineNum", Value = updatePos.lineNum },
                            new DataParameter { Name = "@itemCode", Value = updatePos.itemCode },
                            new DataParameter { Name = "@lot", Value = l.lot }
                            );
                    await _connection.CommitTransactionAsync();
...

so as you maybe can see there needs to be created an incremental alphanumeric id first (structure is given, i cannot change that) and then i will use it in an insert.

So i need to make sure that concurrent usage of the above part will wait for eachother to finish

The await Task.Delay(...) is just there so i can Test the concurrent usage

When i now execute this code from 2 separate clients the second call from the 2nd client fails with the above message

things i've considered but are not applicable:

• use a stored procedure
• use one sql statement and get the new id as a result from a subquery
• use a mutex lock in the application (bad idea anyway i guess)

what i expect from the code:

• the 2nd client waits until it can get the lock. this waiting will be done automagically from within my await ...BeginTransaction() right?

Here the model of the table in question:

    [Table(Schema = "dbo", Name = "@COR_DIA_AKM_LOTAT")]
    public partial class @COR_DIA_AKM_LOTAT : IUDT
    {
        [PrimaryKey, NotNull] public string Code { get; set; }
        [Column, NotNull] public string Name { get; set; }

        [Column, NotNull] public int U_DocEntry { get; set; }

        [Column, NotNull] public int U_LineNum { get; set; }
        [Column] public decimal U_LotQty { get; set; }


        [Column] public string U_DeletedF { get; set; }
        [Column] public string U_LotNum { get; set; }

        [Column] public decimal U_PkgMandatoryQty { get; set; }
    }

any enlightment is highly appreciated

Update

My misunderstanding was that i was thinking

...BeginTransaction(..)

Will already lock the table somehow. But now after some more reading and pointers from @SvyatoslavDanyliv i've found out that locking is happening on query level. So to get the behaviour i want i need to:

// read committed is fine because i just would like to lock -> execute queries -> commit 
 using var trans = await _connection.BeginTransactionAsync(System.Data.IsolationLevel.ReadCommitted);
// UPDLOCK is important here because it will start the lock
sql = @"SELECT ISNULL(MAX(CAST(Code AS INT)),0) FROM [@COR_DIA_AKM_LOTAT] WITH (UPDLOCK)";
var maxCode = await _connection.ExecuteAsync<int>(sql) + 1;
string newCode = maxCode.ToString("00000000");
sql = "INSERT INTO [@COR_DIA_AKM_LOTAT] (Code, Name, U_DocEntry, U_LineNum, U_LotNum, U_LotQty, U_ItemCode, U_Typ, U_DeletedF, U_LotCode) VALUES (@newCode, @newCode, @docEntry, @lineNum, @lot, @qty, @itemCode, 'F', 'N', '')";
await Task.Delay(4000);
await _connection.ExecuteAsync(sql, ...)
...more queries
await _connection.CommitTransactionAsync();