Post reply

sql replication and deadlocking problems

  • May 18, 2009 at 10:50 am

    #205013

    Hi Guys,

    I was looking at a problem today and it relates to SQL replication and a bunch of subscribers, what generally happens is that when subscribers received a publication from the publisher, especially when it stems from a big insert e.g. bulk insert, massive data uploads etc.

    It tends to cause deadlocking problems on the subscribers, as the subscribers are entirely for reporting. I was wondering what could be done to alleviate the problem or potentially a fix for it.

  • May 18, 2009 at 11:36 am

    #996525

    You could try "dirty reads" on Subscriber side - it would only work if such arrangement is acceptable to users. Or you could try to do bulk updates only during least impact hours.

  • May 18, 2009 at 1:39 pm

    #996596

    Thanks for the reply, dirty reads is not an option.

  • May 19, 2009 at 3:35 am

    #996795

    Any other options guys

  • May 19, 2009 at 7:46 am

    #996963

    Dean Jones (5/19/2009)

    Any other options guys

    A little lost for options here guys.

  • May 19, 2009 at 9:37 am

    #997117

    I hope we can change sync mode from continuosly to scheduled.


    - Pradyothana     http://www.msqlserver.com

  • May 19, 2009 at 1:47 pm

    #997236

    here is an option that you can try.... But test it out please.

    SET READ_COMMITTED_SNAPSHOT ON;

    SET ALLOW_SNAPSHOT_ISOLATION ON;

    You could use Row level version for the Subscriber. That would mean that your Selects will return the last committed value without disrupting the replication.

    I wrote an article regarding Row Level version[/url]. I am not sure if that will work for you but test it out in a test environment and see.

    -Roy

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply