J2EEWorld.com - J2EE News, Forums, Blogs, Articles, Books, Jobs

SoloSunglasses

Hit Counter

Visitors: 254534

Written by Java boy

Wednesday, 26 January 2005

Recently, I was doing a task would should avoid multiple transactions coming from different servers interfere each other. Since we also require to record the time of the task, I proposed using Oracle Timestamp type to keep the data integrity of multiple transactions.

Below table was introduced to keep the Server_status

SERVER_NAME

LAST_PROCESS_TIME

JOB_NAME

The pseudo SQL statement which ensures the data integrity of the hold back order transaction as below:

UPDATE SERVER_STATUS

SET LAST_PROCESS_TIME = LOCALTIMESTAMP AND

SERVER_NAME= my_server_name

WHERE

LOCALTIMESTAMP - last_process_time > 1 minute AND

TASK_NAME =”my_task”

COMMIT

IF updated_record_number != 0 THEN

//the above logic is to avoid too many overlapping transactions, which could possibly consuming DB resources

BEGIN TRANSACTION

GET last_process_time

//do my job here

//below SQL script is to ensure any transactions that longer than 1 min will be failed if there’s another server already //executed the transaction successfully

SELECT * from SERVER_STATUS FOR UPDATE of LAST_PROCESS_TIME nowait

UPDATE SERVER_STATUS

SET LAST_PROCESS_TIME = LOCALTIMESTAMP AND

SERVER_NAME= my_server_name

WHERE

LAST_PROCESS_TIME = last_process_time AND

SYSDATE - last_process_time > 1 minute AND

TASK_NAME =”Hold_back_order”

IF updated_record_number == 0 THEN

DO ROLLBACK

ELSE

DO COMMIT

END TRANSACTION

The executeUpdate() method will return the number of record set that has been updated. If the number is zero, which means another application server is already processing the transaction, then this server just ignore current transaction; otherwise it shall continue the process hold back order transaction.

some sample code as below for reference. Note, the compare of timestamp is a bit tricky.

static String TIMESTAMP_FORMAT = "dd-mon-yyyy HH.MI.SSXFF PM";

con = DriverManager.getConnection(
"jdbc:oracle:thin:@myserver:myport:dbname", USERNAME, PASSWORD);

Statement stmt = con.createStatement();

con.setAutoCommit(false);

   ResultSet rs = stmt.executeQuery("SELECT TO_CHAR(LAST_PROCESS_TIME, '" + TIMESTAMP_FORMAT + "') last_time from server_status");
   if (rs.next())
    last_time = rs.getString("LAST_TIME");
   for (int i=0; i<count; i++) {
    stmt.executeUpdate("UPDATE TEST SET TEST_VALUE=TEST_VALUE+1");
   }
   stmt.executeUpdate("select * from Server_status FOR UPDATE of LAST_PROCESS_TIME nowait");

   String sql= "update Server_status set LAST_PROCESS_TIME= LOCALTIMESTAMP " +
    "where LAST_PROCESS_TIME= TO_TIMESTAMP('" + last_time + "', '" + TIMESTAMP_FORMAT+"')";
   int ret = stmt.executeUpdate(sql);

   if (ret == 0) {
    con.rollback();
   } else {
    con.commit();

reference: http://www.psoug.org/reference/timestamp.html