Can Records be Replicated in an Availability Group Out of Sequence? A data warehouse ETL process is querying a read-only secondary in an availability group. The ETL process queries a single table incrementally using datetime range criteria of a minute and read committed isolation level. At the time of execution, 5 records that meet the criteria are committed on the primary, but another 3, with slightly earlier timestamps than the first 5 (but within the criteria range) are still in open transactions. Does the nature of availability groups require all transactions to be applied in LSN order (delaying the visibility of all 8 records until all are committed) or do the delayed 3 records get later LSNs and are applied as soon as they are committed, potentially after the ETL process has adjusted its date criteria?
At the time of execution, 5 records that meet the criteria are committed on the primary, but another 3, with slightly earlier timestamps than the first 5 (but within the criteria range) are still in open transactions. Does the nature of availability groups require all transactions to be applied in LSN order...


REDO requires that LSNs be applied in sequence order, this doesn't change inside or outside of an AG. When the REDO thread is working, it'll have to do the log records in order.


  ... (delaying the visibility of all 8 records until all are committed) or do the delayed 3 records get later LSNs and are applied as soon as they are committed, potentially after the ETL process has adjusted its date criteria?


There is no delaying. In Availability Groups we don't ship individual transactions. We also don't wait for them to commit.

The unit of transport is a LOG BLOCK which is a collection of LOG RECORDS, in order. These log blocks hold log records from many different transactions in the database and not just the ETL ones you have going on. Additionally, the log blocks are sent when they are closed and flushed to disk, which happens for a few different reasons and does not necessarily need to be from a commit.

This means the secondary receives information on all transactions that are going on in the database that are making changes. In this case, the secondary has some portion, if not all, of the information about the earlier transactions that are still open.

Now, this hits on another point. Readable secondary servers aren't using read committed isolation level, in fact it is mapped to snapshot isolation under the covers. This is the reason you cannot see any information on the secondary pertaining to the earlier 3 transactions. You cannot add nolock to the query, it won't let you see the data.

Since snapshot isolation is used, we're going to use the version store. This means if you started a transaction on the secondary to read information and it is still running when the open transactions on the primary are committed, it will not "see" them until the transaction is over. This is part of the isolation of snapshot.

To summarize:


Log blocks are shipped, not log records, and NOT just committed transactions
Yes, there is information already on the secondary about those transactions
Readable secondary servers have read committed (default) isolation level mapped to snapshot isolation level automatically
If you need the data on the secondary, avoid large long running transactions