I am wanting to utilize partitioning based on a [TenantId]
(and later in conjunction with date ranges). Instead of needing to manually insert the latest value within the PARTITION FUNCTION
, I thought of creating a TRIGGER AFTER INSERT
to pull the [TenantId]
value and ALTER PARTITION FUNCTION
to add it to the SPLIT RANGE
. However, I am running into an unexpected error:
Cannot execute ALTER PARTITION FUNCTION on/using table 'Tenant' since the table is the target table or part of cascading actions of a currently executing trigger.
First, I'm creating the PARTITION FUNCTION [PF_Tenant_Isolation]
and PARTITION SCHEME [PS_Tenant_Isolation]
for partitioning on the [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
Following this, I am creating the [Tenant]
table against the newly created partition scheme.
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
I seed the first value before creating the trigger.
INSERT INTO [Auth].[Tenant]
VALUES (1,'Partition Trigger Test A');
I create the trigger against the [Tenant] table.
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
I follow this up with attempting to insert the second [Tenant]
value.
INSERT INTO [Auth].[Tenant]
VALUES (1,'Partition Trigger Test B');
This is when the error described above pops up. Based the error itself as well reading the Technet arguments, I understand the issue to be in utilizing AFTER INSERT
. Since the partition action of the transaction relies on utilizing the range value within the partition function, the ALTER PARTITION SCHEME
fails, and thus the entire transaction as well.
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.
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 the IDENTITY
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 with INSTEAD 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.
Best Answer
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.
EDIT:
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.
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.