hab said:
Hi all,
I'm looking for a good design pattern of integrity-business rules
implementation in J2EE/EJB. Assumption is that the rules should NOT be in
database layer.
I cannot find any good/detailed information of doing it in EJB in business
layer. So this problem is trivial or noone bother with it or it is
'expert' knowledge which is not likely to be shared.
Let me explain my concerns. Such simple rule as: uniqeness of column in
table (this column is not part of primary key). So normaly I would do:
in 'before insert to DB' I would check if object already exist, if exist
throw error, if not 'commit'.
But if two transactions are done in THE SAME time (creation of object with
duplicated data) it doesn't work (when DB transaction isolataion is set as
READ COMMITTED).
I have feeling that there is a need for some kind of locking here, but
how/where?
Any good solutions for J2EE/EJB? (eventualy Session Beans + Hibernate)
Regards,
Hab
Yes, the applicaiton I work on at my 9-5'er had similar issues. It's a
scheduling system and the problem is allowing two people to schedule in the
same slot at the same time without an overbooking if two schedulers saved it
at the same time. Our solution was to create a lock table as such:
create table resourcelock
( key_resour char(8),
locked number(1),
lockdate date
)
We then had lock methods such as this
String query = "insert into resourcelock (key_resour, locked, lockdate)
values (?, 1, sysdate)";
PreparedStatement ps = connection.prepareStatement(query);
ps.SetString(1, resKey);
int results = results = ps.executeUpdate();
if (results > 0) {
// you got the lock
}
This was freehand typed, so don't throw it in an editor and try to compile
it. It's just to give you an idea. The pattern I have used on several
applications works well and is expanded to retry obtaining the lock and to
remove stale locks. The removal of stale locks is incase an application
halts after it has obtained a lock and before it removes the lock.
To further example ad nausium, I use a lock on my stats table on
OraclePower.com (shameless plug, I know). You'll have to excuse any sloppy
anomolies you find, it was when I was first coding java, but the design
pattern is the same (I used it extensively in Delphi) It goes something like
this:
protected synchronized boolean lockStatDay(java.sql.Date date) throws
Exception {
int tries = 0;
int results = 0;
java.sql.Date lockDate = new java.sql.Date(System.currentTimeMillis());
String query = "update ora_site_stats " +
"set hit_locked = 1, " +
"lock_date = ? " +
"where trunc(hit_date) = trunc(?) " +
"and hit_locked = 0 ";
PreparedStatement ps = conn.connection.prepareStatement(query);
ps.setDate(1, lockDate);
ps.setDate(2, date);
results = ps.executeUpdate();
while ((results == 0) && (tries <= global.lockRetries)) {
ps.close();
tries++;
Thread.sleep(global.lockRetryWait);
ps = conn.connection.prepareStatement(query);
ps.setDate(1, lockDate);
ps.setDate(2, date);
results = ps.executeUpdate();
removeStatLocks();
}
ps.close();
if (results != 0) {
conn.connection.commit();
return true;
}
return false;
}
protected boolean unlockStatDay(java.sql.Date date) throws Exception {
int tries = 0;
int results = 0;
String query = "update ora_site_stats " +
"set hit_locked = 0, " +
"lock_date = null " +
"where hit_date = ?";
PreparedStatement ps = conn.connection.prepareStatement(query);
ps.setDate(1, date);
do {
results = ps.executeUpdate();
tries++;
} while ((results == 0) && (tries <= 5));
ps.close();
if (results != 0) {
conn.connection.commit();
return true;
}
return false;
}
public synchronized void updateStats() throws Exception {
FlexDate lockDate = new FlexDate();
lockDate.setToNow();
lockDate.truncateTime();
addStatDay();
removeStatLocks();
if (lockStatDay(lockDate.getSqlDate())) {
String query = "update ora_site_stats " +
"set hit_count = hit_count + 1 " +
"where hit_date = ? ";
PreparedStatement ps = conn.connection.prepareStatement(query);
ps.setDate(1, lockDate.getSqlDate());
int results = ps.executeUpdate();
unlockStatDay(lockDate.getSqlDate());
ps.close();
}
}