Close These Loopholes in Your Database Testing

Alex starts of a series of articles on 'Unit Testing' your database development work. He starts off by describing five simple rules that make all the difference.

DevOps, Continuous Delivery & Database Lifecycle Management
Continuous Integration

Incorrect or incomplete database unit tests may run successfully, but they may not expose all the problems in your application – they can only give you a false sense of security. The following guidelines will help you improve your database unit tests.

Have settings on your Test Server match the Production Server.

Clearly this is an extremely trivial recommendation. However, I cannot emphasize enough how important it is. Discrepancies in database settings may cause your application to silently behave differently, without raising any errors – such problems may be very difficult to detect. For instance, the following code snippet will run differently against databases with different default collations:

CREATE TABLE t(a CHAR(1), b CHAR(1))

INSERT t(a, b) VALUES(‘a’, ‘A’)

SELECT COUNT(*) FROM t WHERE a=b

You can create the following two databases, run the code snippet against both, and see for yourself that it returns 1 on your case insensitive database, and 0 on your case sensitive one:

CREATE DATABASE [TestCI] COLLATE SQL_Latin1_General_CP1_CI_AS
GO
CREATE DATABASE [TestCS] COLLATE SQL_Latin1_General_CP1_CS_AS
GO

There are many other settings that may affect your application, such as default isolation level (READ COMMITTED vs. READ COMMITTED SNAPSHOT), and settings on nested and/or recursive triggers. Providing a complete list of settings and explaining how they may affect your application is beyond the scope of this article. For an example on how snapshot isolation may affect your modifications, refer to a series of Hugo Kornelis’s posts named ” Snapshot isolation: A threat for integrity?” and published on sqlblog.com. I describe how settings on nested and/or recursive triggers have broken an application in my article ” Avoid These Common Business Rule Implementation Mistakes”

(http://www.devx.com/dbzone/Article/31985/)

Impersonate your user when you test.

Let’s suppose that you created a stored procedure but forgot to grant any privileges to execute it. When you, the developer, then unit test your code against your development server, you may connect as a dbo – this is a common practice. Unfortunately, so long as you connect as dbo, you will not detect your missing permissions. This problem is very easy to fix. For example, you can just create a mock user and add it to your database role.

CREATE USER mockuser WITHOUT LOGIN
GO
sp_addrolemember @rolename = ‘YourRoleName’, @membername = ‘mockuser’
GO

In your unit test, impersonate the mock user before executing the stored procedure, as follows:

SETUSER ‘mockuser’

If the permission to execute your stored procedure is not granted to either the mock user or to the role, you will get an exception. Do not forget to revert to dbo after running the test:

SETUSER

Note: This is a just very simple example of impersonation. It is good to use when you just run your unit tests on your local machine. It is not a substitute for a more realistic test environment when a real network account is used. A more detailed discussion of database security and impersonation is beyond the scope of this article.

Populate enough test data before your test.

We’ll suppose that you have failed to provide a WHERE clause in your stored procedure:

/*
I deliberately stripped this stored procedure from all the bells and
whistles that are not relevant to the point I am making.
*/
CREATE PROCEDURE Writers.DeleteCustomer @CustomerID INT
AS
DELETE FROM Data.Customers –WHERE clause is missing
RETURN @@ERROR
GO

If you only had one row in your table before running the stored procedure, your error would not be detected by your unit test. Make sure you have enough test data before running the test. After running the test, make sure that all the data except for the one deleted row is still in the table.

Test how your code handles errors.

Now suppose that you have changed your DeleteCustomer stored procedure, as follows:

CREATE PROCEDURE Writers.DeleteCustomer @CustomerID INT
AS
DECLARE @error INT, @rowcount INT
DELETE FROM Data.Customers WHERE CustomerID = @CustomerID
SELECT @error = @@ERROR  — forgot to populate @rowcount = @@ROWCOUNT
IF @rowcount = 0 BEGIN
  RAISERROR(‘Could Not Find Customer’, 16, 1)
  SET @error = -1
END
RETURN @error
GO

Suppose you want to verify that you get an exception if you provide invalid customer ID, and you have written a unit test in C# using NUnit, as follows:

[Test]
[ExpectedException(typeof(SqlException))]
public void TestDeleteCustomerInvalidID()
{
            SqlCommand cmd = this.connection.CreateCommand();
            cmd.CommandText = “EXEC Writers.Delete_Customer -1”;
            cmd.ExecuteNonQuery();
}

Apparently the test succeeds, but wait a moment: because the name of the stored procedure is misspelled, you are getting the wrong kind of SQL exception! Your procedure did not even execute, and your unit test should not succeed. The following unit test verifies that the right exception is raised:

[Test]
public void TestDeleteCustomerInvalidID()
{
            bool gotTheRightException = false;
            cmd.CommandText = “EXEC Writers.Delete_Customer -1”;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                string s = e.Message;
                gotTheRightException =
                  s.Contains(“Could Not Find Customer”);
            }
            Assert.IsTrue(gotTheRightException);  
            //You still need to verify that no one row is deleted
}

Note that even though you verified that you received the right exception, you still need to verify that no rows are deleted. The reason is simple:

ALTER PROCEDURE Writers.DeleteCustomer @CustomerID INT
AS
DECLARE @error INT, @rowcount INT
DELETE FROM Data.Customers
— deliberately removed WHERE CustomerID = @CustomerID
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @rowcount <> 1 BEGIN
  RAISERROR(‘Could Not Find Customer’, 16, 1)
  SET @error = -1
END
RETURN @error
GO

This incorrect procedure first deletes all the rows and then raises the right exception if there were more than one row in the table. For more details on error handling in Transact-SQL refer to articles on Erland Sommarskog’s Web site.

Testing stored procedures which modify data will be described in more detail in a later article.

Do Not Forget To Consider Concurrency.

In some cases, stored procedures, will work as expected as long as you unit test them from a single connection, but fail intermittently in a more realistic multi-connection environment. For example, consider a requirement to implement a stored procedure that should return a unique integer as an output parameter every time it is called.

The following three simple stored procedures all attempt to implement the requirement:

CREATE TABLE Data.Counter(Counter INT NOT NULL)
INSERT Data.Counter(Counter) VALUES(1)
GO
CREATE PROCEDURE Writers.GetNextCounter1 @NextCounter INT OUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
UPDATE Data.Counter SET Counter = Counter + 1
SELECT @NextCounter = Counter FROM Data.Counter
COMMIT
GO
CREATE PROCEDURE Writers.GetNextCounter2 @NextCounter INT OUT
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT @NextCounter = Counter + 1 FROM Data.Counter
UPDATE Data.Counter SET Counter = Counter + 1
GO
CREATE PROCEDURE Writers.GetNextCounter3 @NextCounter INT OUT
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
UPDATE Data.Counter SET Counter = Counter + 1
SELECT @NextCounter = Counter FROM Data.Counter
COMMIT
GO

If you run any one of these three stored procedures from a single connection only, all three would appear to work correctly. However, if you run them thousands of times simultaneously from several connections, the first one will still reliably provide unique numbers, while the second one may provide some duplicates, and the third one is likely to end up in a deadlock.

As you have seen, simple unit testing from one connection is not sufficient in this case.

The next article will explain in more detail how to utilize C# and NUnit to unit test result sets returned from stored procedures.

DevOps, Continuous Delivery & Database Lifecycle Management
Go to the Simple Talk library to find more articles, or visit www.red-gate.com/solutions for more information on the benefits of extending DevOps practices to SQL Server databases.

Tags: , , , ,

  • 33910 views

  • Rate
    [Total: 0    Average: 0/5]
  • Michelle Taylor

    NUnit can test exception messages directly
    Instead of:
    [Test]
    [ExpectedException(typeof(SqlException))]

    You can have:
    [Test]
    [ExpectedException(typeof(SqlException), “expected message contents here”)]

    It’s not quite as good as using Contains because you have to get the exact message right, but it looks neater if you do know the entire message (and the message has no parts that change without changing the test result).

  • Alex Kuznetsov

    Good point.
    Michelle,

    Good point, thanks! Actually I prefer to put more information in my error messages, such as

    RAISERROR(‘Could Not Find Customer %d’, 16, 1, @CustomerID)

    So my error messages usually look like
    ‘Could Not Find Customer -1’

    This is why I prefer to use Contains.

  • Ravi Shankar

    Good Article Especially the Concurrency part

    I was working on smaller test environment 2 clients, and using a stored procedure to generate unique primary key columns.

    I will follow your recommendations.

    Thanks for the great work

  • Elena

    not all clear
    Why does the third version of SP that generates increment cause a deadlock?

  • Simon Clark

    Concurrency question
    In your first example, if the assignment of the value to the variable is done within the update statement, does this affect the concurrency ?

  • Alex Kuznetsov

    you can see for yourself
    Elena,

    Short answer: because of SERIALIZABLE isolation level, IX lock is applied against the row before an X lock. Because IX lock is shared, not exclusive, two connections can acquire it at the same time. Then both connections will try to acquire an X lock, which is incompatible with an IX lock being held by another connection. To reproduce, you can run the following script from two connections:

    SELECT @@SPID SPID
    GO
    DECLARE @i INT, @NextCounter INT
    SET @i = 0
    WHILE @i < 100000 BEGIN
    EXEC Writers.GetNextCounter3 @NextCounter OUT
    PRINT @NextCounter
    SET @i = @i + 1
    END

    After you have reproduced a deadlock, analyze your ERRORLOG file. For more detail, refer to an excellent article by SQL Server MVP Ron Talmage: “Resolving Deadlocks in SQL Server 2000”. For 2005 the behaviour of DBCC TRACEON has changed, you might also want to read BOL about the breaking changes in 2005.

  • Alex Kuznetsov

    Assingment in update is OK
    Simon,

    Assingment in update is OK, you can see for yourself after the following setup:

    CREATE TABLE Data.UniqueNumbers(iD INT IDENTITY, i INT, spid INT)
    GO
    CREATE PROCEDURE Writers.GetNextCounter4 @NextCounter INT OUT
    AS
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRAN
    UPDATE Data.Counter SET @NextCounter = Counter = Counter + 1
    COMMIT
    GO

    Now you can can simultaneously from several connections this loop:

    DECLARE @i INT, @NextCounter INT
    SET @i = 0
    WHILE @i < 100000 BEGIN
    EXEC Writers.GetNextCounter4 @NextCounter OUT
    INSERT Data.UniqueNumbers(i, spid) VALUES(@NextCounter, @@SPID)
    SET @i = @i + 1
    END

    After it’s finished, make sure there are no duplicates:

    SELECT i, COUNT(*) FROM Data.UniqueNumbers GROUP BY i HAVING COUNT(*) > 1

    Also look at your data to make sure your loops were running simultaneously much of the time:

    SELECT TOP 10000 * FROM Data.UniqueNumbers

    Actually we are using rather more complex automated queries to make sure that our loops were running simultaneously.

  • Lex

    setuser nono
    Nice list of common pitfalls.

    In SQL 2005 the SETUSER-statement has been marked as obsolete. The EXECUTE AS-statement should be used instead. Works fairly the same way.

  • Stuart

    NUnit 2.4 Can Test Exception Messages Directly Using Match Criteria
    A new feature with NUnit 2.4 is the option of testing the exception message using a MatchType parameter. The possibilities are Exact, Contains and RegEx.

    So for the above example you could use

    [Test]
    [ExpectedException( typeof(SqlException), ExpectedMessage=”Could Not Find Customer”, MatchType=MessageMatch.Contains )]

    (http://nunit.com/index.php?p=exception&r=2.4.3)

  • Alex Kuznetsov

    Yes, EXECUTE AS is the way to go.
    Lex,

    Yes, SETUSER has been deprecated, thanks for pointing that out. I just wanted to provide the briefest example. This article is an eye-opener rather than a complete reference.

  • Alex Kuznetsov

    Actually I need to catch the exception inside my test
    Stuart and Michelle,

    Actually I need to catch the exception inside my test because I still need to verify that no rows were deleted. This is why i cannot rely on NUnit functionality, this is why I have to catch the error myself. In real code I have that check instead of the comment saying “You still need to verify that no one row is deleted”.

  • Elena

    the results
    Alex
    I reproduced the deadlock and got better understanding about locks. Thank you.

  • Mitch Wheat

    Bit late, joining the comments but…
    Hi Alex

    In the form, GetNextCounter4, that uses assignment in the UPDATE, is the explicit TRANSACTION required? i.e.

    CREATE TABLE Data.UniqueNumbers(iD INT IDENTITY, i INT, spid INT)
    GO
    CREATE PROCEDURE Writers.GetNextCounter5 @NextCounter INT OUT
    AS
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    UPDATE Data.Counter SET @NextCounter = Counter = Counter + 1
    GO

    Just wondered, as a single UPDATE is wrapped in a implicit transaction isn’t it?

  • Mitch Wheat

    Bit late, joining the comments but…
    Hi Alex

    In the form, GetNextCounter4, that uses assignment in the UPDATE, is the explicit TRANSACTION required? i.e.

    CREATE TABLE Data.UniqueNumbers(iD INT IDENTITY, i INT, spid INT)
    GO
    CREATE PROCEDURE Writers.GetNextCounter5 @NextCounter INT OUT
    AS
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    UPDATE Data.Counter SET @NextCounter = Counter = Counter + 1
    GO

    Just wondered, as a single UPDATE is wrapped in a implicit transaction isn’t it?

  • Alex Kuznetsov

    here you go
    Mitch,

    I am using default settings here. the code is equivalent to the following:

    BEGIN TRANSACTION
    UPDATE Data.Counter SET @NextCounter = Counter = Counter + 1
    IF @@ERROR = 0
    COMMIT
    ELSE
    ROLLBACK

    I would not use the nomenclature “implicit transaction” to describe this behavior, as I think it is misleading. Please read about SET IMPLICIT_TRANSACTION ON in BOL.