Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"?
–
GaiusJan 13 '11 at 13:02

2

@Gaius ... no though I do believe that is one possible result of some race conditions
–
xenoterracideJan 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 DMar 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.
–
xenoterracideMar 29 '12 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)
–
Nick ChammasMar 29 '12 at 15:59

4 Answers
4

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

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:

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-KMar 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 RiffelMar 29 '12 at 14:19

@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.
–
xenoterracideNov 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årtenssonNov 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...
–
xenoterracideDec 5 '10 at 10:05