2

The following two links are each very explicit regarding how long running transactions on the primary impact an asynchronous secondary replica.

Asynchronous Commit

A long-running transaction on the primary replica prevents the updates from being read on the secondary replica.

All read workloads on the secondary replica are snapshot isolation queries. In snapshot isolation, read-only clients see the availability database on the secondary replica at the beginning point of the oldest active transaction in the redone log. If a transaction has not committed for hours, the open transaction blocks all read-only queries from seeing any new updates.

My testing suggests otherwise.

With SQL Server 2017 Enterprise edition, I have created an AlwaysOn Availability Group with two Availability Replicas. Both are configured with:

  • Availability Mode = Asynchronous Commit
  • Failover Mode = Manual
  • Readable Secondary = Yes

I have single table named "Test" with two columns [Id (IDENTITY+PK), Description]

  1. On the primary, I insert 'beforelongtransaction'
  2. On the primary, I start a transaction that inserts 'longtransaction'. I do not commit the transaction.
  3. On the primary, I confirm with DBCC OPENTRAN that a long running transaction is executing.
  4. On the primary, I insert 'whilelongtransaction'
  5. On the secondary, I execute "SELECT * from Test"
    • I receive the results:
    • | Id | Description |
    • | 1 | 'beforelongtransaction' |
    • | 3 | 'whilelongtransaction' |
  6. On the primary, I commit the long transaction from Step 2.
  7. On the secondary, I execute "SELECT * from Test"
    • I receive the results:
    • | Id | Description |
    • | 1 | 'beforelongtransaction' |
    • | 2 | 'longtransaction' |
    • | 3 | 'whilelongtransaction' |

Has SQL Server 2017 (or 2014/2016) removed this limitation? Or am I misunderstanding and not properly reproducing the limitation?

  • 1
    It's not blocking it but the transaction has just not been sent to the secondary until the commit is initiated on the primary. On an asynchronous replica, such as yours, the primary won't wait for log hardening on the secondary before finishing the complete. On a synchronous replica, it will. – swasheck May 18 '18 at 22:41
  • My question could probably be rephrased - "Does a single long running transaction on the primary replica, possibly an 'unimportant' transaction, cause a delay in seeing updates from all SQL commands on the secondary replica?" I'm trying more transactions, but I think the answer might be 'no' - only transactions that impact the results returned by RCSI prevent updates from being read on the secondary replica. – Marksl May 18 '18 at 23:44
3

Can a Long-running transactions on a primary replica block a secondary replica from seeing updates?

Only if it blocks the REDO thread(s) which would require the use of DDL on the primary and a workload that is blocking said DDL from acquiring the needed Sch-M lock on the same object(s).

Long running transactions don't "stop" you from seeing updates but a plethora of other items will, such as:

  1. Slow Redo
  2. Slow network
  3. Slow disk
  4. CPU/Memory pressure
  5. Synchronous/Asynchronous
  6. Readable secondary replicas use SNAPSHOT isolation under the covers

Has SQL Server 2017 (or 2014/2016) removed this limitation?

AFAIK there was never a limitation except that your log is going to grow which could cause other issues. It could also present issues with readable secondary replicas if this impacts or blocks ghost cleanup - that's generally what I've witnessed and in those cases the transactions were open on an extremely busy system for 24-48 hours. Since Snapshot isolation is used, your tempdb is going to take a hit due to version store and there is a limit. I'm not exactly sure what the quoted text you have is referring to as it doesn't explain the scenario(s).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.