Sql Server Transaction Commit times out

Question

I have this weird issue in my application. It happens really rarely like once or may be twice in a week. So basically here is the situation:

I have this method in my application which queries DB multiple times, first there are 4 selects, one of them uses the keyword UPDLOCK then follows an insert to the other table (not one to which the UPDLOCK is applied) and the the update on the table which was previously UPDLOCK-ed.

All of this queries are done in one transaction (which is at the side of .NET) and finally it gets COMMIT-ed.

Now, the problem is that the transaction.Commit() throws exception with message

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

(as I guess SqlConnection times out).

So I have this whole procedure wrapped in a try-catch block and if exception occurs I try to rollback the transaction so when this happens the code execution goes to catch block and transaction.RollBack() is called and it also throws exception with message

This SqlTransaction has completed. it is no longer usable

(as I guess when the COMMIT times out the transaction actually gets COMMIT-ed), so after this some parts of application messes up. The thing that is believed not to exist (because of ROLLBACK) actually exist and causes some unexpected issues which are then manually fixed (at this time).

I could not find anything that could point to what the problem can be, rather than increasing the timeout of SqlConnection. If someone has dealt with this issue before could you share the experience, thanks in advance. (The DB Server CPU utilization never goes above 45-50%, is most cases it idles at 3-15%)

Here is the first Sql Select --First Select

    SELECT TOP 1
            t.Id ,
            t.OId ,
            t.Amount ,
    t.DUserId,
            t.StartDate ,
            t.ExtDesc,
    t.StatusId
    FROM    dbo.[Transaction] t
            JOIN dbo.Wallet cw ON t.CId = cw.Id
            JOIN dbo.Wallet dw ON t.DId = dw.Id
    WHERE   ExtKey = @ExtKey 
            AND ( cw.vId = @vId 
                  OR dw.VId = @vId 
                )

--Second Selct which executes twice with differenc params


    SELECT  u.Id ,
            UserName ,
            PinCode ,
            CurrencyId ,
            StatusId ,
            PersonalNumber ,
            gu.DefaultVendorServiceId ,
            CountryId,
    u.FirstName,
    u.LastName
    FROM    dbo.[User] u
            LEFT JOIN dbo.GamblerUser gu ON gu.UserId = u.Id
    WHERE   u.Id = @UserId


--Last select with (updlock)


SELECT w.Id, AccountNo, FundTypeId, VendorServiceId, Balance, UserId, vs.IsLocalAccount

FROM Wallet w (UPDLOCK)
JOIN VendorService vs on w.VId = vs.Id
WHERE 
    w.UserId  = @UserId
AND w.FundTypeId = @FundTypeId 
AND w.VendorServiceId  = @VendorServiceId


-- Insert


    INSERT  INTO [dbo].[Transaction]
            ( StartDate ,
              OTypeId ,
              StatusId ,
              Amount ,
              ExtDesc,
              DUserId 
            )
    VALUES                  ( @StartDate ,
              @OTypeId ,
              @StatusId ,
              @Amount ,
              @ExtDesc,
              @DUserId 
            )

    SET @Id = ( SELECT  @@IDENTITY
              )


-- Update on updlocked table    


UPDATE dbo.Wallet SET

    Balance = ISNULL(@Balance, Balance)

WHERE Id = @Id

Answer 1

(I assume this is not Hekaton which does thing differently on commit.)

A commit usually takes a trivial amount of time. One physical write must go to the log and in case of Mirroring/AG a network roundtrip must be made. One of those things is likely to hold up the commit here.

I personally experienced this problem with an overloaded Mirroring connection.

The commit timeout cannot be changed separately (which I consider to be a defect). The connect timeout is being used.

Investigate the root causes I mentioned above. As a workaround increase commit timeout.

In case of a failed commit you cannot assume that the transaction was actually committed or not. (This is the Two Generals Problem. It is unsolvable in general.) You must devise some kind of check to see whether the database contains the expected writes or not. This is more common on Azure. Look into Azure guidance.