c# - Sql Server Transaction Commit times out -

i have weird issue in application. happens once or may twice in week. here situation:

i have method in application queries db multiple times, first there 4 selects, 1 of them uses keyword updlock follows insert other table (not 1 updlock applied) , the update on table updlock-ed.

all of queries done in 1 transaction (which @ side of .net) , gets commit-ed.

now, problem transaction.commit() throws exception message

timeout expired. timeout period elapsed prior completion of operation or server not responding

(as guess sqlconnection times out).

so have whole procedure wrapped in try-catch block , if exception occurs try rollback transaction when happens code execution goes catch block , transaction.rollback() called , throws exception message

this sqltransaction has completed. no longer usable

(as guess when commit times out transaction gets commit-ed), after parts of application messes up. thing believed not exist (because of rollback) exist , causes unexpected issues manually fixed (at time).

i not find point problem can be, rather increasing timeout of sqlconnection. if has dealt issue before share experience, in advance. (the db server cpu utilization never goes above 45-50%, cases idles @ 3-15%)

here first sql select --first select

    select top 1             t.id ,             t.oid ,             t.amount ,     t.duserid,             t.startdate ,             t.extdesc,     t.statusid        dbo.[transaction] t             join dbo.wallet cw on t.cid = cw.id             join dbo.wallet dw on t.did = dw.id       extkey = @extkey              , ( cw.vid = @vid                    or dw.vid = @vid                  )  --second selct executes twice differenc params       select  u.id ,             username ,             pincode ,             currencyid ,             statusid ,             personalnumber ,             gu.defaultvendorserviceid ,             countryid,     u.firstname,     u.lastname        dbo.[user] u             left join dbo.gambleruser gu on gu.userid = u.id       u.id = @userid   --last select (updlock)   select w.id, accountno, fundtypeid, vendorserviceid, balance, userid, vs.islocalaccount  wallet w (updlock) join vendorservice vs on w.vid = vs.id      w.userid  = @userid , w.fundtypeid = @fundtypeid  , w.vendorserviceid  = @vendorserviceid   -- insert       insert  [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)  id = @id 

(i assume not hekaton thing differently on commit.)

a commit takes trivial amount of time. 1 physical write must go log , in case of mirroring/ag network roundtrip must made. 1 of things hold commit here.

i experienced problem overloaded mirroring connection.

the commit timeout cannot changed separately (which consider defect). connect timeout being used.

investigate root causes mentioned above. workaround increase commit timeout.

in case of failed commit cannot assume transaction committed or not. (this 2 generals problem. unsolvable in general.) must devise kind of check see whether database contains expected writes or not. more common on azure. azure guidance.