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();

Answer 1

Since linq2db was created to maximally avoid Raw SQL usage, there is the way how to insert such record without transaction.

// tricky part, creating LINQ query which returns result set with calculated 'newCode'
var maxQuery = db.SelectQuery<string>(() =>
        Sql.ConvertTo<string>.From(
            db.GetTable<COR_DIA_AKM_LOTAT>().Max(x => Sql.ConvertTo<int?>.From(x.Code)) ?? 0 + 1)
    )
    .AsSubQuery()  // introducing subquery to help better PadLeft calculation
    .Select(x => Sql.PadLeft(x, 8, '0'))
    .AsSubQuery(); // additional subquery because value will be used twice

// inserting prepared value into destination table
maxQuery.Insert(db.GetTable<COR_DIA_AKM_LOTAT>(),
    newCode =>
        new COR_DIA_AKM_LOTAT
        {
            Code = newCode, 
            Name = newCode,
            U_DocEntry = updatePos.docEntry,
            U_LineNum = updatePos.lineNum,
            U_LotNum = l.lot,
            U_LotQty = l.qty,
            U_ItemCode = updatePos.itemCode,
            U_Typ = "F",
            U_DeletedF = "N",
            U_LotCode = ""
        });

Query should generate the following SQL (some values changed for successful test execution):

INSERT INTO [dbo].[@COR_DIA_AKM_LOTAT]
(
    [Code],
    [Name],
    [U_DocEntry],
    [U_LineNum],
    [U_LotNum],
    [U_LotQty],
    [U_DeletedF],
    [U_PkgMandatoryQty]
)
SELECT
    [t1].[c1],
    [t1].[c1],
    1,
    1,
    N'1',
    1,
    N'N',
    1
FROM
    (
        SELECT
            IIF(Len([x_1].[c1]) > 8, [x_1].[c1], Replicate(N'0', 8 - Len([x_1].[c1])) + [x_1].[c1]) as [c1]
        FROM
            (
                SELECT
                    Convert(NVarChar(11), Coalesce((
                        SELECT
                            Max(Convert(Int, [x].[Code]))
                        FROM
                            [dbo].[@COR_DIA_AKM_LOTAT] [x]
                    ), 1)) as [c1]
            ) [x_1]
    ) [t1]

Anyway better to think about special table which contains last increment value. Because calculating Max by string field is not good option for performance.