Feature Description
When setting super_read_only when demoting a primary during PRS, the statement must wait for any in-flight DML/DDL to release MDLs. In the case of a long-running transaction, this can cause this step of the PRS to exceed the 15 second RPC timeout, at which point the connection will be attempted to be killed. The result of this is indeterminate, and setting super_read_only can succeed at an indeterminate point in the future, causing write unavailability on the primary.
Using a short lock_wait_timeout will allow the PRS to fail fast and gracefully, without causing an impact to write availability.
Use Case(s)
A primary might have a long-running ALTER running that will block super_read_only. Without a lock_wait_timeout, the call can stall and enable super_read_only at an indeterminate point in the future, long after the PRS had timed out. With a lock_wait_timeout, the statement would time out and the PRS would fail fast and gracefully with no impact to write availability.
Feature Description
When setting
super_read_onlywhen demoting a primary during PRS, the statement must wait for any in-flight DML/DDL to release MDLs. In the case of a long-running transaction, this can cause this step of the PRS to exceed the 15 second RPC timeout, at which point the connection will be attempted to be killed. The result of this is indeterminate, and settingsuper_read_onlycan succeed at an indeterminate point in the future, causing write unavailability on the primary.Using a short
lock_wait_timeoutwill allow the PRS to fail fast and gracefully, without causing an impact to write availability.Use Case(s)
A primary might have a long-running
ALTERrunning that will blocksuper_read_only. Without alock_wait_timeout, the call can stall and enablesuper_read_onlyat an indeterminate point in the future, long after the PRS had timed out. With alock_wait_timeout, the statement would time out and the PRS would fail fast and gracefully with no impact to write availability.