Sample Header Ad - 728x90

Question on Long-Running Transactions and Secondary Replica Visibility

0 votes
1 answer
58 views
I have an Availability Group (AG) setup and tried simulating a long-running transaction on the primary. Here’s what I did: On the primary replica, I ran the following commands:
BEGIN TRAN;  
INSERT INTO employees VALUES (1);
In a new tab on the primary, I created a new table (Table1) and inserted some records into it:
CREATE TABLE Table1 (ID INT, Name NVARCHAR(50));  
INSERT INTO Table1 VALUES (1, 'Test');
On the secondary replica, I ran:
SELECT * FROM Table1;
This query on the secondary replica returned the data, showing the changes made in Table1. However, according to the Microsoft documentation > If a transaction has not committed for hours, the open transaction blocks all read-only queries from seeing any new updates. This behavior seems to suggest that the impact of a long-running transaction is database-wide, meaning we should not be able to access any table on the database from the secondary replica when there’s an active, long-running transaction on the primary. Can you clarify if the behavior described in the documentation is database-level, and if so, why was I able to see changes in Table1 on the secondary replica?
Asked by Shahezar khan (1 rep)
Jan 7, 2025, 05:16 PM
Last activity: Jan 7, 2025, 08:53 PM