THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Unit Testing Tips: Reproduce a Lock Timeout

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

 

Published Friday, May 30, 2008 6:24 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement