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

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