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, 2011 at 13:02
  • 3
    @Gaius ... no though I do believe that is one possible result of some race conditions Jan 13, 2011 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, 2012 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. Mar 29, 2012 at 14:55
  • 1
    @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) Mar 29, 2012 at 15:59

5 Answers 5


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? Jan 6, 2011 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.                                           .

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, 2012 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. Mar 29, 2012 at 14:19

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


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. Nov 29, 2010 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, 2010 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... Dec 5, 2010 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.