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 link below.
Load comments