How do you test for race conditions in a database?
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.