Need some advice on preventing concurrent access while truncating tables

["Bergquist, Brett" <BB...@canoga.com>]

I am implementing a poor man's data partitioning scheme.  I have tables for each week of the year (ie. NPA_RESULTS_WEEK_1, NPA_RESULTS_WEEK_2, .. etc).   I have a view NPA_RESULTS that joins these tables back together for read-only access.

I have a Java Persistence Application (JPA) that is inserting data into the proper week constantly, 24x7, with an insertion rate around 50 records per second.   Once a week I have a Quartz scheduled job that goes off an purges an older weeks of data by using the TRUNCATE TABLE statement.     The number of weeks to keep around is configurable so for example, if set to 4, it will truncate all of the tables except the last 4 weeks of data.

What I would like to do is to prevent the NPA_RESULTS View and the TRUNCATE TABLE from interfering with one another.  Preferably I would like the TRUNCATE TABLE to wait or retry if there is a current use of the NPA_RESULTS View in process.   It is easy to catch SQL exceptions in the purge and check the SQLState and retry but it is much harder in the JPA code since is it insulated from the low level database code.

So any suggestions on implementing some sort of locking scheme that could satisfy this will be much appreciated.

Brett