I'm writing a database trigger on Table A. In said trigger, I insert a new record into Table B by doing:
- Look up the PK ID of Table B
- Increment the PK ID and insert new row into Table B
(Note: Table B does not use Identity. That is, the PK ID is not auto-incrementing. This is not something I can change. Reason being is the database schema is vendor-controlled. They don't use identity in any of their tables (auto-incrementing). Vendor keeps track of the next ID when it inserts records. That is how they designed their app. I want to be consistent with their design practices, as veering off course and adding identity to the table could break during a patch or upgrade cycle.)
I want to ensure two things during my transaction:
- Avoid deadlocks on Table B
- Prevent other insert queries/transactions from inserting into Table B (PK ID should be consistent)
Keep in mind, in my use case, the PK ID is not an integer but a varchar; it is an alphanumeric value with a 3-letter prefix followed by several digits.
Here is my code:
CREATE TRIGGER [dbo].[AfterINSERT_TableA_Trigger]
ON [dbo].[TableA]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- [Table A code goes here]
-- Table B code
-- Instruct SQL Server to rollback entire transaction and abort the batch when a run-time error occurs
SET XACT_ABORT ON;
BEGIN TRANSACTION TableB
-- Ex: SUB1234567890
DECLARE @subID VARCHAR(15)
DECLARE @newSubID VARCHAR(15)
-- Lookup last PK ID inserted into Table B
SELECT
@subID = submissionID
FROM
TableB WITH (TABLOCKX, HOLDLOCK)
ORDER BY
submissionID DESC
-- Increment PK ID, separating prefix, increment by 1, and concatenate
SELECT @newSubID = LEFT(@subID, 3) + CAST((RIGHT(@subID, LEN(@subID) - 3) + 1) AS VARCHAR(15))
-- Insert new record into TableB
INSERT TableB WITH (TABLOCKX, HOLDLOCK)
(SubmissionID, Description, Status)
VALUES
(@newSubId, 'Test', 0)
COMMIT TRANSACTION TableB
-- Turn off rollback flag
SET XACT_ABORT OFF;
END
I read that HOLDLOCK is equivalent to SERIALIZABLE (https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15), so I'm not sure it makes a difference which one I use.
Does the code look OK such that it'll satisfy the criteria of avoiding deadlocks and blocking other insert transactions on Table B while my transaction is running? Do I need the WITH locks on both the SELECT and the INSERT statements? Should I be using Read Committed or similar at the transaction level instead of or in addition to?
To be clear, I don't want other insert transactions on the table to necessarily fail. I just want them to wait their turn until my transaction is complete, while keeping the PK ID column consistent.