存储过程中使用分区切换遇权限错误及参数配置疑问咨询

错误信息 #

User does not have permission to use the ABORT_AFTER_WAIT = BLOCKERS option.

用户已拥有ALTER ANY权限，但执行带WAIT_AT_LOW_PRIORITY且ABORT_AFTER_WAIT = BLOCKERS的分区切换语句时触发上述错误，相关执行代码如下：

BEGIN TRY
           BEGIN TRANSACTION
                DELETE FROM TableBackup
                
                ALTER TABLE TableMain SWITCH PARTITION 1 
                         TO TableBackup PARTITION 1
                       WITH ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ));  

                ALTER TABLE TableTemp SWITCH PARTITION 1 
                         TO TableMain PARTITION 1;
            COMMIT
        END TRY

疑问解答 #

1. 是否需要为第二条分区切换语句也添加WITH ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ))？

• 并非强制要求，完全取决于业务场景：
  • 如果第二条分区切换操作也存在被长时间锁阻塞的风险，且你希望控制等待行为，就可以添加该选项；
  • 如果该操作通常能快速执行、不会出现阻塞，不加也不会影响功能。
• 注意：只要使用ABORT_AFTER_WAIT = BLOCKERS，就会触发对应的权限检查，添加后同样会面临当前的权限问题。

2. 是否应考虑将ABORT_AFTER_WAIT = BLOCKERS修改为ABORT_AFTER_WAIT = NONE？

• 这是解决当前权限错误的有效方案之一：
  • ABORT_AFTER_WAIT = NONE表示在MAX_DURATION等待时长结束后，操作会回到正常的锁等待队列，不会主动终止阻塞它的会话，该选项仅需ALTER权限即可使用，无需额外权限；
  • 如果你确实需要终止阻塞会话的能力，那么需要给用户授予ALTER ANY CONNECTION权限——因为ABORT_AFTER_WAIT = BLOCKERS要求用户具备终止其他会话的权限，而ALTER ANY权限并不包含这一权限。

内容的提问来源于stack exchange，提问作者Aiswarya Rajagopalan