Search

transactional push replication : Deadlock on MSreplication_subscriptions by Kunk

Closed
as Won't Fix Help for as Won't Fix

1
0
Sign in
to vote
Type: Bug
ID: 695689
Opened: 10/19/2011 5:19:17 AM
Access Restriction: Public
0
Workaround(s)
0
User(s) can reproduce this bug
We use transactional push replication where muliple publisher (publication) replicate to on subscriber db. Sometimes have deadlocks during 2 Subscriber updation system table MSreplication_subscriptions. Both updates are as follows :

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)

Examine the table scheme shows that transaction_timestamp is part of the unique clustered index. So it seems to be logic that deadlocks might happen.

But for what reasons transaction_timestamp is part of the unique index ?
Isn't publication, publisher_db and publisher unique for itself ?
Details (expand)

Product Language

English

Version

SQL Server 2005 SP3

Category

Replication

Operating System

Other

Operating System Language

US English

Steps to Reproduce

no repro

Actual Results

deadlocks on MSreplication_subscriptions

Expected Results

ther shouldn't be deadlocks on MSreplication_subscriptions if transaction_timestamp is not part of the unique clustered index.

Platform

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Microsoft on 2/28/2012 at 11:49 AM
Hi Mr Kunk,

thanks for the update. As such I'm archiving this item for now.
Regards


Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
SQL Server Engine Team
Posted by Kunk on 2/24/2012 at 5:08 AM
Hello Mr. Devant,

in the meantime Mr. Linke from MS support team (germany) wrote a kb article (number 2674882) and started the publishing process.

Regards
Wolfgang Kunk
Posted by Microsoft on 2/23/2012 at 12:40 PM
Hi Mr Kunk,

Sorry, this item has been prematurely resolved, before I could share feedback.
We've looked into this. Although we would like to improve the product this way we will not have the means to do it in a foreseeable future.
That said your feedback is very valuable to us, we keep track of the situation.


Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
SQL Server Engine Team
Posted by Kunk on 1/25/2012 at 1:27 AM
In the meantime I found forum entries in the internet which guided me to a solution for that problem ( see http://preethiviraj.blogspot.com/2009_02_01_archive.html or http://www.sqlservercentral.com/Forums/Topic571883-291-1.aspx#bm572375). Table properties shows a rowcount of 4294967296 instead of the real value (in our case 8). Recreating the index sets the rowcount to a proper value. I recreated the index this 2 days ago and up to now there wasn't any deadlock!

Wolfgang Kunk



Posted by Kunk on 1/9/2012 at 1:41 AM
In the meantime I opened a Case at MS Support for this issue.
Field "Resolution" changed from "Not Set" to "Won't Fix". This is not acceptable cause I still think that it is a bug (or a design issue). It is not the first time that a bug is resolved this way. With this kind of resolution MS Connect isn't useful for me.

Regards
Wolfgang Kunk
Posted by Kunk on 11/7/2011 at 7:54 AM
Hi,

I think that is a bug. I cannot see a reason why "transaction_timestamp" to be a part of the unique index on this table.

And I have to correct the statement of my last comment. In the meantime I also found deadlocks while "update statistics" is not running. So there isn't a correlation to the update statistics task.

Wolfgang Kunk

Posted by nicofer on 11/2/2011 at 8:24 PM
I have the same issue ,is this a bug ?
Posted by Kunk on 10/25/2011 at 12:54 AM
I have some additional information. It looks like the problems only occures while a complete "update statistics" - Maintentance-Job is running. Maybe it has something to do with the load on the system. Due to users complaint about slow system response during "update statistics" we rescheduled the job to run only over the weekend. Now the deadlocks only happen during the weekends run of the maintenance-Job.
We have many other replication queues but nowwhere else we had these deadlocks before!
Posted by Microsoft on 10/24/2011 at 5:46 PM
Hi Kunk,
    
thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
SQL Server Engine Team
Sign in to post a workaround.