Understanding InnoDB X-Lock with REPEATABLE_READ and READ_COMMITED isolation level

I have two mysql(AWS Aurora) db tables:

-------------------------------- |     Table:deparment          | -------------------------------- -------------------------------- |    id      |  dept_name      | -------------------------------- |     d1     |       dept1     | -------------------------------- |     d2     |       dept2     | -------------------------------- --------------------------------  ------------------------------------------------- |                Table:employee                 | ------------------------------------------------- ------------------------------------------------- |   id      |   emp_name       |    dept_id     | ------------------------------------------------- |   e1      |   Emp 1          |    d1          | ------------------------------------------------- |   e2      |   Emp 2          |    d1          | ------------------------------------------------- |   e3      |   Emp 3          |    d1          | ------------------------------------------------- |   e4      |   Emp 4          |    d2          | ------------------------------------------------- 

Business logic: Multiple thread can perform create/modify or delete operation on Depatment and Employee table. Any modification on employee table will result updating the employee id

------ Java Code with Spring Transaction:  /** *   Modify Employee */ @Transactional(isolation = REPEATABLE_READ) public void modifyEmployeeByDeptarment(int deptId, int empId Update upd) {     Department dept = deptDB.getDeparmentForUpdate(deptId); // returns SELECT * FROM department where id = :deptId FOR UPDATE; Row level Lock on dept          Employee employee = empDB.getEmployee(deptId, employee.id); // returns SELECT * from employee where dept_id = :depltId;          employee.setId(newEmpId);     addUpdate(employee, upd); // This will update in DB }  /** *   Delete department with employees */ @Transactional(isolation = REPEATABLE_READ) public void deleteDepartment(int deptId) {     Department dept = deptDB.getDeparmentForUpdate(deptId); // returns SELECT * FROM department where id = :deptId FOR UPDATE; Row level Lock on dept      List<Employee> employees = empDB.getEmployeesByDept(deptId); // returns SELECT * from employee where dept_id = :depltId;      deleteDepartment(dept); // Deletes dept from DB     deleteEmployees(employees); // Delete all employee for deptId } ------ 

Few cases, It has been observed that employee record was prsent in our database without its corresponding department.

Can someone please exaplain this behaviour?

Spring Framework version: 5.2.0.RELEASE Aurora MySQL Version: 5.7.12

* Note: We changed the isolation level to READ_COMMITED, and everything was working as expected. 
Add Comment
0 Answer(s)

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.