Hello,

I write a java app that starts some threads which in turn run in an endless loop trying to select rows of an Oracle table every few seconds.

Since the same code runs in a cluster environment and I have to prevent rows of the DB table to be processed twice, so the first thread that selects the rows locks it :

select ... from table1 where ID = ? for update skip locked.

Now, when the 2nd, 3rd, ... threads are trying to select and lock the row, they return nothing because of the 'skip locked' at the end of the statement.

so the (pseudo) code looks like this :

Long ID = ... // get ID from somewhere
Connection cn = .. // get sql connection
cn.setAutocommit(false); // start transaction

MyObject selectedRow = selectRow(cn, ID); // select with 'for update skip locked' at the end

if (selectRow == null) { // the row is already locked
1: cn.commit();
return;
}

boolean success = doSomeStuffWithMyObject(selectedRow);
if (success) {
updateStatusColumn(cn, ID);
cn.commit();
} else {
cn.rollback();
}


Questions :
1) At 1: a transaction has started, but only a 'select for update' has been issued. Should I commit or rollback here ? Or is the result the same at this point ?
In other words, I have to commit or rollback since the row is locked at that point, right ? So, rollback is fine, but what about committing after selecting a row for update but do no update at all ?

2) if there's no difference between commit and rollback, I could just write :

boolean success = doSomeStuffWithMyObject(selectedRow);
if (success) {
updateStatusColumn(cn, ID);
}
cn.commit();



I need your advices on these issues, would be great if someone would comment about it.

(Btw, the code works, but I want / have to be sure.)