Clever Geek Handbook

SQL Server errors in triggers that lock a table orphaned with SqlDataAdapter and external transaction

Ok, so I ran into a rather strange circumstance. There are several layers in my situation. I haven't determined if each level is strictly required, but here's what happens:

  • The C # code creates an external transaction, which is automatically posted SqlConnection

    .
  • The C # code uses SqlDataAdapter

    to insert a row into a table.
  • InsertCommand

    refers to a stored procedure. A stored procedure is a simple statement INSERT

    .
  • The table that is executing INSERT

    has a trigger on it INSTEAD OF INSERT

    .
  • The trigger acquires an exclusive lock on the table.
  • An error occurs inside the trigger.

With this connection, no error occurs in C # code. However, if the trigger does not acquire an exclusive lock on the table, the error does so before C # code.

The error actually occurs, although this is confirmed by the fact that on the SQL Server side, the transaction was aborted. The C # code does not know that the transaction was aborted and only encounters an error when the delete TransactionScope

tries COMMIT TRANSACTION

.

I created a minimal reproduction of this script:

https://github.com/logiclrd/TestErrorWhileLockedInTrigger

Can anyone understand why this is possible, and how can the correct error handling behavior be restored?

+3


source to share


2 answers


I have identified the cause of the problem.

The statement in the table lock trigger looks like this:

SELECT TOP 0 *
  FROM TableToTriggerAndLock WITH (TABLOCKX, HOLDLOCK)

As long as this does not return data, it returns an (empty) result set. It turns out that the class SqlDataAdapter

only cares about the first result set that is returned in the TDS stream, so the error returned in the second result set is completely passed on.



Take out the lock statement and pop out that redundant result set and now the error is in the first result set.

So the solution is to suppress the result set, which I did by reworking the locking statement as:

DECLARE @Dummy INT

SELECT TOP 0 @Dummy = 1
  FROM TableToTriggerAndLock WITH (TABLOCKX, HOLDLOCK)

Hope it helps someone to work with SqlDataAdapter

and more complex basic operations. :-)

0


source


So I did some more tests.

My first thought was that if holding on to an exclusive lock causes it to suppress an error, perhaps when explicitly releasing the lock would unsquelch it? So, I put TRY

/ CATCH

around an expression that would generate an error in my proof of concept if there was one ROLLBACK TRANSACTION

, and then rec THROW

, but did nothing.



So my next thought was that the statement RAISERROR

, when used with severity levels 20-25, forcibly terminates the connection. I'm not sure if this is the ideal solution because it also writes an entry to the SQL Server event log when this happens. However, it achieves the goal of SqlDataAdapter

seeing an error during its command Update

instead of C # code, assuming the transaction is still active and trying to commit.

Does anyone know of any other potential downsides to this sledgehammer approach, or perhaps this is the only way to get an error in this case?

+1


source






More articles:


All Articles