Click here to monitor SSC
Alex Kuznetsov

Close these Loopholes - Reproduce Database Errors

23 May 2008

This is the final part of Alex's ground-breaking series on unit-testing Transact-SQL code. Here, he shows how you can test the way that your application handles database-related errors such as constraint-violations or deadlocks. With a properly-constructed test-harness you can ensure that the end-user need never sees the apparent gobbledegook of database system error messages, and that they are properly and robustly handled by the application.

We have been proud to publish this unique series of articles on Simple-Talk. We would urge you to read them in sequence, as they follow a logical sequence.

. Close These Loopholes in Your Database Testing
. Close These Loopholes: Testing Stored Procedures
. Close These Loopholes: Testing Database Modifications
. Close These Loopholes: Stress-Test those Stored Procedures

Reproduce database errors in your unit tests.

Robust applications must gracefully handle whichever errors occur when they run. Error handling in Transact-SQL is complex, and so is the testing of it. To unit test the error-handling of your database, you need a way to reproduce database-related errors in your unit test harness, and I will give you several examples. I will show you how to reproduce a constraint violation, a conversion error, a timeout, a lock timeout, and a deadlock. The article that I am presenting to you is just to get you started; it is not supposed to describe all the cases – that would be too much for short article. 

Prerequisites

Again, I will be using the same database, StressTests. All the unit tests in this article will reproduce error during the execution of the following stored procedure:

CREATE PROCEDURE Writers.SaveUser

  @UserID INT,

  @FirstName VARCHAR(8),

  @LastName VARCHAR(8),

  @Position VARCHAR(8)

AS

DECLARE @ret INT;

SET @ret = 0;

SET NOCOUNT ON

BEGIN TRY

  INSERT INTO Data.Users(UserID, FirstName, LastName, Position, LotsOfComments)

    VALUES(@UserID, @FirstName, @LastName, @Position, '');

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

RETURN @ret;

 

This stored procedure is supposed to log errors in the following table:

CREATE TABLE [Data].[ErrorLog](

      [ERROR_PROCEDURE] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [ERROR_LINE] [int] NULL,

      [ERROR_NUMBER] [int] NULL,

      [ERROR_MESSAGE] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

      [ERROR_TIME] [datetime] NOT NULL

)

Also the test involving a timeout will use the following UDF:

CREATE FUNCTION Readers.Wait(@seconds INT)

RETURNS INT AS BEGIN

DECLARE @d DATETIME, @toggle INT;

SET @d = DATEADD(second, @seconds, CURRENT_TIMESTAMP);

SET @toggle = 0;

WHILE(CURRENT_TIMESTAMP < @d)BEGIN

  SET @toggle = 1 - @toggle;

END

RETURN 1;

END

 

To do list for your unit tests.

After you have reproduced a database-related error, check the following:

  • Your data is in expected state. Failed modifications need to roll back completely. If you are logging errors in the database, verify that your error has been logged.
  • If you expect an exception, verify that a correct exception was thrown.
  • The connection is in a clean state – no outstanding and/or doomed transaction.

This list applies to all unit tests involving database related errors.

 

Reproducing a constraint violation.

To reproduce a constraint violation you can, of course, just attempt to save some invalid data. However, this is not always the easiest way, because it may take some effort to come up with test data, and because changes in test data frequently break such unit tests. Alternatively, you can create a constraint just for your test, have your modification fail because of that constraint, and dispose of the constraint when your test is over – this approach may save you considerable time which otherwise would be spent on developing and maintaining your test harness. All this logic is implemented once, in a class named TemporaryConstraint, and the following unit test demonstrates its use:

        private static void SetUpInsertUserCommand(SqlCommand command)

        {

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

            command.CommandType = CommandType.Text;

            command.ExecuteNonQuery();

            command.CommandText = "Writers.SaveUser";

            command.CommandType = CommandType.StoredProcedure;

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

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

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

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

        }

 

        [Test]

        public void FailedCheckConstraintTest()

        {

            SqlCommand command = _connection.CreateCommand();

            SetUpInsertUserCommand(command);

            using (new TemporaryConstraint(_connection, "Data", "Users", "1<0"))

            {

                try

                {

                    command.ExecuteNonQuery();

                    Console.WriteLine("No exception raised");

                }

                catch(Exception e)

                {

                    Console.WriteLine(e);

                }

            }

            //check if there is a new row in Data.Users, and if the error has been recorded

            //also check that the constraint is gone

        }

 

The parameter "1<0" is the body of the constraint – the TemporaryConstraint class will essentially embed it in the following DDL command and execute it:

ALTER TABLE Data.Users WITH NOCHECK ADD CONSTRAINT Data_Users_TemporaryConstraint CHECK(1<0)

Note that the WITH NOCHECK clause means that the constraint will not apply against existing data in the table – otherwise the constraint would not create if the table is not empty. Dispose method of the class will drop this constraint by executing the following DDL:

ALTER TABLE Data.Users DROP CONSTRAINT Data_Users_TemporaryConstraint

That method will be called automatically when the execution leaves the using block.

Note: to keep the article short, I have not included the code which verifies that the table Data.Users is unchanged, and that the error has been logged in Data.Errorlog table. I was explaining how to accomplish that in one of my previous articles.

 

When your try…catch block does not catch an error.

It is important to realize that in Transact SQL try..catch blocks are not guaranteed to catch all the errors. For instance, a timeout will not be caught in the catch block of the Writers.SaveUser stored procedure. The following unit test reuses TemporaryConstraint class and reproduces a timeout:

        [Test]

        public void TimeoutTest()

        {

            SqlCommand command = _connection.CreateCommand();

            SetUpInsertUserCommand(command);

            command.CommandTimeout = 1;

            using (new TemporaryConstraint(_connection, "Data", "Users", "Readers.Wait(5)=1"))

            {

                try

                {

                    command.ExecuteNonQuery();

                    Console.WriteLine("No exception raised");

                }

                catch (Exception e)

                {

                    Console.WriteLine(e);

                }

            }

            //check if there is a new row in Data.Users, and if the error has been recorded

            //also check that the constraint is gone

        }

 

As you have seen, the timeout has not been caught in the stored procedure and needs to be dealt with on the client.

 

Utilize triggers to reproduce errors.

Triggers are also very useful in reproducing errors. A trigger allows for a lot of flexibility – you can tuck various commands in it, including a straightforward RAISERROR command. The following unit test uses the TemporaryTrigger class, which wraps up all the reusable code necessary to build a trigger and to dispose of it:

        [Test]

        public void ConversionErrorTest()

        {

            SqlCommand command = _connection.CreateCommand();

            SetUpInsertUserCommand(command);

            using (new TemporaryTrigger(_connection, "Data", "Users", "DECLARE @i INT; SET @i=CAST('abc' AS INT)"))

            {

                try

                {

                    command.ExecuteNonQuery();

                    Console.WriteLine("No exception raised");

                }

                catch (Exception e)

                {

                    Console.WriteLine(e);

                }

            }

            //check if there is a new row in Data.Users, and if the error has been recorded

            //also check that the constraint is gone

        }

 

As you have seen, this time the error is handled by the stored procedure. Similarly to the previous tests, all you have to come up with is the body of the trigger.

Reproduce a deadlock using only one thread.

In the preceding article on stress testing, I needed to issue commands from two different threads to reproduce a rather subtle type of deadlock involving a reader and a writer. In its article I will reproduce the simplest kind of a deadlock, involving two writers competing for two different resources, and in a completely repeatable way. The following unit test runs in exactly the same way every time. Also note that Ado.Net comes with a very useful feature – it allows you to run queries asynchronously. I am utilizing asynchronous execution in my Deadlocker class, so that I do not even need a second thread to reproduce a deadlock. Anyway, all these complexities are already implanted once for you, and the unit test which uses the Deadlocker class is just as simple as the previous ones:

        [Test]

        public void DeadlockTest()

        {

            SqlCommand command = _connection.CreateCommand();

            SetUpInsertUserCommand(command);

            using (new Deadlocker(_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

        }

In this unit test all you need to provide is a command which will lock the row which the Writers.SaveUser stored procedure will attempt to modify. The rest is implemented in Deadlocker class and may be reused multiple times. Note that the deadlock is handled in the stored procedure.

Similarly, you can reproduce other errors caused by concurrency: lock timeouts and update conflicts under snapshot isolation level. Reproducing such errors is quite similar to reproducing deadlocks and as such is beyond the scope of this article.

Developing and maintaining robust applications is not easy. All kinds of problems may occur in production, and a robust application must handle them gracefully – and this needs testing. You have seen how to develop unit tests that reproduce database related problems and verify that they are handled properly.

This article concludes the series on database testing. You have seen various techniques and approaches allowing you to quickly develop comprehensive and robust unit tests. Good luck with your development!

As usual, the files are downloadable from the speech-bubble at the top of the article ,or by right-clicking ...Here... ...Here... and ...Here...  and then selecting 'save target as...'  ( or 'save link as...) in the context menu!

Alex Kuznetsov

Author profile:

Alex Kuznetsov has been working with object oriented languages and databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. Alex has published multiple articles on simple-talk.com and sqlblog.com and wrote a book entitled Defensive Database Programming with SQL Server. Currently he works in an agile team in Chicago. In his leisure time, Alex prepares for and runs ultramarathons.

Search for other articles by Alex Kuznetsov

Rate this article:   Avg rating: from a total of 14 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Bad Links
Posted by: John Marsing (not signed in)
Posted on: Wednesday, May 28, 2008 at 1:00 PM
Message: Alex,

It appears that the code in the buble doesn't match to code in your article. Also the links at the bottom of the article are bad

Subject: Can you describe your problems in more detail?
Posted by: Alex K (not signed in)
Posted on: Wednesday, May 28, 2008 at 1:53 PM
Message: John,

Can you describe your problems in more detail? Error messages etc?

Subject: re: Bad links
Posted by: Andrew Clarke (view profile)
Posted on: Wednesday, May 28, 2008 at 5:04 PM
Message: I've changed these to .TXT files so they should now work in any browser.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.