LINQ to SQL - update to increase non-primary key field - thread safe

Question

I have two tables (well, two relevant for this question):

Bids (holds bets; Columns: Id, MessagesPosted,) Bets_Messages (contains messages from the betting forum, columns: Id, BetId,)

When I insert a new BetMessage into Bets_Messages, I want to update (enlarge to be exact) the corresponding field in Bets.

In pure T-SQL, which will be:

INSERT INTO Bets_Messages (BetId, <bla bla>) VALUES (23, "asfasdfasdf");
UPDATE Bets SET MessagesPosted = MessagesPosted + 1 WHERE Id = 23;

The above code will work just fine and it will be thread safe; If two threads make database calls to it (and for the same rate), the MessagesPosted column will grow nicely, since the first UPDATE would put at least ROWLOCK on it, practically serializing UPDATE.

However, using LINQ to SQL, this leads to a more complex approach:

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

, ? :

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'<m ai="a0d253af-6261-49ae-8c11-ba6117ef35c7" a="AndreiR" s="aaawww" t="2009-01-31T18:04:31.282+02:00">wwwwwaaa</m>',@p5='2009-01-31 18:04:31.2820000',@p6=0,@p7=N'wwwwwaaa'

( BetMessage) UPDATE:

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

T-SQL, UPDATE, , , , , , , , , , , . ?

.

1- :

betMessage, , MessagePosted 0 1

:

betMessage, , MessagePosted 0 1 ( 0, ). 1, WHERE , wHERE false. 0 LINQ , , .

f * # $ing , ROW LOCK SQL Server.

, LINQ to SQL , ..?

, .

Answer 1

. , , , , SubmitChanges, , . , , , LINQ, , SubmitChanges .

, , . .

, /.

using System.Transactions;

public void PostMessage(MyProject.Entities.BetMessage betMessageEntity)
{
    using (TransactionScope scope = new TransactionScope()) {
       DatabaseDataContext ctx = GetFreshContext();
       Bets_Message msg = new Bets_Message(betMessageEntity);
       ctx.Bets_Messages.InsertOnSubmit(msg);
       ctx.SubmitChanges();  // this is what I thought you did
       Bet bet = (from b in ctx.Bets
                  where b.Id == (long)betMessageEntity.BetId select b)
                 .Single();
       bet.MessagesPosted++;
       ctx.SubmitChanges();
       scope.Complete();
    }
}

, , . , , . , .

LINQ2SQL MSDN.