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