The below code is in a stored procedure. It is called by different users. when the #tempTABLE is huge (more than 160k), we see deadlocks that cause errors ProcessControl, when multiple users are running the stored procedure to
could you suggest what are the best standards that can be applied to handle this graciously?
BEGIN TRAN InsertBlockFromTemp
INSERT INTO [dbo].ProcessControl WITH (ROWLOCK) ( 9 COLUMNS )
SELECT 9 columns FROM #tempTABLE ORDER BY IDENTITYColumnPK_Column
COMMIT TRAN InsertBlockFromTemp