某个订阅库下存在很多关于更新MSreplication_subscriptions表造成的大量死锁
Posted on 2011-11-03 09:22 nzperfect 阅读(706) 评论(1) 编辑 收藏 举报订阅库中有很多的死锁现象,MSreplication_subscriptions表中有8条记录,死锁大概是1小时40个,如下所示:
deadlock-list
deadlock victim=processdbf1c8
process-list
process id=processdbf1c8 taskpriority=0 logused=2116 waitresource=PAGE: 6:1:3304140 waittime=577 ownerId=1250752686 transactionname=user_transaction lasttranstarted=2011-11-03T09:17:09.920 XDES=0xffffffffaadb39a8 lockMode=U schedulerid=2 kpid=2868 status=suspended spid=72 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2011-11-03T09:17:15.100 lastbatchcompleted=2011-11-03T09: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: 6:1:3304141 waittime=577 ownerId=1250752084 transactionname=user_transaction lasttranstarted=2011-11-03T09:17:08.693 XDES=0xffffffff99c24958 lockMode=U schedulerid=8 kpid=4356 status=suspended 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\EEAAPRD_db_ee_occ_lucene 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
发现已经有人把这个提交给微软了:
解决方法:
重建了该表的聚集索引,目前一小时内未出现死锁,持续监控中。
alter index uc1MSReplication_subscriptions on dbo.MSreplication_subscriptions REBUILD
作者:nzperfect
出处:http://www.cnblogs.com/nzperfect/
引用或者转载本BLOG的文章请注明原作者和出处,并保留原文章中的版权信息。