SQL Server replication and application queries/updates conflicts resulting in deadlocks

Question

We recently upgraded to sql server 2012. We have xxx-D-011 as OLTP server and yyy-D-011 as distributor server. The log is showing deadlocks every day between application queries/updates and replication jobs.

A fragment of the log about the deadlock is included below. Any comments/ideas that can help me with the next steps will be much appreciated. Thank you!

2014-07-10 15:31:05.94 spid13s     deadlock-list
2014-07-10 15:31:05.94 spid13s      deadlock victim=process37ced3498
2014-07-10 15:31:05.94 spid13s       process-list
2014-07-10 15:31:05.94 spid13s        process id=process37ced3498 taskpriority=0 logused=0 waitresource=OBJECT: 8:532249001:0  waittime=357 ownerId=860304057 transactionname=SELECT lasttranstarted=2014-07-10T15:31:05.090 XDES=0x4674fbca0 lockMode=Sch-S schedulerid=2 kpid=10128 status=suspended spid=111 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2014-07-10T15:31:05.090 lastbatchcompleted=2014-07-10T15:31:05.090 lastattention=1900-01-01T00:00:00.090 clientapp=.Net SqlClient Data Provider hostname=DIT3330W7DT hostpid=1092 loginname=xuser isolationlevel=read uncommitted (1) xactid=860304057 currentdb=8 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
2014-07-10 15:31:05.94 spid13s         executionStack
2014-07-10 15:31:05.94 spid13s          frame procname= dbo.UpdatePurchaseOrderWorkQueueStatus line=39 stmtstart=2232 stmtend=2756 sqlhandle=0x0300080013baae0b4974c5004aa3000001000000000000000000000000000000000000000000000000000000
2014-07-10 15:31:05.94 spid13s     SELECT  @XDate   = dbo.fn_getxdate(C.ClinicID)
2014-07-10 15:31:05.94 spid13s     FROM dbo.Station S 
2014-07-10 15:31:05.94 spid13s      INNER JOIN dbo.Clinic C  ON C.ClinicID = S.ClinicID
2014-07-10 15:31:05.94 spid13s      INNER JOIN dbo.PurchaseOrder PO ON PO.StationID = S.StationID
2014-07-10 15:31:05.94 spid13s     WHERE S.Active = 1
2014-07-10 15:31:05.94 spid13s     AND  PO.PurchaseOrderID = @PurchaseOrderID     
2014-07-10 15:31:05.94 spid13s         inputbuf
2014-07-10 15:31:05.94 spid13s     Proc [Database Id = 8 Object Id = 196000275]    
2014-07-10 15:31:05.94 spid13s        process id=process37ced3c38 taskpriority=0 logused=60504 waitresource=OBJECT: 8:644249400:0  waittime=313 ownerId=860303537 transactionname=user_transaction lasttranstarted=2014-07-10T15:31:04.830 XDES=0x1da8ead28 lockMode=Sch-M schedulerid=2 kpid=5544 status=suspended spid=119 sbid=0 ecid=0 priority=0 trancount=5 lastbatchstarted=2014-07-10T15:31:04.880 lastbatchcompleted=2014-07-10T15:31:04.880 lastattention=1900-01-01T00:00:00.880 clientapp=xxx-D-011-x-x_OrgHierarchy_Publication-13 hostname=yyy-D-011 hostpid=3360 loginname=svcadminsqldev isolationlevel=read committed (2) xactid=860303537 currentdb=8 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128024
2014-07-10 15:31:05.94 spid13s         executionStack
2014-07-10 15:31:05.94 spid13s          frame procname=mssqlsystemresource.sys.sp_MSsetfilteredstatus line=30 stmtstart=1570 stmtend=1796 sqlhandle=0x0300ff7f66f82acd71e2fc00efa0000001000000000000000000000000000000000000000000000000000000
2014-07-10 15:31:05.94 spid13s     EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)     
2014-07-10 15:31:05.94 spid13s          frame procname=mssqlsystemresource.sys.sp_MSrepl_changesubstatus line=909 stmtstart=66056 stmtend=66312 sqlhandle=0x0300ff7f48dddfc2bfe2fc00efa0000001000000000000000000000000000000000000000000000000000000
2014-07-10 15:31:05.94 spid13s     exec sys.sp_MSsetfilteredstatus @tabid
2014-07-10 15:31:05.94 spid13s                         -- clear nonsqlsub status for this article.     
2014-07-10 15:31:05.94 spid13s          frame procname=mssqlsystemresource.sys.sp_changesubstatus line=52 stmtstart=3496 stmtend=5162 sqlhandle=0x0300ff7f5eea5ef792d8fa00efa0000001000000000000000000000000000000000000000000000000000000
2014-07-10 15:31:05.94 spid13s     EXEC @retcode = @cmd
2014-07-10 15:31:05.94 spid13s                      @publication,
2014-07-10 15:31:05.94 spid13s                      @article,
2014-07-10 15:31:05.94 spid13s                      @subscriber,
2014-07-10 15:31:05.94 spid13s                      @status,
2014-07-10 15:31:05.94 spid13s                      @previous_status,
2014-07-10 15:31:05.94 spid13s                      @destination_db,
2014-07-10 15:31:05.94 spid13s                      @frequency_type,
2014-07-10 15:31:05.94 spid13s                      @frequency_interval,
2014-07-10 15:31:05.94 spid13s                      @frequency_relative_interval,
2014-07-10 15:31:05.94 spid13s                      @frequency_recurrence_factor,
2014-07-10 15:31:05.94 spid13s                      @frequency_subday,
2014-07-10 15:31:05.94 spid13s                      @frequency_subday_interval,
2014-07-10 15:31:05.94 spid13s                      @active_start_time_of_day,
2014-07-10 15:31:05.94 spid13s                      @active_end_time_of_day,
2014-07-10 15:31:05.94 spid13s                      @active_start_date,
2014-07-10 15:31:05.94 spid13s                      @active_end_date,
2014-07-10 15:31:05.94 spid13s                      @optional_command_line,
2014-07-10 15:31:05.94 spid13s                      @distribution_jobid OUTPUT,
2014-07-10 15:31:05.95 spid13s                      @from_auto_sync,
2014-07-10 15:31:05.95 spid13s                      @ignore_distributor,
2014-07-10 15:31:05.95 spid13s                      -- Agent offload
2014-07-10 15:31:05.95 spid13s                      @offloadagent,
2014-07-10 15:31:05.95 spid13s                      @offloadserver,
2014-07-10 15:31:05.95 spid13s                      @dts_package_name,
2014-07-10 15:31:05.95 spid13s                      @dts_package_password,
2014-07-10 15:31:05.95 spid13s                      @dts_package_location,
2014-07-10 15:31:05.95 spid13s                      @skipobjectactivation,
2014-07-10 15:31:05.95 spid13s                      @distribution_job_name,
2014-07-10 15:31:05.95 spid13s                      @publisher,
2014-07-10 15:31:05.95 spid13s                      @publisher_type
2014-07-10 15:31:05.95 spid13s                      ,@ignore_distributor_failure     
2014-07-10 15:31:05.95 spid13s          frame procname=mssqlsystemresource.sys.sp_MSactivate_auto_sub line=92 stmtstart=4236 stmtend=4768 sqlhandle=0x0300ff7f6366c5c4d8e2fc00efa0000001000000000000000000000000000000000000000000000000000000
2014-07-10 15:31:05.95 spid13s     *sp_changesubstatus------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------     
2014-07-10 15:31:05.95 spid13s         inputbuf
2014-07-10 15:31:05.95 spid13s     Proc [Database Id = 32767 Object Id = -993696157]    
2014-07-10 15:31:05.95 spid13s       resource-list
2014-07-10 15:31:05.95 spid13s        objectlock lockPartition=0 objid=532249001 subresource=FULL dbid=8 objectname=X.dbo.Station id=lock389908f00 mode=Sch-M associatedObjectId=532249001
2014-07-10 15:31:05.95 spid13s         owner-list
2014-07-10 15:31:05.95 spid13s          owner id=process37ced3c38 mode=Sch-M
2014-07-10 15:31:05.95 spid13s         waiter-list
2014-07-10 15:31:05.95 spid13s          waiter id=process37ced3498 mode=Sch-S requestType=wait
2014-07-10 15:31:05.95 spid13s        objectlock lockPartition=0 objid=644249400 subresource=FULL dbid=8 objectname=X.dbo.Clinic id=lock3f2864b00 mode=S associatedObjectId=644249400
2014-07-10 15:31:05.95 spid13s         owner-list
2014-07-10 15:31:05.95 spid13s          owner id=process37ced3498 mode=Sch-S
2014-07-10 15:31:05.95 spid13s         waiter-list
2014-07-10 15:31:05.95 spid13s          waiter id=process37ced3c38 mode=Sch-M requestType=convert

score 1 · Answer 1 · answered Jul 10 '14 at 22:14

1

This is very unusual.

Are you using a plain vanilla publication/subscription?

Is there a filter in the mix? Can we see the filter if you are using one?

There are some issues with local table variables in triggers in published articles.

I suspect the issue is with fn_getxdate, can I see the code for that function?

answered Jul 10 '14 at 22:14

HilaryCotter

109
1

Hilary, You are in the right track. At this writing, I have 2 environments DEV and QA. In QA Replication snapshot jobs were disabled. In Dev, Replication snapshot jobs run every 5 minutes. In QA there is no deadlocks. So the suspect is the replication snapshot jobs running too frequently. I will disable the DEV replication snapshots and watch the log for the next 3-5 days to see if that helps minimizing the deadlocks. I will confirm if the snapshot jobs disabled stop/damper the flow of deadlocks. Thanks! – Daisy Jul 11 '14 at 17:39
Hilary, All indications are the snapshot jobs running too frequently caused the deadlocks. They should only run once at set up then on demand. I made the changes, enabled the jobs and things are better now. Thanks! – Daisy Jul 13 '14 at 16:36

SQL Server replication and application queries/updates conflicts resulting in deadlocks

1 Answers1