ousoonerjoe
Programmer
Using SQL Server 2008 R2
I have an intermittent deadlock that happens on one of our audit tables. From what I understand of Row Locking, it shouldn't be happening at all here. All our stored procedures have the following layout (trimmed for ease of debugging). Now with all things being equal, the -INSERT Audit- should never deadlock, right?
Any suggestions or thoughts are appreciated.
Thank you.
--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------
I have an intermittent deadlock that happens on one of our audit tables. From what I understand of Row Locking, it shouldn't be happening at all here. All our stored procedures have the following layout (trimmed for ease of debugging). Now with all things being equal, the -INSERT Audit- should never deadlock, right?
Code:
CREATE PROCEDURE cst_Insert_Template(
@Argument DataType)
AS
DECLARE @NewId INT;
[COLOR=#3465A4]BEGIN TRY
BEGIN TRANSACTION[/color];
INSERT INTO Audit(ProcName, ...)
VALUES(OBJECT_NAME(@@PROCID), ...);
[COLOR=#3465A4]COMMIT TRANSACTION;
BEGIN TRANSACTION;[/color]
INSERT INTO TableX ()
VALUES or SELECT...
SET @NewId = SCOPE_IDENTITY();
IF @NewId < 1
BEGIN
RAISERROR(60000, 16, 1, 'Insert failed...');
END
[COLOR=#3465A4]COMMIT TRANSACTION;
END TRY
BEGIN CATCH[/color]
If @@TRANCOUNT > 0
BEGIN
[COLOR=#3465A4]ROLLBACK TRANSACTION[/color]
END
INSERT INTO ErrLog (Number, Severity, ErrState, ErrSource, ErrLine, ErrMsg, Form, AppID)
SELECT ISNULL(ERROR_NUMBER(),-1), ISNULL(ERROR_SEVERITY(),0), ISNULL(ERROR_STATE(),0),
ISNULL(ERROR_PROCEDURE(),'Unknown.'), ISNULL(ERROR_LINE(),0),
ISNULL(ERROR_MESSAGE(),'ROLLBACK: Failed to Insert TableX Record.'), OBJECT_NAME(@@PROCID), 1;
[COLOR=#3465A4]END CATCH[/color]
Any suggestions or thoughts are appreciated.
Thank you.
--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------