SQL Server 2017 Availability Groups - Can a Long-running transactions on a primary replica block a secondary replica from seeing updates?

Question

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

• https://technet.microsoft.com/en-us/library/dn135335(v=sql.110).aspx
• https://blogs.msdn.microsoft.com/alwaysonpro/2015/01/06/troubleshooting-redo-queue-build-up-data-latency-issues-on-alwayson-readable-secondary-replicas-using-the-wait_info-extended-event/

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?

Answer 1

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).