EJB - integrity (business) rules implementation (like unique)

H

hab

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
 
T

Tom Dyess

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();
}
}
 
H

hab

I think the locking can be done as Java Object as well.
But in such solution (lock) always following problems appear:
- deadlocks;
- forgotten/unreleased locks

My idea it to use somehow Entity Bean for locking - it is managed by
the container, so above problems would not appear - but in fact I miss
the knowledge how exactly it works.
Hab
 
T

Tom Dyess

Tom Dyess said:
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();
}
}

The initial solution does require a unique constraint on the lock table. The
first example was written in haste. I'll try to write something better this
afternoon. The second example is used on the site currently and works like a
charm.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top