none
querying a database that has replication subscriber tables RRS feed

  • Question

  • Hi we run 2017 standard. 

    we are wondering if queries run against replication subscriber tables can interfere (ie block) with the replication process.   and if the opposite is true as well, ie the replication process can block the queries.

    and can read committed snapshot isolation be turned on a subscribing (replication) database?...theoretically to avoid any locking that the query (or replication) might create.

    we don't really want to query with nolock.

    • Moved by Tom Phillips Thursday, August 15, 2019 4:57 PM Replication question
    Thursday, August 15, 2019 12:27 PM

Answers

  • Hi db042...,

    yes - vice verca an blockings occur. It all depends on the workload.

    The distributor will not ship transactions but DML-Operations to the subscribers. This is exactly the same as when an individual will enter data or update data.

    On the other hand a select statement can surely block DML-Operations based on the isolation level.

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        SELECT * FROM TABLE WHERE ...

    The above code will hold S-Locks as long as the transaction is open!

    RCSI can help as long as READ COMMITTED isolation level is used.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    • Marked as answer by db042190 Tuesday, August 20, 2019 12:36 PM
    Thursday, August 15, 2019 1:36 PM
  • SQL Server transactional replication replicates transactions. A transaction on the publisher will be replicated as a transaction on the subscriber. Long running transaction on replicated tables on the publisher which modify tables will hold transactions on the subscriber tables as well. 

    Transactional replication also replicates changes in batches.

    Both of these have the potential to cause blocking on the subscriber with user processes running there. Using RCSI may minimize these changes.

    For short running transactions occurring on the publisher you will not find much blocking occurring on the subscriber.

    Thursday, August 15, 2019 5:43 PM
    Moderator
  • I have not worked a whole lot with replication, but I can't but see that the scenario is not very different from an enivironment where some processes reads data, and others write data. The only difference is that there is only a single input source, but the again, there are plenty of such applications too.

    Whether you need to worry about deadlocks, I don't know, because that depends on the workload. And the available indexes.

    If the queries are run in bursts once an hour, it would be attractive to pause replication during this window, but I don't know if this is possible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Tuesday, August 20, 2019 12:36 PM
    Thursday, August 15, 2019 9:24 PM
  • If you do a large batch update it will cause havoc on the subscriber. If your transaction is held for a long time and only modifies 10 rows on the subscriber, but does a tonne of reads on the publisher, all that would be replicated on the subscriber would be a transaction that modified 10 rows.

    So, it depends on your transaction, but yes, there is the potential for it to affect the subscriber negatively.

    • Marked as answer by db042190 Tuesday, August 20, 2019 12:36 PM
    Monday, August 19, 2019 7:46 PM
    Moderator

All replies

  • Hi db042...,

    yes - vice verca an blockings occur. It all depends on the workload.

    The distributor will not ship transactions but DML-Operations to the subscribers. This is exactly the same as when an individual will enter data or update data.

    On the other hand a select statement can surely block DML-Operations based on the isolation level.

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        SELECT * FROM TABLE WHERE ...

    The above code will hold S-Locks as long as the transaction is open!

    RCSI can help as long as READ COMMITTED isolation level is used.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    • Marked as answer by db042190 Tuesday, August 20, 2019 12:36 PM
    Thursday, August 15, 2019 1:36 PM
  • thx , so if I get more interested in things that cause errors to occur,  I see at https://social.msdn.microsoft.com/Forums/en-US/bcb08826-ef9b-41de-8355-909954d258ad/querying-a-database-that-has-replication-subscriber-tables?forum=sqldatabaseengine that S (shared, read thing) is red when running up against an X (exclusive) lock and undesirable when run up against an IX (more a child thing like page related to the X etc).  From what you say, I'm assuming replication causes X locks all the time.   But i'm also guessing that red doesn't necessarily cause an error to occur. 

    In our environment the queries (they essentially do etl only and wake up only once an hour currently) in question run up against one subscriber table at a time (ie no joins).  And replication occurs every 15 minutes.

    Do I need to worry about errors (eg deadlocks) happening? If yes, what can I do to avoid them?   

     
    Thursday, August 15, 2019 2:57 PM
  • I think I see part of the answer at https://sqlperformance.com/2014/04/t-sql-queries/the-read-committed-isolation-level .  It looks like an exclusive lock cant even occur on the same data if a shared lock is already present, at least in an environ where the read committed isolation level (default) is used.  And the opposite cant occur either. 

    so i'm starting to think that deadlocks can only occur if the order of the read targets and replication targets conflict in such a way as to make them both wait for the same piece of data after they've already succeeded in doing part of what they intended to do.  And I suppose adhoc queries can create all sorts of havoc too. 

    is there a way to predict what sequence replication will use in distributing its updates?   and perhaps fashion etl queries to never create a conflict based on that rule of thumb?  

    or maybe it makes sense to set the tran isolation level to serializable (just during query) given the very restricted use of the queries.



    • Edited by db042190 Thursday, August 15, 2019 5:19 PM clarity
    Thursday, August 15, 2019 5:08 PM
  • SQL Server transactional replication replicates transactions. A transaction on the publisher will be replicated as a transaction on the subscriber. Long running transaction on replicated tables on the publisher which modify tables will hold transactions on the subscriber tables as well. 

    Transactional replication also replicates changes in batches.

    Both of these have the potential to cause blocking on the subscriber with user processes running there. Using RCSI may minimize these changes.

    For short running transactions occurring on the publisher you will not find much blocking occurring on the subscriber.

    Thursday, August 15, 2019 5:43 PM
    Moderator
  • I have not worked a whole lot with replication, but I can't but see that the scenario is not very different from an enivironment where some processes reads data, and others write data. The only difference is that there is only a single input source, but the again, there are plenty of such applications too.

    Whether you need to worry about deadlocks, I don't know, because that depends on the workload. And the available indexes.

    If the queries are run in bursts once an hour, it would be attractive to pause replication during this window, but I don't know if this is possible.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by db042190 Tuesday, August 20, 2019 12:36 PM
    Thursday, August 15, 2019 9:24 PM
  • thx Hilary but if we have hundreds of tables being replicated on each subscribing db, wouldn't the sum (I assume that's a batch) of the individual trans from each table potentially create havoc equal to a publisher having long running trans?
    Friday, August 16, 2019 1:21 PM
  • If you do a large batch update it will cause havoc on the subscriber. If your transaction is held for a long time and only modifies 10 rows on the subscriber, but does a tonne of reads on the publisher, all that would be replicated on the subscriber would be a transaction that modified 10 rows.

    So, it depends on your transaction, but yes, there is the potential for it to affect the subscriber negatively.

    • Marked as answer by db042190 Tuesday, August 20, 2019 12:36 PM
    Monday, August 19, 2019 7:46 PM
    Moderator