java - SELECT FOR UPDATE locking -

i'm using hibernate data persistence, , i'm counting on select update sql statement locking system ensure 1 thread working on task, when internet connection interrupted, threads blocked in select update statement.

my theory: first thread gets in lock function, executes sql statement, , puts lock, , before committing changes database table, connection gets interrupted thread fails commit, , unlock table, therefor lock keeps living, , when next threads execute sql statement, blocked.

in code snippet bellow, task inside while block, have represented "..."

code snippet

while(lockisok()){     ... } 

and lockisok() function handles lock.

code snippet

private boolean lockisok(){     session session = null;         transaction transaction = null;         try {             session = hibernateutil.getsessionfactory().getcurrentsession();             transaction = session.begintransaction();             sqlquery request = daofactory.getcurrentsession().createsqlquery("select * synchro_status id = 1 update ");              list<object> resultlist = request.addentity("synchro_status", synchrostatusmodel.class).list();             if(!resultlist.isempty()) {                 synchrostatusmodel docsynchrostatusmodel = (synchrostatusmodel) resultlist.get(0);                 updatesync(docsynchrostatusmodel);                 transaction.commit();                 return true;                 } else {                 try {                     thread.sleep(configurator.getpropertyasint(solrindexerforstiproperties.sleep_time));                 } catch (interruptedexception e) {                     // restore interrupted status                     thread.currentthread().interrupt();                     exploitlogger.error(exploiterror.execution_failed, severityerror.minor, "error while sleeping thread.", e, stiexploitid.id_50114);                 }             }             // commit transaction             transaction.commit();         } catch (exception e) {             // rollback transaction             exploitlogger.error(exploiterror.unattempted_error, severityerror.major, "error while checking synchronization status table.", e, stiexploitid.id_50115);             hibernatetransactionhelper.rollbacktransaction(transaction);         } {             if (session.isopen()) {                 session.close();             }         }         return false; }  private void updatesync(){         synchrostatusdao synchrostatusdao = daofactory.getsynchrostatusdao();         string stiname = managementfactory.getruntimemxbean().getname();         docsynchrostatusmodel.setstiname(stiname);         docsynchrostatusmodel.setsynchroenabled(1);         docsynchrostatusmodel.setlastactiondate(calendar.getinstance().gettime());         synchrostatusdao.update(docsynchrostatusmodel); } 

i can provide log file if needed.

questions :

• is theory reasonable ?
• if how can solve problem ?

your theory reasonable , correct.

i 2 things fix situation.

1. if possible, reduce amount of time hold transaction open reduce vulnerability broken connections.

2. if database allows, set database connection timeout time. way, database not hold on broken connections indefinitely. note solution may require code changes in application application can either keep connections open or reconnect appropriate.