Hi Experts,
Our application has some back-end jobs that are triggered by a set of 4 external Schedulers. So, at every half-hour all 4 schedulers send requests to our app for triggering the job. The requirement is that only one out of these 4 requests should do the actual processing, rest three should not do anything.
To achieve this, I tried to implement a database locking mechanism where I crated following Table in Oracle:
Code:
CREATE TABLE T_LOCKS
(
JOB_ID INTEGER NOT NULL,
IS_LOCKED CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
UPDATE_DATE TIMESTAMP(6) NULL
)
this is the mapping file:
Code:
<hibernate-mapping>
<class name="com.test.domain.TLocks" table="T_LOCKS" schema="test">
<id name="jobId" type="java.math.BigDecimal">
<column name="JOB_ID" precision="22" scale="0" />
<generator class="assigned" />
</id>
<property name="isLocked" type="java.lang.String">
<column name="IS_LOCKED" length="1" />
</property>
<property name="updatedDate" type="java.sql.Timestamp">
<column name="UPDATED_DATE"/>
</property>
</class>
</hibernate-mapping>
Inside DAO class, I've this method which is called by each of the 4 scheduler requests before doing the core job processing. The idea is to let each request try to update the
UPDATED_DATE value with
SYSTIMESTAMP for same T_Locks record ,and whichever request is able to update the record first would do the actual processing. To ensure the single update of
UPDATED_DATE value, I've included a check to ensure that the
UPDATED_DATE must be at least
29 minutes less than the
SYSTIMESTAMP :
Code:
@Transactional (timeout=30, rollbackFor=Exception.class)
public boolean lockRowForUpdate(Integer job_id)
{
try
{
String queryString = "select IS_LOCKED from T_LOCKS WHERE JOB_ID=? FOR UPDATE NOWAIT";
Query queryObject = getSession().createSQLQuery(queryString);
queryObject.setParameter(0, job_id);
Character res = (Character)queryObject.uniqueResult();
if("N".equals(res.toString())){
queryString = "UPDATE T_LOCKS set IS_LOCKED='Y', UPDATE_DATE=SYSTIMESTAMP WHERE JOB_ID=? AND (UPDATE_DATE IS NULL OR (( SYSTIMESTAMP - UPDATE_DATE) > NUMTODSINTERVAL (?, 'MINUTE')))";
queryObject = getSession().createSQLQuery(queryString);
queryObject.setParameter(0, job_id);
queryObject.setParameter(1, 29);
int updateCnt = queryObject.executeUpdate();
if(updateCnt == 1)
return true;
else
return false;
}else{
return false;
}
}
catch (RuntimeException re) {
log.error("lockRowForUpdate failed for job_id:"+job_id, re);
return false;
}
}
Also, our app server(with Pacific time set) and DB server are in Arizona. For DB I checked DBTimezone and SYSTIMESTAMP, and found this:
DBTimezone :
-7:00SYSTIMESTAMP:11/8/2011 8:59:01.391562 AM
-08:00This code worked perfectly fine(only one of 4 requests was returning true from above method), until 11/06 morning when Day Light Savings ended. Now, each of the 4 requests are returning true from above method hence job processing is being done by All 4. My guess is that check
( SYSTIMESTAMP - [b]UPDATE_DATE) > NUMTODSINTERVAL (?, 'MINUTE'))[/b] could be where the problem is, but not sure...
Could you please tell me how to fix this issue?
Thanks in Advance.
KT
Login
FAQ
Search