利用觸發器動態改變分區函式
我想利用基於 a 的分區
[TenantId]
(稍後結合日期範圍)。PARTITION FUNCTION
我不需要TRIGGER AFTER INSERT
在.[TenantId]
_ALTER PARTITION FUNCTION
_SPLIT RANGE
但是,我遇到了一個意外錯誤:無法在/使用表“租戶”上執行 ALTER PARTITION FUNCTION,因為該表是目標表或目前正在執行的觸發器的級聯操作的一部分。
首先,我正在創建
PARTITION FUNCTION [PF_Tenant_Isolation]
andPARTITION SCHEME [PS_Tenant_Isolation]
用於在[TenantId]
.CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int]) AS RANGE LEFT FOR VALUES (1); GO CREATE PARTITION SCHEME [PS_Tenant_Isolation] AS PARTITION [PF_Tenant_Isolation] ALL TO ([Auth]); GO
在此之後,我將
[Tenant]
針對新創建的分區方案創建表。IF OBJECT_ID('[Auth].[Tenant]', 'U') IS NULL BEGIN CREATE TABLE [Auth].[Tenant] ( [TenantId] [int] IDENTITY(1,1) ,[TenantActive] [bit] NOT NULL CONSTRAINT [DF_Tenant_TenantActive] DEFAULT 1 ,[TenantName] [varchar](256) NOT NULL ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC) ) ON [PS_Tenant_Isolation]([TenantId]); END
我在創建觸發器之前播種了第一個值。
INSERT INTO [Auth].[Tenant] VALUES (1,'Partition Trigger Test A');
我創建了針對
$$ Tenant $$桌子。
CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant] AFTER INSERT AS BEGIN DECLARE @MaxInsertedId int SET @MaxInsertedId = (SELECT MAX([TenantId]) FROM inserted) ALTER PARTITION SCHEME [PS_Tenant_Isolation] NEXT USED [Auth]; ALTER PARTITION FUNCTION [PF_Tenant_Isolation]() SPLIT RANGE (@MaxInsertedId); END
我繼續嘗試插入第二個
[Tenant]
值。INSERT INTO [Auth].[Tenant] VALUES (1,'Partition Trigger Test B');
這是彈出上述錯誤的時候。基於錯誤本身以及閱讀Technet 論點,我了解使用中的問題
AFTER INSERT
。由於事務的分區動作依賴於利用分區函式內的範圍值,ALTER PARTITION SCHEME
因此失敗,因此整個事務也是如此。AFTER 指定僅當觸發 SQL 語句中指定的所有操作都已成功執行時才觸發 DML 觸發器。在觸發此觸發器之前,所有引用級聯操作和約束檢查也必須成功。
I have looked into INSTEAD OF INSERT but have not had any success. The trigger fires once and updates the
SPLIT RANGE
with a value of 0 (implicitly converted from NULL). I believe this is due to theIDENTITY
not being properly captured in the scope of the transaction.CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant] INSTEAD OF INSERT AS BEGIN DECLARE @MaxInsertedId int SET @MaxInsertedId = (SELECT [TenantId] FROM inserted) ALTER PARTITION SCHEME [PS_Tenant_Isolation] NEXT USED [Auth]; ALTER PARTITION FUNCTION [PF_Tenant_Isolation]() SPLIT RANGE (@MaxInsertedId); INSERT INTO [Auth].[Tenant] ([TenantActive], [TenantName]) SELECT [TenantActive], [TenantName] FROM inserted; END
Subsequent row inserts into
[Tenant]
produce an additional error due to it trying to enter 0 (NULL).Duplicate range boundary values are not allowed in partition function boundary values list. The boundary value being added is already present at ordinal 1 of the boundary value list.
How can I work around this? Do I need to explicitly set the
IDENTITY
value of[TenantId]
in conjunction withINSTEAD OF INSERT
? New inserts into[Tenant]
will be rather sporadic and minimal, but[TenantId]
will be a constraining key across other tables. This is why I decided to investigate this implementation method so to dynamically alter the partition function.
The cryptic error in the
AFTER
trigger is due to performing a DDL against the trigger target table. With theINSTEAD OF
trigger, you would need to perform theINSERT
to get the assignedIDENTITY
value and then split the partition function. However, you probably don’t want to use IDENTITY here anyway since those may have gaps that are sometimes large and result in an untidy partition boundary list.Below is an example that ditches the IDENTITY and uses a RANGE RIGHT function, which I believe is more natural for incremental partition boundaries. This version validates exactly one row is inserted but could be extended to handle multi-row inserts if needed. Your use case as I understand it suggests only rare singleton inserts.
--start with no partition boundaries CREATE PARTITION FUNCTION [PF_Tenant_Isolation] ([int]) AS RANGE RIGHT FOR VALUES (); GO CREATE PARTITION SCHEME [PS_Tenant_Isolation] AS PARTITION [PF_Tenant_Isolation] ALL TO ([Auth]); GO CREATE TABLE [Auth].[Tenant] ( [TenantId] [int] NOT NULL ,[TenantActive] [bit] NOT NULL CONSTRAINT [DF_Tenant_TenantActive] DEFAULT 1 ,[TenantName] [varchar](256) NOT NULL ,CONSTRAINT [PK_Tenant_TenantId] PRIMARY KEY CLUSTERED ([TenantId] ASC) ) ON [PS_Tenant_Isolation]([TenantId]); GO CREATE TRIGGER [TR_Tenant_Isolation] ON [Auth].[Tenant] INSTEAD OF INSERT AS DECLARE @TenantId int; BEGIN TRY --Get next TenantId and exclusively lock table to prevent deadlocking during DDL. --If other tables are partitoned via this function, add code to get exclusive locks on those too. SELECT TOP(1) @TenantId = COALESCE(MAX(TenantId),0) + 1 FROM [Auth].[Tenant] WITH(TABLOCKX); INSERT INTO [Auth].[Tenant] ([TenantId], [TenantActive], [TenantName]) SELECT @TenantId, [TenantActive], [TenantName] FROM inserted; IF @@ROWCOUNT <> 1 BEGIN RAISERROR('Exactly one row must be inserted into Auth.Tenant at a time',16,1); END; ALTER PARTITION SCHEME [PS_Tenant_Isolation] NEXT USED [Auth]; ALTER PARTITION FUNCTION [PF_Tenant_Isolation]() SPLIT RANGE (@TenantId); END TRY BEGIN CATCH; THROW; END CATCH; GO INSERT INTO [Auth].[Tenant]([TenantActive], [TenantName]) VALUES (1,'Partition Trigger Test A'); GO
EDIT:
I see your notation, but given that queries will read from
$$ Tenant $$, wouldn’t the opposite happen where this would actually cause deadlocks?
The course-grained X lock on the Tenant table will wait for (be blocked by) other concurrent activity against the table to complete and, once granted, block other activity against the table. This blocking will prevent deadlocks on the Tenant table during the DDL operation within the trigger transaction. The duration of the SPLIT itself will be fast since rows are not moved between partitions. The duration of blocking before the initial block X lock is granted will depend on how long the other queries run.
In the case of multiple tables (i.e. related tables partitioned by schemes based on the same function), deadlocks can still occur if the locking order in the trigger is different than that of other activity. An exclusive lock on those tables too in the trigger can only mitigate the likelihood of deadlocks in that case. For example, if you have a SELECT query that joins Tenant and TenantDetails, both partitioned similarly, a deadlock may occur if the query acquires locks those tables in the reverse order as the trigger.
Also, I understand with partition schemes you typically want to leave partitions on the left and right boundaries that are “empty” for proper switching.
Empty partitions are a consideration for
SPLIT
andMERGE
but notSWITCH
. With theSPLIT
in the trigger, the split partition is always empty so no expensive data movement is needed to conform to the new boundary specification.The general best practice is to
MERGE
boundaries when both adjacent partitions are empty. That said, you can sillMERGE
without row movement as long as the partition containing the boundary (one on the right with aRANGE RIGHT
function) is empty.