title: linqtodb use connection.BeginTransactionAsync inside asp.net core
body: <p>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</p>
  <p>the error i get:</p>
  <pre><code>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
  ...
  ...
  &quot;message&quot;:&quot;Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.&quot;
  </code></pre>
  <p>the code in question is:</p>
  <pre class="lang-cs prettyprint-override"><code>...
      await _connection.BeginTransactionAsync(System.Data.IsolationLevel.Serializable);
                      sql = &quot;SELECT ISNULL(MAX(CAST(Code AS INT)),0) FROM [@COR_DIA_AKM_LOTAT]&quot;;
                          var maxCode = await _connection.ExecuteAsync&lt;int&gt;(sql) + 1;
                          string newCode = maxCode.ToString(&quot;00000000&quot;);
                          sql = &quot;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', '')&quot;;
                      await Task.Delay(10000);
                      await _connection.ExecuteAsync(sql,
                              new DataParameter { Name = &quot;@newCode&quot;, Value = newCode },
                              new DataParameter { Name = &quot;@qty&quot;, Value = l.qty },
                              new DataParameter { Name = &quot;@docEntry&quot;, Value = updatePos.docEntry },
                              new DataParameter { Name = &quot;@lineNum&quot;, Value = updatePos.lineNum },
                              new DataParameter { Name = &quot;@itemCode&quot;, Value = updatePos.itemCode },
                              new DataParameter { Name = &quot;@lot&quot;, Value = l.lot }
                              );
                      await _connection.CommitTransactionAsync();
  ...
  </code></pre>
  <p>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.</p>
  <p>So i need to make sure that concurrent usage of the above part will wait for eachother to finish</p>
  <p>The await Task.Delay(...) is just there so i can Test the concurrent usage</p>
  <p>When i now execute this code from 2 separate clients the second call from the 2nd client fails with the above message</p>
  <p>things i've considered but are not applicable:</p>
  <ul>
  <li>use a stored procedure</li>
  <li>use one sql statement and get the new id as a result from a subquery</li>
  <li>use a mutex lock in the application (bad idea anyway i guess)</li>
  </ul>
  <p>what i expect from the code:</p>
  <ul>
  <li>the 2nd client waits until it can get the lock. this waiting will be done automagically from within my await ...BeginTransaction() right?</li>
  </ul>
  <p>Here the model of the table in question:</p>
  <pre class="lang-cs prettyprint-override"><code>    [Table(Schema = &quot;dbo&quot;, Name = &quot;@COR_DIA_AKM_LOTAT&quot;)]
      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; }
      }
  </code></pre>
  <p>any enlightment is highly appreciated</p>
  <p><strong>Update</strong></p>
  <p>My misunderstanding was that i was thinking</p>
  <pre class="lang-cs prettyprint-override"><code>...BeginTransaction(..)
  </code></pre>
  <p>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:</p>
  <pre class="lang-cs prettyprint-override"><code>// read committed is fine because i just would like to lock -&gt; execute queries -&gt; commit 
   using var trans = await _connection.BeginTransactionAsync(System.Data.IsolationLevel.ReadCommitted);
  // UPDLOCK is important here because it will start the lock
  sql = @&quot;SELECT ISNULL(MAX(CAST(Code AS INT)),0) FROM [@COR_DIA_AKM_LOTAT] WITH (UPDLOCK)&quot;;
  var maxCode = await _connection.ExecuteAsync&lt;int&gt;(sql) + 1;
  string newCode = maxCode.ToString(&quot;00000000&quot;);
  sql = &quot;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', '')&quot;;
  await Task.Delay(4000);
  await _connection.ExecuteAsync(sql, ...)
  ...more queries
  await _connection.CommitTransactionAsync();
  </code></pre>