title: LINQ to SQL - Update to increment a non-primary-key field - thread-safe
body: <p>I have two tables (well, two relevant for this question) :</p>
  
  <p>Bets (holds the bets; Columns : Id, , MessagesPosted, )
  Bets_Messages (holds the bets' forum messages; Columns : Id, BetId, )</p>
  
  <p>When I insert a new BetMessage in Bets_Messages I want to update (increment to be precise) the corresponding field in Bets.</p>
  
  <p>In pure T-SQL that would be :</p>
  
  <pre><code>INSERT INTO Bets_Messages (BetId, &lt;bla bla&gt;) VALUES (23, "asfasdfasdf");
  UPDATE Bets SET MessagesPosted = MessagesPosted + 1 WHERE Id = 23;
  </code></pre>
  
  <p>The above code would work wonderful and it is thread safe; If two threads would make DB calls to it (and for the same Bet ofcourse) the MessagesPosted column would increment nicely since the first UPDATE would put at least a ROWLOCK on it practically serializing the UPDATEs.</p>
  
  <p>However using LINQ to SQL this comes to a more difficult approach: </p>
  
  <pre><code>    public void PostMessage(MyProject.Entities.BetMessage betMessageEntity)
      {
          DatabaseDataContext ctx = GetFreshContext(); // GetFreshContext is a private method that practically initializes a new DataContext
          Bets_Message msg = new Bets_Message(betMessageEntity);
          ctx.Bets_Messages.InsertOnSubmit(msg);
          Bet bet = (from b in ctx.Bets where b.Id == (long)betMessageEntity.BetId select b).Single();
          bet.MessagesPosted++;
          ctx.SubmitChanges();
      }
  </code></pre>
  
  <p>Looks nice, huh? Well here's what it will generate : </p>
  
  <pre><code>exec sp_executesql N'INSERT INTO [dbo].[Bets_Messages]([ParentMessageId], [BetsId], [UserId], [Subject], [DisplayXml], [Time], [ReplyDepth], [Text])
  VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7)
  
  SELECT CONVERT(BigInt,SCOPE_IDENTITY()) AS [value]',N'@p0 bigint,@p1 bigint,@p2 uniqueidentifier,@p3 nvarchar(6),@p4 nvarchar(114),@p5 datetime2(7),@p6 tinyint,@p7 nvarchar(8)',@p0=NULL,@p1=1,@p2='A0D253AF-6261-49AE-8C11-BA6117EF35C7',@p3=N'aaawww',@p4=N'&lt;m ai="a0d253af-6261-49ae-8c11-ba6117ef35c7" a="AndreiR" s="aaawww" t="2009-01-31T18:04:31.282+02:00"&gt;wwwwwaaa&lt;/m&gt;',@p5='2009-01-31 18:04:31.2820000',@p6=0,@p7=N'wwwwwaaa'
  </code></pre>
  
  <p>(for the BetMessage insert) and for the UPDATE :</p>
  
  <pre><code>exec sp_executesql N'UPDATE [dbo].[Bets]
  SET [MessagesPosted] = @p17
  WHERE ([Id] = @p0) AND ([UserId] = @p1) AND ([Bets_CategoriesId] = @p2) AND ([Bets_TypesId] = @p3) AND ([TotalSum] = @p4) AND ([TotalBetters] = @p5) AND ([CreateDate] = @p6) AND ([DeadlineDate] = @p7) AND ([ClosedDate] IS NULL) AND ([Bets_StatusesId] = @p8) AND ([LastBetAdded] IS NULL) AND ([Title] = @p9) AND ([ShortDescription] = @p10) AND ([Description] = @p11) AND ([DescriptionPlainText] = @p12) AND ([Version] = @p13) AND ([ReviewedBy] = @p14) AND ([UrlFragment] = @p15) AND ([MessagesPosted] = @p16) AND ([ClosedBy] IS NULL) AND ([OutcomeNumber] IS NULL)',N'@p0 bigint,@p1 uniqueidentifier,@p2 smallint,@p3 tinyint,@p4 money,@p5 int,@p6 datetime2(7),@p7 datetime2(7),@p8 tinyint,@p9 nvarchar(7),@p10 nvarchar(30),@p11 nvarchar(33),@p12 nvarchar(22),@p13 smallint,@p14 uniqueidentifier,@p15 varchar(7),@p16 int,@p17 int',@p0=1,@p1='A0D253AF-6261-49AE-8C11-BA6117EF35C7',@p2=2,@p3=1,@p4=$0.0000,@p5=0,@p6='2008-12-03 00:00:00',@p7='2008-12-31 00:00:00',@p8=2,@p9=N'Pariu 1',@p10=N'Descriere pariu 1 - text chior',@p11=N'Descriere pe larg 1 - html permis',@p12=N'descriere text chior 1',@p13=1,@p14='A0D253AF-6261-49AE-8C11-BA6117EF35C7',@p15='pariu-1',@p16=18,@p17=19
  </code></pre>
  
  <p>The problem with the T-SQL generated for the UPDATE is that although seems ok as thread-safety it will probably throw an error  in the second thread doing the update on the row instead of waiting for it to finish. Will it?</p>
  
  <p>Here's why I think this.</p>
  
  <p>1st thread does this :</p>
  
  <p>insert the corresponding betMessage,
  update the bet row to increment the MessagePosted from 0 to 1</p>
  
  <p>the 2nd thread will do this:</p>
  
  <p>insert it's corresponding betMessage,
  update the bet row to increment the MessagePosted from 0 to 1 (it was 0 when it read it). However now it is 1 and the WHERE clause will make it not update since the wHERE clause will evaluate to false. 0 row(s) affected will be sent to the LINQ client and that in turn will throw an exception.</p>
  
  <p>Therefore I will have to write my f*#$ing retry attempt code instead of relying on ROW LOCKs in the SQL Server.</p>
  
  <p>Is there some decent approach that uses LINQ to SQL and <strong>NOT</strong> stored procedures, ad-hoc queries and so on?</p>
  
  <p>Thank you for your patience of reading this long post..</p>