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.