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