sql - Unable to see deadlocks on xml_deadlock_report -
i getting deadlock errors(nearly 20) since few hours.
mysp: 1205: transaction (process id 55) deadlocked on lock | communication buffer resources process , has been chosen deadlock victim. rerun transaction. transaction count after execute indicates mismatching number of begin , commit statements. previous count = 1, current count = 0 i trying more info using(extented event system_health running),
select xevent.query('(event/data/value/deadlock)[1]') deadlockgraph ( select xevent.query('.') xevent ( select cast(target_data xml) targetdata sys.dm_xe_session_targets st bring together sys.dm_xe_sessions s on s.address = st.event_session_address s.name = 'system_health' , st.target_name = 'ring_buffer' ) info cross apply targetdata.nodes ('ringbuffertarget/event[@name="xml_deadlock_report"]') xeventdata ( xevent ) ) src; update: here sp causing issue,
alter procedure [dbo].[insertorupdatedevicecatalog] ( @os nvarchar(50) ,@uniqueid varchar(500) ,@longitude float ,@latitude float ,@culture varchar(10) ,@other nvarchar(200) ,@ipaddress varchar(50) ,@nativedeviceid varchar(50) ,@myappapplicationid int = 1 ,@latestapplicationversion varchar(50) ) begin set nocount on; declare @trancount int; set @trancount = @@trancount; declare @olduniqueid varchar(500) = '-1'; select @olduniqueid = [uniqueid] devicecatalog (@nativedeviceid != '' , [nativedeviceid] = @nativedeviceid); declare @geo geography = geography::stgeomfromtext('point(' + convert(varchar(100), @longitude) + ' ' + convert(varchar(100), @latitude) + ')', 4326); declare @oldgeo geography ,@oldcity nvarchar(100) ,@oldcountry nvarchar(100) ,@oldaddress nvarchar(100); select @oldgeo = [lastupdatedlocationfromjob] ,@oldcity = [city] ,@oldcountry = [country] ,@oldaddress = [address] devicecatalog [uniqueid] = @uniqueid; begin seek if @trancount = 0 begin transaction else save transaction insertorupdatedevicecatalog; merge devicecatalog dc using (select @uniqueid uniqueid, @nativedeviceid nativedeviceid) t on (dc.uniqueid = t.uniqueid or dc.[nativedeviceid] = t.nativedeviceid) when matched update set [os] = @os ,[location] = @geo ,[culture] = @culture ,[other] = @other ,[ipaddress] = @ipaddress ,[nativedeviceid] = case when dc.uniqueid = t.uniqueid (case when @nativedeviceid null or @nativedeviceid = '' dc.[nativedeviceid] else @nativedeviceid end) else dc.[nativedeviceid] end ,[uniqueid] = case when dc.[nativedeviceid] = t.nativedeviceid , dc.[nativedeviceid] <> '' @uniqueid else dc.uniqueid end ,[lastmodifieddate] = case when (@oldgeo null or @oldaddress null or @oldcity null or @oldcountry null or isnull(@geo.stdistance(@oldgeo) / 1000,0) > 50) getdate() else [lastmodifieddate] end ,[lastupdatedbydevicedate] = getdate() ,[myappapplicationid] = @myappapplicationid ,[latestapplicationversion] = @latestapplicationversion when not matched insert ([os] ,[uniqueid] ,[location] ,[culture] ,[other] ,[ipaddress] ,[nativedeviceid] ,[myappapplicationid] ,[latestapplicationversion]) values (@os ,@uniqueid ,@geo ,@culture ,@other ,@ipaddress ,@nativedeviceid ,@myappapplicationid ,@latestapplicationversion); lbexit: if @trancount = 0 commit; end seek begin grab declare @error int, @message varchar(4000), @xstate int; select @error = error_number() ,@message = error_message() ,@xstate = xact_state(); if @xstate = -1 rollback; if @xstate = 1 , @trancount = 0 rollback if @xstate = 1 , @trancount > 0 rollback transaction insertorupdatedevicecatalog; raiserror ('insertorupdatedevicecatalog: %d: %s', 16, 1, @error, @message) ; end grab end and here xdl deadlock report(got profiler),
<deadlock-list> <deadlock victim="process2a28b0188"> <process-list> <process id="process2a28b0188" taskpriority="0" logused="228" waitresource="page: 5:1:1183 " waittime="8632" ownerid="2111061378" transactionname="user_transaction" lasttranstarted="2014-10-22t12:07:28.620" xdes="0x2206d9c40" lockmode="u" schedulerid="4" kpid="12064" status="suspended" spid="70" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2014-10-22t12:07:28.620" lastbatchcompleted="2014-10-22t12:07:28.620" lastattention="1900-01-01t00:00:00.620" clientapp=".net sqlclient info provider" hostname="norq01" hostpid="3640" isolationlevel="read uncommitted (1)" xactid="2111061378" currentdb="5" locktimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionstack> <frame procname="myapp.dbo.insertorupdatedevicecatalog" line="44" stmtstart="2692" stmtend="6364" sqlhandle="0x03000500d291a62559cc2801e3a2000001000000000000000000000000000000000000000000000000000000"> merge devicecatalog dc using (select @uniqueid uniqueid, @nativedeviceid nativedeviceid) t on (dc.uniqueid = t.uniqueid or dc.[nativedeviceid] = t.nativedeviceid) when matched update set [os] = @os ,[location] = @geo ,[culture] = @culture ,[other] = @other ,[ipaddress] = @ipaddress ,[nativedeviceid] = case when dc.uniqueid = t.uniqueid (case when @nativedeviceid null or @nativedeviceid = '' dc.[nativedeviceid] else @nativedeviceid end) else dc.[nativedeviceid] end ,[uniqueid] = case when dc.[nativedeviceid] = t.nativedeviceid , dc.[nativedeviceid] <> '' @uniqueid else dc.uniqueid end ,[lastmodifieddate] = case when (@oldgeo null or @oldaddress null or @oldcity null or @oldcountry null or isnull(@geo.stdistance(@oldgeo) / 1000,0) > 50) getdate() else [lastmodifieddate] end ,[lastupdatedbydevicedate] = getdate() ,[myappapplicationid] </frame> </executionstack> <inputbuf> proc [database id = 5 object id = 631673298] </inputbuf> </process> <process id="process2b46be558" taskpriority="0" logused="228" waitresource="page: 5:1:1183 " waittime="4650" ownerid="2111061011" transactionname="user_transaction" lasttranstarted="2014-10-22t12:07:25.357" xdes="0x1c8e65bb0" lockmode="u" schedulerid="1" kpid="9408" status="suspended" spid="51" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2014-10-22t12:07:25.357" lastbatchcompleted="2014-10-22t12:07:25.357" lastattention="1900-01-01t00:00:00.357" clientapp=".net sqlclient info provider" hostname="norq01" hostpid="3640" isolationlevel="read uncommitted (1)" xactid="2111061011" currentdb="5" locktimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionstack> <frame procname="myapp.dbo.insertorupdatedevicecatalog" line="44" stmtstart="2692" stmtend="6364" sqlhandle="0x03000500d291a62559cc2801e3a2000001000000000000000000000000000000000000000000000000000000"> merge devicecatalog dc using (select @uniqueid uniqueid, @nativedeviceid nativedeviceid) t on (dc.uniqueid = t.uniqueid or dc.[nativedeviceid] = t.nativedeviceid) when matched update set [os] = @os ,[location] = @geo ,[culture] = @culture ,[other] = @other ,[ipaddress] = @ipaddress ,[nativedeviceid] = case when dc.uniqueid = t.uniqueid (case when @nativedeviceid null or @nativedeviceid = '' dc.[nativedeviceid] else @nativedeviceid end) else dc.[nativedeviceid] end ,[uniqueid] = case when dc.[nativedeviceid] = t.nativedeviceid , dc.[nativedeviceid] <> '' @uniqueid else dc.uniqueid end ,[lastmodifieddate] = case when (@oldgeo null or @oldaddress null or @oldcity null or @oldcountry null or isnull(@geo.stdistance(@oldgeo) / 1000,0) > 50) getdate() else [lastmodifieddate] end ,[lastupdatedbydevicedate] = getdate() ,[myappapplicationid] </frame> </executionstack> <inputbuf> proc [database id = 5 object id = 631673298] </inputbuf> </process> <process id="process2bfef7c38" taskpriority="0" logused="10000" waittime="4617" schedulerid="2" kpid="1844" status="suspended" spid="51" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-22t12:07:25.357" lastbatchcompleted="2014-10-22t12:07:25.357" lastattention="1900-01-01t00:00:00.357" clientapp=".net sqlclient info provider" hostname="norq01" hostpid="3640" loginname="myapp" isolationlevel="read uncommitted (1)" xactid="2111061011" currentdb="5" locktimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionstack> <frame procname="myapp.dbo.insertorupdatedevicecatalog" line="44" stmtstart="2692" stmtend="6364" sqlhandle="0x03000500d291a62559cc2801e3a2000001000000000000000000000000000000000000000000000000000000"> merge devicecatalog dc using (select @uniqueid uniqueid, @nativedeviceid nativedeviceid) t on (dc.uniqueid = t.uniqueid or dc.[nativedeviceid] = t.nativedeviceid) when matched update set [os] = @os ,[location] = @geo ,[culture] = @culture ,[other] = @other ,[ipaddress] = @ipaddress ,[nativedeviceid] = case when dc.uniqueid = t.uniqueid (case when @nativedeviceid null or @nativedeviceid = '' dc.[nativedeviceid] else @nativedeviceid end) else dc.[nativedeviceid] end ,[uniqueid] = case when dc.[nativedeviceid] = t.nativedeviceid , dc.[nativedeviceid] <> '' @uniqueid else dc.uniqueid end ,[lastmodifieddate] = case when (@oldgeo null or @oldaddress null or @oldcity null or @oldcountry null or isnull(@geo.stdistance(@oldgeo) / 1000,0) > 50) getdate() else [lastmodifieddate] end ,[lastupdatedbydevicedate] = getdate() ,[myappapplicationid] </frame> </executionstack> <inputbuf> proc [database id = 5 object id = 631673298] </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="1183" dbid="5" subresource="full" objectname="myapp.dbo.devicecatalog" id="lock2edbca800" mode="u" associatedobjectid="72057594051952640"> <owner-list> <owner id="process2bfef7c38" mode="u"/> </owner-list> <waiter-list> <waiter id="process2a28b0188" mode="u" requesttype="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="1183" dbid="5" subresource="full" objectname="myapp.dbo.devicecatalog" id="lock2edbca800" mode="u" associatedobjectid="72057594051952640"> <owner-list> <owner id="process2a28b0188" mode="u" requesttype="wait"/> </owner-list> <waiter-list> <waiter id="process2b46be558" mode="u" requesttype="wait"/> </waiter-list> </pagelock> <exchangeevent id="pipe222b92900" waittype="e_waitpipegetrow" nodeid="9"> <owner-list> <owner id="process2b46be558"/> </owner-list> <waiter-list> <waiter id="process2bfef7c38"/> </waiter-list> </exchangeevent> </resource-list> </deadlock> </deadlock-list> sql sql-server tsql sql-server-2012
No comments:
Post a Comment