AlwaysOn: I just enabled Readable Secondary but my query is blocked?

When you connect to Secondary Replica, but it has not been enabled for read workload, you will get the following error under two situations

(1) You connect directly to one of the databases under availability group. It is denied because the database is not enabld for read workload.

(2) You connect to a non-AG database such as master database which succeeds and now you execute ‘use <db>’ command. 

Msg 976, Level 14, State 1, Line 1

The target database, 'hadr_test', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

To connect to readable secondary database, you will need to enable the secondary replica for read workload. Once it is done, you can connect but there is one catch. Your queries will block until all active transactions active at the time of enabling readable secondary have been committed and processed on the secondary replica. This is needed to guarantee that row versions are available on the secondary replica before executing the query under snapshot isolation as all isolation levels are implicitly mapped to snapshot isolation. Here is one example to illustrate this

-- On primary replica, execute a transaction that updates all the rows in employee

-- but don't commit it

create table employee (name char (100), id int)

go

declare @i int

select @i = 0

while ( @i < 10000)

begin

 insert into employee values ('my name' + CONVERT(varchar(10), @i), 33)

 select @i = @i + 1

end

begin tran

                update employee set id = 1000

The transaction on the primary replica is still active. Now, enable secondary replica to accept read workload and issue the following query on the secondary replica. This query will get blocked

-- on secondary replica, connect to the secondary database, your connection will succeed but the

-- following query will block

select count(*) from employee

You can run query the DMV to see where the blocking is (in my example, the query above was issued on session_id 54). Note, while the queries on user data are blocked but readable secodnary still allows you to query DMVs otherwise you would not have been able to troubleshoot or understand what is going on.

select session_id, wait_type

from sys.dm_exec_requests where session_id = 54

The output is as follows. This wait stat corresponds to versioning transition.

If you now commit the transaction on the primary, the query will get unblocked once the commit log record has been processed by the secondary replica. We expect that enabling secondary replica is a very in-frequent and in a typical configuration, it would need to be done only once.

Thanks

Sunil Agarwal

Comments

• Anonymous
  December 22, 2011
  Sunil, thank you so much for getting back to writing. :) I was so impatient to see new content at your blog and now you have given us a very Merry Christmas !: )

• Anonymous
  October 13, 2013
  Hi sunil, on my read only replica, my queries are blocked by a system processid ( spid 44 ). on From Sysprocesses I understand that the system process command type was DBStartup. The read only replica was running normal for many days. The replica state is also syncrhonizing ( aysnc mode ) and healthy. Any reasons ? thanks. Regards, Raj

• Anonymous
  October 27, 2013
  Raj: I can't think of a reason but DBStartup (spid 44) is surprising as database is already up? One suggestion is to restart the secondary and see if you see any changes thanks sunil