how to make insert in to select query lock free

Hi

In my current project to fetch the report from database, we fire query by using union of multiple tables. As we are using nested query to fetch report, my sql creates a temp file to to do the manipulation of inner query which consists of union of multiple table. Due to this IO operation of the system gets high it results in to many performance issue like slowing down the performance of the other queries due to which transaction time out exception occurs.

So to resolve this issue we are planning to use temporary table instead of inner query. But the problem is we are using "insert in to ..... select * from .." statement. In such cases select statement takes lock on the rows of the table. Since this table is used by other modules of the application it results in to deadlock scenario

.

Can any one tell me, how to make changes in the java code or query so that select statement doesn't take lock on the table.

Please let me know if any one need any other input from my side.

Thanks
Gagan

Whatever MySQL does as you say (I'm not sure about that) might be vendor specific so those might have tested properly to work with high volume of data. Do you have huge amount of data in those tables? Generally most mission critical systems backup old data and keep only the recent (in defined time period) data to make the queries faster. If past data are required then go and query from the backup. Anyway have you tried with any stored procedures?

Hey Vijatha

Data is not old.We do archive the data from database once the data set value goes to 200 million. Since the data set value is dependent on the number of cdr pushes from another application and the cdr table creates on hourly basis. Those tables are dynamic table.And we make union on those table. So the only solution to overcome this problem is to make temp table as defined in the problem description. So I just wan to know is there any way that select quest doesn't take lock. One more thing i wan to add that we are not concerned about any stale data related issue in temp table. So cant we set any isolation level in transaction or just pass the instruction to the query to doesn't take lope.

I hope now you are clear with my issue. Please let me know if you need any more detail from my side.

Thanks
Gagan