Guys,

I have a mysql table(tblrates) which continuously receives insert statements to insert rows into that table.

I have a stored procedure which selects rows from that table fora given criteria and insert into a temp table t process them(say temprates).

This procedure is called from an application. Whenever the application executes the stored procedure the master table(tblrates) gets locked and the lock gets released only after the insert into the temporary table is complete. Because of this the mastre table was not able to catch up with the data and it always lags during the peak hours.

I need both the insert into master table(tbltrates) and procedure to select into the temp table to work simultaneously without locking.Is there a way to avoid this?

Thanks,

I'm not too familiar with MySQL yet, but is there an uncommitted read?What are you doing with the data in the temp table? I've always thought temp tables were one of the worst items ever introduced to DBMS's, as most times they are used as the lazy person's way of doing the work and not the original intent.

The reason for using temp table is that we process those rows and do some calculations for those rows and return those rows as a resultset to the applications.Since there are many claculations based on those rows, i dont want to read those rows everytime from the mastre table.Thats why we store them in temporary table.

Also i checked my 'TRANSACTION ISOLATION LEVEL' and it shows as 'REPEATABLE READ'