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
Last activity: Jan 7, 2025, 08:53 PM