Deadlock occurs when competing with Jdbc Repository Update and Delete. [DATAJDBC-493]

[spring-projects-issues]

MyeongHyeonLee opened DATAJDBC-493 and commented

Thank you.

An update deadlock issue has been fixed.  DATAJDBC-488

However, Deadlock issues still exist when Update and Delete are executed in concurrent.

The deletion scenario also deletes the reference data first, as in the previous update.

 

1. Delete References
2. Delete Root

 

So Deadlock can happen.

 

	time 1	time 2	result
TX1 UPDATE	UPDATE Root
(Acquire Root Table LOCK)	DELETE References
(Waiting TX2 Reference Table LOCK)	DEAD LOCK
WITH
TX2
TX2 DELETE	DELETE References
(Acquire Reference  Table LOCK)	DELETE Root
(Waiting TX1 Root Table LOCK)	DEAD LOCK
WITH
TX1

 

Unlike Update, it does not solve the problem by changing the execution order.

There may be an FK constraint between the Root and the Reference table.

1. Delete Root (FK Violation)
2. Delete References

 

In order to solve the deadlock problem, LOCK must be acquired first in the root table.

1. UPDATE root SET id = :id WHERE id = :id;  (Acquire Root Table LOCK)
2. Delete References
3. Delete Root

 

OR

 

1. SELECT 1 FROM root WHERE id = :id FOR UPDATE (Acquire Root Table LOCK)
2. Delete References
3. Delete Root

Please comment on which solution is right for you.

 

I wrote failing test for this.

#195

And the two solutions suggested above pass this failing test.

 

 

---

Affects: 2.0 M3 (Neumann)

Reference URL: https://jira.spring.io/browse/DATAJDBC-488?focusedCommentId=188174&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-188174

Referenced from: pull request #195

[spring-projects-issues]

Jens Schauder commented

I'd prefer the second solution.
With it, it is more obvious why that statement is executed

[spring-projects-issues]

MyeongHyeonLee commented

To solve this problem, I will divide it into three steps.

 

Step 1. Add LockClause to Dialect (#196)

 

Step 2. Add methods for Select With Lock

• SqlGenerator
• DataAccessStrategy

 

Step 3. Apply Lock the  root when the delete operation is executed

• resolve this issue