CODEWITHSUNDEEP
sqlsql-server-2008loggingdeadlock
Ash
Ash

Reputation: 1298

Deadlock vs. Logging

Web application. C#, SQL 2k8

In a list, there is a lot of things going on, before the actual Dynamic SQL gets executed, and returns the desired records.

Recently we added some code for the whole app (deadlock issues) with transaction commit, and rollback and retry X times, the whole 9 yards.

That is a big plus, we really needed that.

BUT

In order to debug any possible issues with the list, I was saving the dynamic SQL string in a Log table (which doesn't store old records just the last couple of weeks)

Problem is: right now there is no log, if the list crashes. Because of the rollback stuff ...

My best idea so far was to call the list twice:

  1. "check" - mode, will create the dynamic SQL, save it in the LOG tbl, but WILL NOT EXEC

  2. "list" - mode will either:

2a. recalculate the dynamic SQL

or

2b. reuse the dynamic SQL created by "check - mode

Either 2a or 2b, should not have a huge performance issue, because the most expensive part of the list stored procedure would be the actual execution of the dynamic sql. The "check" - mode is some basic string concatenation.

The deadlock retry part would only do the second db call, which is also good!

Still. I am not the happiest person with this idea. I wonder if there is a better way to achieve this.

Upvotes: 1

Views: 122

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

You can use (table) variables along with TRY CATCH blocks to save data during transaction. Variables are kept after rollback.

If your translations are inside stored procedure, that can look something like this:

BEGIN TRY
    BEGIN TRAN

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT 1'

    RAISERROR('A',16,1)

    EXEC (@SQL)

    COMMIT

END TRY
BEGIN CATCH
    ROLLBACK
    SELECT @SQL --WRITE TO LOG TABLE

END CATCH

And if your transactions are outside of SP - in C# code (which is in general a better idea), you can send your variable back to app with RAISERROR along with actuall error that happened. Something like this:

BEGIN TRY

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT 1'

    RAISERROR('A',16,1)

    EXEC (@SQL)

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000)
    DECLARE @ErrorSeverity INT
    DECLARE @ErrorState INT

    SELECT 
        @ErrorMessage = 'Error: ' + ERROR_MESSAGE() + '; SQL: ' + @SQL,
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE()

    RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState)

END CATCH

Upvotes: 1

Related Questions