I have 2 tables, Movement and Movement_encoded_names, Movement_encoded_names has a FK movementId pointing to Movement table.

1. Deadlock happens when I call JPARepository.deleteByMovementNumber(String movementNumber), movementNumber is a column of Movement table (index on for movementNumber).

2. I have @oneToMany(fetch = Fetch.LAZY, orphanRemoval = true, mappedBy = 'Movement', cascade = CascadeType.ALL) annotated in Movement JPA definition

3. Deadlock happens when apps running on multiple machines so delete happens concurrently. But each delete deletes different rows on Movement and thus different rows on MovementEncodedNames.

4. Turned on log for deadlock and it shows 4 participants, 1 wait for 2, 2 for 3, 3 for 4 and 4 for 1 so dead lock.

• Participant 4 running delete from movement_code_names where movement_coded_name_id = ?
• Participant 1 running delete from movement where movement_id = ?
• Participant 2 running delete from movement_code_names where movement_coded_name_id = ?
• Participant 3 running delete from movement where movement_id = ?

6

• Participant 4 locak specifics: ROWID = 187, DATA_PARTITION_ID = 0, PAGEID = 141
• Participant 3 locak specifics: ROWID = 187, DATA_PARTITION_ID = 0, PAGEID = 141
• Participant 1 locak specifics: ROWID = 132, DATA_PARTITION_ID = 0, PAGEID = 141
• Participant 2 locak specifics: ROWID = 132, DATA_PARTITION_ID = 0, PAGEID = 141

I found some comments say to delete a record in Movement, scan will be done to find all of movement_encoded_names referring to it. And I don't have an index on MovementEncodedNames.movement_id, the scan will be slow so likely cause the issue?

I'll try to add an index to Movement_encoded_names.movemnt_id. But at same time, is there any code change I can do to fix the issue? thanks

And if possible, can any one help to explain how this deadlock happens, there are only 2 tables involved and I don't see how circular dependency can happen.