java - SELECT FOR UPDATE query with Spring's @Transaction management creates deadlock upon subsequent updates -

setup: spring application deployed on weblogic 12c, using jndi lookup datasource oracle database.

we have multiple services polling database regularly new jobs. in order prevent 2 services picking same job using native select update query in crudrepository. application takes resulting job , updates processing instead of waiting using crusrepository.save() method.

the problem can't seem save() work within for update transaction (at least current working theory of goes wrong), , result entire polling freezes until default 10 minute timeout occurs. have tried putting @transactional (with various propagation flags) everywhere, i'm not able work (@enabletransactionmanagement activated , working).

obviously there must basic knowledge i'm missing. possible setup? unfortunately, using @transactional non-native crudrepository select query not possible, apparently first makes select see if row locked or not, , then makes new select locks it. service pick same job in meanwhile, why need lock immediately.

---

update in relation @m. deinum's comment.: should perhaps mention it's setup wherein central component that's doing polling library used other services (therefore library has @springbootapplication, each service using it, double component scanning present). furthermore, service has 2 separate classes polling depending on type of service, lot of common code, shared in abstracttransactionhelper class. below i've aggregated code sake of brevity.

the library's main class:

@springbootapplication @enabletransactionmanagement @enablejparepositories public class jobsmain {  public static void initializejobsmain(){     persistenceproviderresolverholder.setpersistenceproviderresolver(new persistenceproviderresolver() {          @override         public list<persistenceprovider> getpersistenceproviders() {             return collections.singletonlist(new hibernatepersistenceprovider());         }          @override         public void clearcachedproviders() {             //not quite sure should do...         }     }); }  @bean public jtatransactionmanager transactionmanager(){     return new weblogicjtatransactionmanager(); }  public datasource datasource(){     final jndidatasourcelookup dslookup = new jndidatasourcelookup();     dslookup.setresourceref(true);     datasource datasource = dslookup.getdatasource("jobs");     return datasource; }  } 

the repository (we're returning set 1 job had other issues when returning single object):

public interface jobrepository extends crudrepository<job, integer> {  @query(value = "select * job job.id in "                     + "(select id "                         + "(select * job "                             + "job.status = :status1 or "                             + "job.status = :status2 "                         + "order job.priority asc, job.created asc) "                     + "where rownum <= 1) "                 + "for update", nativequery = true) public set<job> getnextjob(@param("status1") string status1, @param("status2") string status2); 

the transaction handling class:

@service public class jobmanagertransactionhelper extends abstracttransactionhelper{  @transactional @override public qdbjob getnextjobtoprocess(){         set<job> jobs = null;         try {             jobs = jobrepo.getnextjob(status.done.name(), status.failed.name());         } catch (exception ex) {             logger.error(ex);         }     return extractsinglejobfromset(jobs); } 

---

update 2: more code.

abstracttransactionhelper:

@service public abstract class abstracttransactionhelper {  @autowired qdbjobrepository jobrepo;  @autowired archivedjobrepository archive;  protected job extractsinglejobfromset(set<job> jobs){     job job = null;     if(jobs != null && !jobs.isempty()){         for(job job : jobs){             if(this instanceof jobmanagertransactionhelper){                 updatejob(job);             }             job = job;         }     }     return job; }  protected void updatejob(job job){     updatejob(job, status.processing, null); }  protected void updatejob(job job, status status, string servicemessage){     if(job != null){         if(status != null){             job.setstatus(status);         }         if(servicemessage != null){             job.setservicemessage(servicemessage);            }         savejob(job);     } }  protected void savejob(job job){     jobrepo.save(job);     archive.save(job.convertjobtoarchivedjob(job)); } 

---

update 4: threading. newjob() implemented each service uses library.

@service public class jobmanager{  @autowired private jobmanagertransactionhelper transactionhelper;  @autowired joblistener joblistener;  @autowired config config;  protected final atomicinteger atomicthreadcounter = new atomicinteger(0);  protected boolean keeppolling;     protected future<?> futurepoller; protected scheduledexecutorservice pollservice; protected threadpoolexecutor threadpool;  public boolean start(){     if(!keeppolling){         threadfactory pollservicethreadfactory = new threadfactorybuilder()             .setnameprefix(config.getservice() + "scheduledpollingpool-thread").build();         threadfactory threadpoolthreadfactory = new threadfactorybuilder()                             .setnameprefix(config.getservice() + "threadpool-thread").build();         keeppolling = true;         pollservice = executors.newsinglethreadscheduledexecutor(pollservicethreadfactory);         threadpool = (threadpoolexecutor)executors.newfixedthreadpool(getconfig().getthreadpoolsize(), threadpoolthreadfactory);         futurepoller = pollservice.schedulewithfixeddelay(getpolltask(), 0, getconfig().getpollingfrequency(), timeunit.milliseconds);         return true;     }else{         return false;     } }  protected runnable getpolltask() {     return new runnable(){         public void run(){             try{                 while(atomicthreadcounter.get() < threadpool.getmaximumpoolsize() &&                          threadpool.getactivecount() < threadpool.getmaximumpoolsize() &&                          keeppolling == true){                     job job = transactionhelper.getnextjobtoprocess();                     if(job != null){                         threadpool.submit(getjobhandler(job));                         atomicthreadcounter.incrementandget();//threadpool.getactivecount() isn't updated fast enough first loop                     }else{                         break;                     }                 }             }catch(exception e){                 logger.error(e);             }         }     }; }  protected runnable getjobhandler(final job job){     return new runnable(){         public void run(){             try{                 atomicthreadcounter.decrementandget();                 joblistener.newjob(job);             }catch(exception e){                 logger.error(e);             }         }     }; } 

as turns out, problem weblogicjtatransactionmanager. guess for update resulted in jpa transaction, upon updating object in database, weblogicjtatransactionmanager used, failed find ongoing jta transaction. since we're deploying on weblogic wrongly assumed had use weblogicjtatransactionmanager.

either way, exchanging transactionmanager jpatransactionmanager (and explicitly setting entitymanagerfactory , datasource on it) solved problems.

@bean public platformtransactionmanager transactionmanager() {     jpatransactionmanager jpatransactionmanager = new jpatransactionmanager(entitymanagerfactory().getobject());     jpatransactionmanager.setdatasource(datasource());     jpatransactionmanager.setjpadialect(new hibernatejpadialect());     return jpatransactionmanager; } 

assuming have added entitymanagerfactorybean needed if want use multiple datasources in same project (which we're doing, not within single transactions, no need jta).

@bean public localcontainerentitymanagerfactorybean entitymanagerfactory() {      hibernatejpavendoradapter vendoradapter = new hibernatejpavendoradapter();      localcontainerentitymanagerfactorybean factorybean = new localcontainerentitymanagerfactorybean();      factorybean.setdatasource(datasource());     factorybean.setjpavendoradapter(vendoradapter);     factorybean.setpackagestoscan("my.model");      return factorybean; }