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 statementINSERT
. - The table that is executing
INSERT
has a trigger on itINSTEAD 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?
source to share
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. :-)
source to share
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?
source to share