How to acquired a lock at database row level using in Spring Boot and Hibernate?-CodePudding

I am reading a single record from the database and updating it for which I have 2 methods. But between these 2 operations, I want to acquire a lock on that particular row so that different threads will not process the same row.

Here is my code for the repository :

        package com.anco.crud.repository;

import com.anco.crud.model.Employee;
import org.hibernate.Session;
import org.hibernate.query.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;

@Repository
public class EmployeeRepositoryImpl implements EmployeeRepository{


    @Autowired
    private EntityManager entityManager;

@Override
@Transactional
public Employee findEmpToProcessAndUpdateStatus() {
    Session currentSession = entityManager.unwrap(Session.class);

    String hql = "from Employee where status is null";
    Query query = currentSession.createQuery(hql);
    query.setMaxResults(1);
    Employee employee = (Employee) query.uniqueResult();
    System.out.println("FETCHED : "   employee.getFirstName());
    employee.setStatus("INPROGRESS");
    updateEmployee(employee);
    return employee;
}

private boolean updateEmployee(Employee employee) {
    try{
        Thread.sleep(3000);
        Session currentSession = entityManager.unwrap(Session.class);
        currentSession.update(employee);
        System.out.println("UPDATED : "   employee.getFirstName());
    }catch (Exception e){
        e.printStackTrace();
    }
    return true;
}
}

Controller code :

    @GetMapping("/employee/{mail}")
    public boolean getAndUpdate() {
        for(int i=0;i<5;i  ){
            Runnable rn = () -> process();
            this.executor.execute(rn);
        }
        return true;
    }

    private void process() {
    try {
        Employee emp = employeeRepository.findEmpToProcessAndUpdateStatus();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

if this is not the correct way then pls suggest any better approach ...

CodePudding user response：

You can set a specific lock mode in order to issue a 'select for update' to the DBMS:

EntityManager em;
...
Employee found = em.find(Employee.class, empId, LockModeType.PESSIMISTIC_WRITE);

CodePudding user response：

First of all use Spring to your advantage, you are currently working around it. You should have a single transactional method that does the work. Next just use JPA instead of directly accessing the Hibernate Session that doesn't add anything but confusion/complexity here. Finally use the setLockMode on the query to determine the locks.

Your repository method (or service) should look something like this

@Repository
public class EmployeeRepositoryImpl implements EmployeeRepository{


  @PersistenceContext
  private EntityManager em;

  @Override
  @Transactional
  public Employee findEmpToProcessAndUpdateStatus() {

    String hql = "SELECT e FROM Employee e where e.status is null";
    TypedQuery<Employee> query = em.createQuery(hql, Employee.class);
    query.setMaxResults(1);
    query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
    Employee employee = query.getSingleResult();
    System.out.println("FETCHED : "   employee.getFirstName());
    employee.setStatus("INPROGRESS");
    this.updateEmployee(employee);
    return employee;
  }

  private boolean updateEmployee(Employee employee) {
    try{
        Thread.sleep(3000);
        em.merge(employee);
        System.out.println("UPDATED : "   employee.getFirstName());
    }catch (Exception e){
        e.printStackTrace();
    }
    return true;
  }
}

However this could cause deadlocks on your database with all those locks. So