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.