There are many deadlocks caused by updating the msreplication_subscriptions table in a subscription library.

There are many deadlocks in the subscription library. The msreplication_subscriptions table has 8 records, and the deadlock is about 40 in an hour, as shown below:

Deadlock-list
Deadlock victim = processdbf1c8
Process-list
Process id = processdbf1c8 taskpriority = 0 logused = 2116 waitresource = page: 3304140 waittime = 577 ownerid = 1250752686 transactionname = user_transaction lasttranstarted = 2011-11-03t09: 17: 09.920 xdes = 0xffffffffaadb39a8 lockmode = u schedulerid = 2 kpid = 2868 status = sushortded spid = 72 sbid = 0 ECID = 0 priority = 0 trancount = 2 lastbatchstarted = 2011-11-03t09: 17: 15.100 lastbatchcompleted = t09: 17: 15.100 clientapp = eeaaprd \ eeaaprd_db_ee_occ to rdj hostname = eeaadist hostpid = 4932 loginname = repl_user isolationlevel = Read committed (2) xactid = 1250752686 currentdb = 6 locktimeout = 4294967295 clientoption1 = 671156320 clientoption2 = 128056
Executionstack
Frame procname = adhoc line = 1 stmtstart = 164 sqlhandle = 0x02000000246863336efb6f4b519564562cfd41f094d18561
Update msreplication_subscriptions set transaction_timestamp = cast (@ P1 as binary (15) + Cast (substring (transaction_timestamp, 16, 1) as binary (1 )), "Time" = @ P2 where upper (publisher) = upper (@ P3) and publisher_db = @ P4 and publication = @ P5 and subscription_type = 0 and (substring (transaction_timestamp, 16, 1) = 0 or datalength (transaction_timestamp) <16)
Frame procname = unknown line = 1 sqlhandle = 0x000000000000000000000000000000000000000000000000
Unknown
Inputbuf
(@ P1 varbinary (14), @ P2 datetime, @ P3 nvarchar (13), @ P4 nvarchar (7), @ P5 nvarchar (14 )) update msreplication_subscriptions set transaction_timestamp = cast (@ P1 as binary (15) + Cast (substring (transaction_timestamp, 16, 1) as binary (1 )), "Time" = @ P2 where upper (publisher) = upper (@ P3) and publisher_db = @ P4 and publication = @ P5 and subscription_type = 0 and (substring (transaction_timestamp, 16, 1) = 0 or datalength (transaction_timestamp) <16)
Process id = process407b8e8 taskpriority = 0 logused = 37032 waitresource = page: 3304141 waittime = 577 ownerid = 1250752084 transactionname = user_transaction lasttranstarted = 2011-11-03t09: 17: 08.693 xdes = 0xffffffff99c24958 lockmode = u schedulerid = 8 kpid = 4356 status = sushortded spid = 92 sbid = 0 ECID = 0 priority = 0 trancount = 2 lastbatchstarted = 2011-11-03t09: 17: 14.917 lastbatchcompleted = 2011-11-03t09: 17: 14.913 clientapp = eeaaprd \ login to RD hostname = eeaadist hostpid = 7184 loginname = repl_user isolationlevel = Read committed (2) xactid = 1250752084 currentdb = 6 locktimeout = 4294967295 clientoption1 = 671156320 clientoption2 = 128056
Executionstack
Frame procname = adhoc line = 1 stmtstart = 164 sqlhandle = 0x02000000c535342ae6f47e336782ad406ac4eccd72796c0c
Update msreplication_subscriptions set transaction_timestamp = cast (@ P1 as binary (15) + Cast (substring (transaction_timestamp, 16, 1) as binary (1 )), "Time" = @ P2 where upper (publisher) = upper (@ P3) and publisher_db = @ P4 and publication = @ P5 and subscription_type = 0 and (substring (transaction_timestamp, 16, 1) = 0 or datalength (transaction_timestamp) <16)
Frame procname = unknown line = 1 sqlhandle = 0x000000000000000000000000000000000000000000000000
Unknown
Inputbuf
(@ P1 varbinary (14), @ P2 datetime, @ P3 nvarchar (13), @ P4 nvarchar (7), @ P5 nvarchar (20 )) update msreplication_subscriptions set transaction_timestamp = cast (@ P1 as binary (15) + Cast (substring (transaction_timestamp, 16, 1) as binary (1 )), "Time" = @ P2 where upper (publisher) = upper (@ P3) and publisher_db = @ P4 and publication = @ P5 and subscription_type = 0 and (substring (transaction_timestamp, 16, 1) = 0 or datalength (transaction_timestamp) <16)
Resource-list
Pagelock fileid = 1 pageid = 3304140 dbid = 6 objectname = dB. DBO. msreplication_subscriptions id = lock2f350b80 mode = iu associatedobjectid = 72057594038845440
Owner-list
Owner ID = process407b8e8 mode = iu
Waiter-list
Waiter ID = processdbf1c8 mode = u requesttype = wait
Pagelock fileid = 1 pageid = 3304141 dbid = 6 objectname = dB. DBO. msreplication_subscriptions id = lock3184e900 mode = iu associatedobjectid = 72057594038845440
Owner-list
Owner ID = processdbf1c8 mode = iu
Waiter-list
Waiter ID = process407b8e8 mode = u requesttype = wait

 

I found someone has submitted this to Microsoft:

Http://connect.microsoft.com/SQLServer/feedback/details/695689/transactional-push-replication-deadlock-on-msreplication-subscriptions#tabs

 

Solution:

The clustered index of the table is rebuilt. No Deadlock occurred within an hour and the table is under continuous monitoring.
Alter index uc1msreplication_subscriptions on DBO. msreplication_subscriptions rebuild