LINQ to SQL - Update to increment a non-primary-key field - thread-safe

Question

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

Bets (holds the bets; Columns : Id, , MessagesPosted, ) Bets_Messages (holds the bets' forum messages; Columns : Id, BetId, )

When I insert a new BetMessage in Bets_Messages I want to update (increment to be precise) the corresponding field in Bets.

In pure T-SQL that would be :

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

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.

However using LINQ to SQL this comes to a more difficult 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();
    }

Looks nice, huh? Well here's what it will generate :

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'

(for the BetMessage insert) and for the 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

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?

Here's why I think this.

1st thread does this :

insert the corresponding betMessage, update the bet row to increment the MessagePosted from 0 to 1

the 2nd thread will do this:

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.

Therefore I will have to write my f*#$ing retry attempt code instead of relying on ROW LOCKs in the SQL Server.

Is there some decent approach that uses LINQ to SQL and NOT stored procedures, ad-hoc queries and so on?

Thank you for your patience of reading this long post..

Answer 1

EDIT: Actually after rereading your post, I think that the automatic transaction generated by SubmitChanges will already take care of making sure that all of the statements are completed or none are. I was thinking that since you were using the automatically generated id, that you'd do a two-stage update in LINQ, but it seems that SubmitChanges handles it for you.

I'll leave the code below for reference, though I don't think that it's required. The link at the bottom explains about the different ways of accomplishing the transaction.

What you need is a transaction scope to wrap the entire set of inserts/updates in.

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

I don't think this will cause automatic promotion to a distributed transaction since all of the commands reuse the same connection in the data context. If it turns out that it does, you can create a transaction on the data context's connection and assign it to the data context's Transaction property. If you do this, though, you'll need to manage and dispose of it yourself.

More info on LINQ2SQL and transactions at MSDN.