«

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

"hab" <> wrote in message
news:d1e8g7$j6i$...
> 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(,
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();
}
}

--
Tom Dyess
OraclePower.com

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

"Tom Dyess" <> wrote in message
news:FFA_d.54435$%.. .
> "hab" <> wrote in message
> news:d1e8g7$j6i$...
>> 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(,
> 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();
> }
> }
>
> --
> Tom Dyess
> OraclePower.com
>

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.

--
Tom Dyess
OraclePower.com