SQL Server errors in trigger that locks table lost with SqlDataAdapter and ambient transaction

Question

Okay, so I've run into a rather bizarre circumstance. There are several layers to my situation. I haven't identified whether every layer is strictly required, but here's what's going on:

• C# code is creating an ambient transaction, into which a SqlConnection is automatically enlisting.
• C# code is using a SqlDataAdapter to insert a row into a table.
• The InsertCommand is referencing a stored procedure. The stored procedure is a straightforward INSERT statement.
• The table into which the INSERT is being done has a trigger on it INSTEAD OF INSERT.
• The trigger obtains an exclusive lock on the table.
• An error occurs within the trigger.

With this conjunction, the error is not raised within the C# code. However, if the trigger does not obtain an exclusive lock on the table, the error does make it up to the C# code.

The error is actually happening, though, evidenced by the fact that on the SQL Server side, the transaction has been aborted. The C# code doesn't know that the transaction has been aborted, and only encounters an error when the disposal of the TransactionScope tries to COMMIT TRANSACTION.

I have created a minimal reproduction of this scenario:

https://github.com/logiclrd/TestErrorWhileLockedInTrigger

Does anyone have any understanding of why this might be, and how proper error handling behaviour might be restored?

Answer 1

I have identified the cause of the problem.

The statement in the trigger locking the table looked like this:

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

While this returns no data, it does return an (empty) result set. It turns out the SqlDataAdapter class only cares about the first result set it gets back on the TDS stream, so the error coming back in the second result set is completely passed over.

Take out the locking statement, and you take out that redundant result set, and now the error is in the first result set.

The solution, then, 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 this helps someone out there working with SqlDataAdapter and more complicated underlying operations. :-)