Well, I have just finished a series of articles about unit testing on simple-talk.com, and some techniques were not included, just to keep the articles short. Still problems such as reproducing a lock timeout and unit testing the retry after it (lock timeout) are interesting, at least to me, so I am continuing.

You will need the database StressTests, which can be downloaded at the following URL:

 

http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---reproduce-database-errors/

 

You will also need the following stored procedure which logs the timeout and retries at most five times:

 

CREATE PROCEDURE [Writers].[UpdateUserWithRetry]

  @UserID INT,

  @FirstName VARCHAR(8),

  @LastName VARCHAR(8),

  @Position VARCHAR(8)

AS

SET NOCOUNT ON

DECLARE @ret INT, @succeeded INT, @attemptNumber INT;

SELECT @ret = 0, @succeeded = 0, @attemptNumber = 1;

WHILE(@succeeded = 0 AND @attemptNumber < 5) BEGIN

    SET @attemptNumber = @attemptNumber + 1;

     BEGIN TRY

       UPDATE Data.Users

           SET FirstName = @FirstName,

                LastName = @LastName,

                Position = @Position

           WHERE UserID = @UserID;

       SET @succeeded = 1;

     END TRY

     BEGIN CATCH

       IF (XACT_STATE()) = -1

       BEGIN

                ROLLBACK TRANSACTION;

       END;

       INSERT INTO Data.ErrorLog(ERROR_PROCEDURE, ERROR_LINE, ERROR_NUMBER, ERROR_MESSAGE, ERROR_TIME)

           SELECT ERROR_PROCEDURE(),

             ERROR_LINE(),

             ERROR_NUMBER(),

             ERROR_MESSAGE(),

             CURRENT_TIMESTAMP;

       SET @ret = 1;

     END CATCH

END

RETURN @ret;

 

Also you will need the LockTimouter class which reproduces a lock timeout inside your stored procedure:

·          - it opens another connection,

·         - begins a transaction,

·         - modifies the data from that connection,

·         - then it attempts to modify the same data from your stored procedure

·         - releases the lock as soon as your error is logged, so that your retry can succeed

 

 

    public class LockTimeouter : IDisposable

    {

        private readonly SqlConnection _anotherConnection;

        private readonly SqlCommand _command;

        private readonly SqlCommand _anotherCommand;

        private readonly IAsyncResult _anotherCommandStarted;

 

        public LockTimeouter(SqlConnection connection, string hookCommand)

        {

            _command = connection.CreateCommand();

            _command.CommandText = "SET LOCK_TIMEOUT 1;DELETE FROM Data.ErrorLog";

            _command.CommandType = CommandType.Text;

            _command.ExecuteNonQuery();

            _anotherConnection = new SqlConnection(connection.ConnectionString + ";Asynchronous Processing=true");

            _anotherConnection.Open();

            _anotherCommand = _anotherConnection.CreateCommand();

            _anotherCommand.CommandText = string.Format("BEGIN TRANSACTION; {0};", hookCommand);

            _anotherCommand.CommandType = CommandType.Text;

            _anotherCommand.ExecuteNonQuery();

            _anotherCommand.CommandText = "DECLARE @c INT;SET @c=0;WHILE @c=0 BEGIN SELECT @c=COUNT(*) FROM Data.ErrorLog;END;COMMIT";

            _anotherCommand.CommandType = CommandType.Text;

            _anotherCommandStarted = _anotherCommand.BeginExecuteNonQuery();

        }

 

        public void Dispose()

        {

            _command.CommandText = "IF @@TRANCOUNT>0 BEGIN ROLLBACK; END";

            _command.ExecuteNonQuery();

            _anotherCommand.EndExecuteNonQuery(_anotherCommandStarted);

            _anotherCommand.CommandText = _command.CommandText;

            _anotherCommand.ExecuteNonQuery();

            _anotherConnection.Close();

        }

    }

 

The unit test itself is quite simple, because most of the work is wrapped in the LockTimouter class

 

        [Test]

        public void LockTimeoutTest()

        {

            SqlCommand command = _connection.CreateCommand();

            command.CommandText = "DELETE FROM Data.Users WHERE UserId = 12345";

            command.CommandType = CommandType.Text;

            command.ExecuteNonQuery();

            command.CommandText = "Writers.UpdateUserWithRetry";

            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.AddWithValue("@UserId", 12345);

            command.Parameters.AddWithValue("@FirstName", "Josh");

            command.Parameters.AddWithValue("@LastName", "Olson");

            command.Parameters.AddWithValue("@Position", "QA");

            using (new LockTimeouter(_connection, "INSERT INTO Data.Users(UserID, FirstName, LastName, Position, LotsOfComments)VALUES(12345,'Jill','Hansen','QA','')"))

            {

                try

                {

                    command.ExecuteNonQuery();

                    Console.WriteLine("No exception raised");

                }

                catch (Exception e)

                {

                    if (!e.ToString().Contains("Transaction count after EXECUTE indicates "))

                        Console.WriteLine(e);

                }

            }

            //check if there are updates in Data.Users, and if the error has been recorded

        }

 

 

When the test is finished, you can verify that one error was indeed recorded and that the modification finally completed:

 

SELECT * FROM Data.ErrorLog ORDER BY ERROR_TIME

SELECT * FROM Data.Users