# How do you test for race conditions in a database?

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.

-
By "race condition" do you mean "deadlock"? –  Gaius Jan 13 '11 at 13:02
@Gaius ... no though I do believe that is one possible result of some race conditions –  xenoterracide Jan 13 '11 at 14:18
@Gaius race conditions in a database would be doing things like dropping a table before it was created or updating a row before it had been inserted. Generally I would imagine that it is handled by the application logic outside the database itself. –  Mark D Mar 29 '12 at 14:40
updating a row before it had been inserted? that wouldn't cause a db problem. no a race condition would be like fetching a row, and updating it, but having another user update it after your row was fetched but before your update was processed. –  xenoterracide Mar 29 '12 at 14:55
@MarkD - No. There are many types of race conditions that arise from incorrectly encapsulating an atomic unit of work in your database. Here's an example. Remember, "a race condition or race hazard is a flaw in an electronic system or process whereby the output or result of the process is unexpectedly and critically dependent on the sequence or timing of other events." (source) –  Nick Chammas Mar 29 '12 at 15:59

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.

-

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 ;-)

-
could you give a step by step example? and can programmatic tests be written to do the same thing? –  xenoterracide Jan 6 '11 at 13:20

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.

-
I would not describe such tests as unit tests, because unit tests must run in exactly the same way every time. Race conditions fail involved processes intermittently, not in exactly the same way every time. –  A-K Mar 29 '12 at 2:41
@AlexKuznetsov You are correct that Unexpected race conditions can show them selves intermittently, however the OP is referring to Expected conditions he believes the code to be handling. These specific conditions can be reproduced precisely and the handling verified with a unit test. –  Leigh Riffel Mar 29 '12 at 14:19

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.

-
@darioo lol I thought maybe wn was an acronym for something... idk what he would mean by "do locking on your own" If he means not with an ORM, I checked the code my ORM outputs it certainly doesn't do the locking right. Which is one of the reasons I'd like to be able to test potential race condition scenario's. –  xenoterracide Nov 29 '10 at 2:15
Yes I meant own, and normally the database driver handles locking, row, table or possibly field, but I am just opening up for the possibility that you use some DB that does not handle locking ;) –  David Mårtensson Nov 29 '10 at 13:17
.. I'm pretty sure if I have a multiple statement transaction that my DB won't know which rows to lock automagically... things like select for update wouldn't exist if they did... –  xenoterracide Dec 5 '10 at 10:05