How do you test for race conditions in a database?

Question

I try to write database code to make sure that it's not subject to race conditions, to make sure that I've locked the correct rows or tables. But I often wonder: Is my code correct? Is it possible to force any existing race conditions to manifest? I want to be sure that if they do happen in a production environment my application will do the right thing.

I generally know exactly which concurrent query is likely to cause a problem, but I've no idea how to force them to run concurrently to see if the correct behavior happens (e.g. I used the correct type of lock), that the right errors are thrown, etc.

Note: I use PostgreSQL and Perl, so if this can't be answered generically it should probably get retagged as such.

Update: I'd prefer it if the solution was programmatic. That way I can write automated tests to make sure there aren't regressions.

Answer 1

I do it all the time with my T-SQL modules.

Essentially, all you need to do is run your modules from two or more connections in a loop for a couple of minutes. Typically, all potential problems are exposed in a few minutes, assuming you have a SQL Server box with decent CPUs.

I wrote a few examples here and here.

Answer 2

I usually works with the command line tool of the RDBMS, just having 2 (or more) instances of the CLI started. You can then replay one by one and as a race (that would look like an action-RPG) the SQL statements your application layer is sending. You should experiment/feel the locking systems in action as your CLI will "hang" a bit, waiting the locks to be released from the others CLI.

If this sounds clear as mud, do not hesitate to say so ;-)

Answer 3

Race Conditions require multiple thread of execution, therefore to unit test this you will need to be able to start one or more threads. In Oracle I would use DBMS_Scheduler to run a process to simulate a second user. If PostgreSQL/Perl has a way to initiate a second process programatically, then you should be able to do something like this:

Process 1                                                          Process 2

Start Process 2. >>                            
Delay to allow 2 to do it's work. 
.                                               Lock rows or change data.
.                                               Delay to allow 1 to do it's work.
Attempt to lock rows or change data.            .
Check to ensure proper handling is done.        .
Ends.                                           .
                                                Ends.

It is good to see thinking on how to handle race conditions and more importantly how to unit test them.

Answer 4

You might be interested in stress testing. I have a similar situation in which many concurrent requests can race for a resource, but only of them should be able to claim it.

Example code for my use case in Laravel:

$file = FileModel::lockForUpdate()->findOrFail(1);

Users in my system can mark files as "claimed", so no other accounts can access them. I thought of using PEST for stress testing (https://pestphp.com/docs/stress-testing) in this way:

• Serve the app locally on localhost:8000
• Stress test the endpoint that contains the above code, for example POST /claim-files
• Check for the count of successful and failed responses: Only ONE request should be successful and the others should fail because I'm using DB locks

Unfortunately, I'm still looking for another solution for testing because I couldn't figure out how to mock the request user (that is, if it's even possible at the first place in stress testing).

Answer 5

As long as you lock rows you should not get at race condtion as that usually is caused when no locking is in place.

But you could get deadlocked if one question blocks your question for too long.

This is hard to test as time for queries can change when the database grows.

Queries that work fine with 100 000 rows of test data goes off the chart with 10 000 000 rows.

This type of problem can be very difficult to find in advance, but many DBs have some method of identifying slow queries.

By using that regulary you should be able to trap any queries heading into trouble with ample warning.

If you do locking on your own, its another story, but there I cannot help.