Sql-server – Sleeping SPID blocking other transactions

sql-server-2008transactiontroubleshooting

I'm really having trouble tracking down some blocking we are experiencing.

The root blocking SPID's status is 'sleeping', the cmd is 'AWAITING COMMAND', and the sqltext is SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

When I view the Top Transactions by Blocked Transactions Count report, the Blocking SQL Statement is '–'.

I've performed a trace on the SQL and when the blocking happens tracing the root blocking SPID but it hasn't really led me anywhere. The last trace statement is the same as the sqltext above SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

I've checked all the related stored procedures I can find to make sure they have TRY/CATCH BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN statements (we use stored procedures for everything so there are no standalone statements being ran). This issue just started happening over the last 24 hours and no one is claiming to have made any changes to the system.

Solution: one of our seldomly used stored procedures had an error with an insert (number of columns didn't match), but we are still confused on what exactly was happening.

When looking at all the trace information, the EXEC statement for this stored procedure was listed at times, but NEVER just before the BLOCK happened on the blockking SPID. It seemed that when it starting blocking, the trace didn't record the execution of it (or any of the statements within it either). However there are other times were the trace did record it's execution and no blocking occurred.

The stored procedure error report came from a user, and I was able to find multiple EXEC statements in traces and run them in SSMS. No time when I ran them did we have any blocking occur or did they hang. They ran as expected (the catch block fired and rolled back the transaction after the error). After resolving the fixing the stored procedure, we have not seen the issue again.

Best Answer

From comments, I'm guessing you had a client side Command timeout that has aborted the SQL query. This does not rollback the transaction because the connection stays open on SQL Server due to connection pooling.

So, you need to use SET XACT_ABORT ON or add some client rollback code

See SQL Server Transaction Timeout for all the gory details

Related Solutions

Sql-server – how to make a table unlocked within transaction block

You'd have to do it outside of the transaction

For example: Spool the data in a table variable which don't participate in transactions and write once at the end (after commit). Or use Service Broker to decouple log writes.

Any logging otherwise is subject to the transaction semantics eg locks, can be rolled back etc

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Sql-server – how to make a table unlocked within transaction block

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

Related Question