03 February 2008

Close Those Loopholes: Stress-Test those Stored Procedures

You can write a stored procedure that tests perfectly in your regression tests. You will hand it to the tester in the smug certainty that it is perfectly bug-free. Dream on, for without stress-testing you could easily let some of the most unpleasant bugs through. Alex continues his excellent series, by showing how to catch those subtle problems.

This is the fourth article in the series, Close These Loopholes in Your Database Testing. It is worth reading them in sequence.

Finding those more subtle problems

In some cases, stored procedures will work perfectly as long as there is no concurrency, but then fail intermittently in a more realistic multi-user environment. I will explain this in more detail, as well as provide some examples, below. In this article I shall demonstrate a few simple techniques which will allow you to make sure that your stored procedures work in a multi-user environment. This is done by emulating real life concurrency in a test harness written in C# using NUnit.

Of course, there are many ways to run stress tests. I have chosen to use C# and NUnit, because it allowed me to reuse my code to the fullest, and it allowed me to incorporate my stress tests into my unit test harness, which is written in C# using NUnit. Anyway, the main point of this article is to encourage you to do stress testing, and the choice of tools to implement tests is less important. How you do your stress testing is up to you – what works best for me might not be the best choice for you.

Note: Strictly speaking, stress tests are different from unit tests. Unit tests are supposed to run in exactly the same way every time they run. On the other hand, stress tests usually do not have to provide identical results every time they run. If your stress test is done right, and if the module being tested has problems,  then most likely the stress test will expose the problems, but not in exactly the same way every run.


In order to emulate concurrency, you would need to issue queries at the same time from at least two connections, so the test harness needs to provide these two connections. You also have  to guarantee that your two connections are going to issue queries simultaneously for a considerable time. This is why the test harness needs to do its best to ensure that queries from different connection actually run simultaneously. The following simple class StressTester meets both requirements, and it can be reused several times – it will be used twice later in this article. To use StressTester, you need to provide two implementations of the following interface:

The important piece here is the AnotherConnectionStartedUp method, which detects whether another connection is ready to start stress testing, as should be clear from the following source code:

It is essential to do this detection: If you don’t, then  one thread may complete its task before the other one starts, which would render your stress test completely useless.

Using the Test Harness

Use the harness: have a select become a deadlock victim.

Now that the harness is ready, it is time to use it. In this section I will utilize it to reproduce a deadlock. A typical textbook example of a deadlock involves two connections modifying different rows then attempting to modify rows already modified and locked by another connection. However, there are other occasions that are likely to lead to deadlocks.  In some cases a query which only reads data without modifying it may still become a deadlock victim;  you will see it for yourself soon.

The following stored procedure issues SELECTs in a loop (it is assumed, and essential, that snapshot isolation is not enabled):

Another stored procedure issues modifications in a loop:

Using StressTester harness to run these two stored procedures simultaneously is very simple:

Most likely the Readers.SampleReader will be a deadlock victim. If it does not happen, then you should increase the number of iterations Let me repeat myself: there is no guarantee that you will reproduce a deadlock, but there is a very high probability that you will reproduce it. Come to think of it: if you run your SELECT only once, and run your update only once, the chance that they will embrace in a deadlock is very low. By increasing the number of iterations, you increase the chance that your processes will embrace in a deadlock. In my recent experience, 10K iterations is more than enough under most circumstances. Depending on your environment, you might need a different number.


Use the harness: stress test generation of unique numbers.

In the first article of this series, I provided three stored procedures which were supposed to generate unique numbers, and stated that only one of them actually works in a concurrent environment. In this chapter I will stress test all three so that you can see for yourself that two of them have problems. Here is the one that is correct:

In my Run method, you’ll have noticed that I finish stress testing before I start saving the unique numbers. This is essential: when you are stress testing, concentrate on it and do nothing else but stress test. Anything that can wait should be postponed until the stress testing is finished.

The second assertion (verify that threads ran simultaneously) needs some explanations. If one thread completed selecting its 10000 numbers before the second one started, the query would return 1. If two threads were always taking turns in acquiring unique numbers, then one thread would get all odd numbers, and the other would get all even ones – in that case the query would return 19999. Typically, if both threads run more or less simultaneously, the query returns at least 10000. (Let me repeat myself: the method StressTester.AnotherConnectionStartedUp is supposed to ensure that both threads do indeed run simultaneously, and it typically does ensure that.) So the second assertion verifies that both threads actually ran simultaneously.

Your mileage may vary. Depending on your hardware you might need some other threshold, not necessarily 10000.

After you have verified that you are actually getting unique consequent numbers, change the stored procedure:

Now rerun the test. This time you will get duplicates, and the test will fail. You can inspect the Data.UniqueNumers table and see the duplicates. Change the stored procedure again:

Rerun the test. This time you will get deadlocks.


As you have seen, stress testing is an easy way to emulate real life concurrency and expose any problems in your stored procedures.  In the next article I will demonstrate how to reproduce common database-related errors and how to unit test error handling.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


Tags: , ,


  • Rate
    [Total: 25    Average: 4.2/5]

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.

View all articles by Alex Kuznetsov

  • Arne

    Cannot reproduce your SampleReader deadlock

    I cannot reproduce your SampleReader deadlock. I increased number of iterations, but that did not help. Do you have any clue?

  • Alex Kuznetsov

    A few suggestions

    A few suggestions:
    1. Make sure read committed snapshot is not enabled.
    2. Try running on a box with more CPUs. My development box has four dual core CPUs, which is good enough to run 2-3 threads of stress testing and a local instance of Developer’s Edition SQL Server 2005.
    3. Try running against a server running on another box.

  • Joe Webb

    Excellent article
    Good stuff, Alex. Thanks.


  • puzsol

    Good idea
    Ok, so I’m totally missing the point of the testing procedure, but in my opionion, if you are coding this procedure correctly, you don’t need a transaction.

    ALTER PROCEDURE Writers.GetNextCounter
    DECLARE @Ctr int
    UPDATE Data.Counter SET @Ctr = Counter, Counter = Counter + 1
    SELECT @Ctr as Counter

  • Alex Kuznetsov

    I simplified all the examples as much as possible.

    Fair enough, but to keep the article simple and easy to understand, I simplified all the examples as much as possible. GetNextCounter was developed using only the simplest SQL, because I did not want any distractions from my main point. Similarly, this is why in these examples there are connection1 and connection2, runner1 and runner2 – in real life there are arrays of connections and IStressTestables.

  • RichardHowells

    I don’t see how Readers.SampleReader and Writers.SampleWriter can deadlock.
    Ok – I’m going to nail my colors to the mast.

    I see how it’s possible for Readers.SampleReader and Writers.SampleWriter to deadlock.

    Here is my reasoning. Each has only a single atomic SQL statement. These statements may take locks for the duration of their execution, but those locks will not be held after the statement is completed. Therefore – if one of them has a lock that blocks the other, the other will wait until the lock releases. They CANNOT reach the situation where both are already holding a lock and trying to obtain a resource locked by the other.

    This seems really fundamental to me. If I have it wrong and they CAN deadlock then I need to understand why, so I can go and change some code out there in production. Someone please educate me.

  • RichardHowells

    Second para above s/b…

    I DON’T see how it’s possible for Readers.SampleReader and Writers.SampleWriter to deadlock.

    What a difference a word makes. 🙁

  • dananos

    Stress testing with NUnit
    Here is a handy wee function to call a function multiple times at once:

    Helped uncover some nasty ‘heisenbugs’ …

  • dananos

    Stress testing with NUnit
    Here is a handy wee function to call a function multiple times at once:

    Helped uncover some nasty ‘heisenbugs’ …