I have a stored procedure which does the billing stuff in our system,it works ok,but if I put in production,where there is some 5-10 billing events per second,the whole database slows down. It won't even drop some test table,reindex,vacuum,things which were done before in the blink of an eye. If I stop the application which calls the procedure,all is back to normal.
We didn't implement any special locking mechanism in the procedure,all is default. The procedure is updating user's balance in table 'users'. On the other hand a couple of 'heavy load' table has foreign keys pointing to table 'users'.
Is it the matter of concurency and some locking issue or maybe the existing of all those foreign keys pointing to table 'users',or maybe something else which we're not aware at the moment ?
Can you please post your procedure and explain plan of the SQL which the procedure uses to do the billing stuff . There can be a zillion reasons for the performance problems you are seeing, but the email does not provide enough information.