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]
- On the primary, I insert 'beforelongtransaction'
- On the primary, I start a transaction that inserts 'longtransaction'. I do not commit the transaction.
- On the primary, I confirm with DBCC OPENTRAN that a long running transaction is executing.
- On the primary, I insert 'whilelongtransaction'
- On the secondary, I execute "SELECT * from Test"
- I receive the results:
- | Id | Description |
- | 1 | 'beforelongtransaction' |
- | 3 | 'whilelongtransaction' |
- On the primary, I commit the long transaction from Step 2.
- 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?
Best Answer
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:
SNAPSHOT
isolation under the coversAFAIK 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).