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://docs.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.