This is the fourth article in the series, Close These Loopholes in Your Database Testing. It is worth reading them in sequence.
- Close These Loopholes in Your Database Testing
- Close these Loopholes – Testing Stored Procedures
- Close These Loopholes – Testing Database Modifications
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.
Prerequisites.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
public interface IStressTestable { //this method makes sure everything is ready to go //before the stress testing starts void Prepare(SqlConnection connection); //this method actually does the stress testing void Run(); } The StressTester class itself is quite short: public class StressTester : IDisposable { private readonly SqlConnection _connection1; private readonly SqlConnection _connection2; private readonly IStressTestable _runner1; private readonly IStressTestable _runner2; public StressTester(string connectionString, IStressTestable runner1, IStressTestable runner2) { _connection1 = new SqlConnection(connectionString); _connection1.Open(); _connection2 = new SqlConnection(connectionString); _connection2.Open(); _runner1 = runner1; _runner2 = runner2; _runner1.Prepare(_connection1); _runner2.Prepare(_connection2); } private static bool AnotherConnectionStartedUp( SqlConnection connection) { SqlCommand command = connection.CreateCommand(); command.CommandText = "Readers.WaitForAnotherConnection"; command.CommandType = CommandType.StoredProcedure; object obj = command.ExecuteScalar(); return (obj != null); } public void RunConcurrently() { Thread t1 = new Thread( new ThreadStart(delegate { if (AnotherConnectionStartedUp(_connection1)) { _runner1.Run(); } })); Thread t2 = new Thread( new ThreadStart(delegate { if (AnotherConnectionStartedUp(_connection2)) { _runner2.Run(); } })); t1.Start(); t2.Start(); t1.Join(); t2.Join(); } public void Dispose() { _connection1.Close(); _connection2.Close(); } } |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE PROCEDURE Readers.WaitForAnotherConnection AS BEGIN TRAN DECLARE @otherSpid INT, @d DATETIME, @i INT SELECT @i = COUNT(*) FROM Data.Hook1 WITH(HOLDLOCK) SELECT @otherSpid = NULL, @d = DATEADD(second, 10, Getdate()) WHILE @otherSpid IS NULL AND Getdate()<@D BEGIN select @otherSpid = req_spid from master.dbo.syslockinfo where rsc_objid = OBJECT_ID('dbo.Hook1') AND req_spid <> @@SPID END WAITFOR DELAY '00:00:00.050' COMMIT SELECT @otherSpid otherSpid GO |
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):
|
1 2 3 4 5 6 7 8 9 10 11 |
ALTER PROCEDURE Readers.SampleReader AS declare @i int, @j int, @c varchar(1000) select @i=0, @j = 0 set nocount on set deadlock_priority low while @i < 10000 begin select @c = filler from a1 where j = 119 SET @i = @i + 1 end |
Another stored procedure issues modifications in a loop:
|
1 2 3 4 5 6 7 8 9 10 |
ALTER PROCEDURE Writers.SampleWriter AS declare @i int, @j int select @i=0, @j = 0 set nocount on set deadlock_priority high while @i < 10000 begin update a1 set j = -j where k = 119 SET @i = @i + 1 end |
Using StressTester harness to run these two stored procedures simultaneously is very simple:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
internal class StoredProcedureRunner : IStressTestable { private SqlCommand _command; private readonly string _storedProcedureName; private bool runSucceeded = false; public StoredProcedureRunner(string storedProcedureName) { _storedProcedureName = storedProcedureName; } public bool RunSucceeded { get { return runSucceeded; } } public void Prepare(SqlConnection connection) { _command = connection.CreateCommand(); _command.CommandText = _storedProcedureName; _command.CommandType = CommandType.StoredProcedure; } public void Run() { try { _command.ExecuteNonQuery(); runSucceeded = true; } catch (Exception e) { Console.WriteLine(e); } } } [TestFixture] public class DeadlockTests { readonly string connectionString = "server=(local);trusted_connection=true;database=StressTests"; [Test] public void DeadlockTest() { StoredProcedureRunner runner1 = new StoredProcedureRunner("Writers.SampleWriter"); StoredProcedureRunner runner2 = new StoredProcedureRunner("Readers.SampleReader"); using ( StressTester tester = new StressTester(connectionString, runner1, runner2) ) { tester.RunConcurrently(); } //verify that both procedures completed execution //all the way to the end Assert.IsTrue(runner1.RunSucceeded); Assert.IsTrue(runner2.RunSucceeded); } } |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
CREATE PROCEDURE Writers.GetNextCounter AS SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN UPDATE Data.Counter SET Counter = Counter + 1 SELECT Counter FROM Data.Counter COMMIT And here is the test: internal class UniqueNumbersLoopReader : IStressTestable { private SqlCommand _command; private int[] numbers = new int[10000]; private int currentIndex = 0; private int errors = 0; public void Prepare(SqlConnection connection) { _command = connection.CreateCommand(); _command.CommandText = "Writers.GetNextCounter"; _command.CommandType = CommandType.StoredProcedure; } public void Run() { for(int i=0;i<numbers.Length; i++) { try { numbers[currentIndex++] = Convert.ToInt32(_command.ExecuteScalar()); } catch(Exception e) { Console.WriteLine(e); errors++; } if(errors > 10) { break; } } Console.WriteLine(string.Format("{0} completed, {1} failed", currentIndex, errors)); _command.CommandText = "INSERT data.UniqueNumbers(n, spid) VALUES(@i, @@spid)"; _command.CommandType = CommandType.Text; _command.Parameters.AddWithValue("@i", 0); for (int i = 0; i < currentIndex; i++) { _command.Parameters[0].Value = numbers[i]; _command.ExecuteNonQuery(); } } } [TestFixture] public class UniqueNumbersLoopReaderTests { readonly string connectionString = "server=(local);trusted_connection=true;database=StressTests"; [Test] public void UniqueNumbersTest() { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandType = CommandType.Text; command.CommandText = "TRUNCATE TABLE Data.UniqueNumbers"; command.ExecuteNonQuery(); command.CommandText = "UPDATE Data.Counter SET Counter = 0"; command.ExecuteNonQuery(); using ( StressTester tester = new StressTester(connectionString, new UniqueNumbersLoopReader(), new UniqueNumbersLoopReader())) { tester.RunConcurrently(); } //verify that you are getting the expected results command.CommandText = "SELECT COUNT(DISTINCT n) FROM Data.UniqueNumbers"; int numbersCount = Convert.ToInt32(command.ExecuteScalar()); //verify that all tests succeeded Assert.AreEqual(20000, numbersCount); command.CommandText = "SELECT SUM(isDifferent) FROM (SELECT CASE WHEN u.spid = (SELECT TOP 1 u1.spid FROM Data.UniqueNumbers u1 WHERE u1.n < u.n ORDER BY n DESC) THEN 0 ELSE 1 END AS isDifferent FROM Data.UniqueNumbers u) t"; int numberSwitches = Convert.ToInt32(command.ExecuteScalar()); //verify that threads ran simultaneously Assert.IsTrue((numberSwitches > 10000),string.Format("Not enough switching: {0}", numberSwitches)); } } } |
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:
|
1 2 3 4 5 6 7 |
ALTER PROCEDURE Writers.GetNextCounter AS -- this approach provides duplicates SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT Counter + 1 AS Counter FROM Data.Counter UPDATE Data.Counter SET Counter = Counter + 1 GO |
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:
|
1 2 3 4 5 6 7 8 9 10 |
ALTER PROCEDURE Writers.GetNextCounter AS -- this approach provides deadlocks SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN UPDATE Data.Counter SET Counter = Counter + 1 SELECT Counter FROM Data.Counter COMMIT GO |
Rerun the test. This time you will get deadlocks.
Postscript
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.
Load comments