Partager via

AlwaysOn: Minimizing blocking of REDO thread when running reporting workload on Secondary Replica

  • Article

In earlier blog https://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-impact-on-the-primary-workload-when-you-run-reporting-workload-on-the-secondary-replica.aspx, I discussed the impact on RTO in case the REDO thread gets blocked. While designing this feature, it was one of the key usability constraints we had (i.e. to eliminate REDO blocking for common usage scenario). For the un-initiated, here is the problem

 

Primary Replica

Secondary Replica

 

Set Isolation level Repeatable Read

 

Begin Tran

 

Select T1.C1 from T1 where T1.C2 = 10

-- this takes a S lock on the qualifying data rows

Begin Tran

 

Update T1 set T1.C3 = <some-value>

Where T1.C2 = 10

--This statement will not block because the

-- S lock is held on the secondary replica, not on

-- primary replica

 

Commit

REDO applies the UPDATE. To do this update, it needs to acquire an X lock on qualifying rows but it will get blocked because the read transaction holds a S lock on the row

 

As you can guess, this will be a common scenario and we need to ensure that REDO thread is not blocked. A blocked REDO thread can compromise the RTO and/or can prevent cleaning up the log on the primary replica. To avoid this blocking completely, all transaction isolation levels used in an application on the secondary replica are mapped transparently to Snapshot Isolation as shown in the table below including the side effects without requiring any changes to the reporting application.

 

Isolation Level

Mapped To

Impact on workload

Read Uncommitted (RU)

Snapshot Isolation (SI)

None

Read Committed (RC)

Snapshot Isolation (SI)

None. Since SI eliminates blocking, if the read-only application depends on blocking, it will not work as expected. However, we do not think that read-only application that will depend on blocking. If it does, then part of the application logic depends on a DML activity somewhere which by definition means that it is not a read-only application.

Repeatable Read (RR)

Snapshot Isolation (SI)

SI is a higher isolation level than RR in the sense that it eliminates phantoms. We don’t think a read-only application will depend on phantoms. If it does, then part of the application logic depends on a DML activity somewhere which by definition means that it is not a read-only application.

Snapshot Isolation (SI)

Snapshot Isolation (SI)

None

Serializable (SR)

Snapshot Isolation (SI)

Transactions running under SI are not guaranteed to be serializable in its most general form. However, for read-only workload, SI offers the same Serializable guarantee.

 

You may ask how about locking hints? Well, all locking hints are ignored. While transparent mapping to Snapshot Isolation level should work for most reporting workloads, we recommend you to test your workload before deploying in production.

 One concern that you will have is that is the impact of mapping reporting workload to Snapshot Isolation on the primary replica and also how does it change the space usage in TempDB? This is the topic of my next blog https://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-impact-of-mapping-reporting-workload-to-snapshot-isolation-on-readable-secondary.aspx. While mapping reporting workload to snapshot isolation eliminates blocking between DML workload as applied by the REDO thread on the secondary replica and the read or reporting workload, it does not eliminate the potential blocking of REDO thread when it is executing a DDL operation. I will discuss this in more details in a subsequent blog.

 

Thanks

Sunil Agarwal

Comments

  • Anonymous
    February 21, 2012
    Hi Sunil Please can you advise how to configure your secondary replica to have SI whilst your Primary is using RC ? Regards Donovan

  • Anonymous
    May 29, 2012
    why bother ? there is no lock in a readonly db

  • Anonymous
    May 29, 2012
    for a readonly db , there is no point of a s-lock , coz no x-lock will be required

  • Anonymous
    February 04, 2013
    Great blog. A question regarding the internal mapping you, and others are speaking of. If I'd understood correctly this is automatic, and if so our need for further involvment is only when the blocking occurs in spite of the mapping having taken place?