db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Need some advice on preventing concurrent access while truncating tables
Date Wed, 20 Jul 2011 15:03:13 GMT
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

Mime
View raw message