Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
spring-projects-issues opened this issue Feb 22, 2020 · 2 comments
Assignees
Labels
in: relational in: repository type: enhancement

Comments

@spring-projects-issues
Copy link

@spring-projects-issues spring-projects-issues commented Feb 22, 2020

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
Copy link
Author

@spring-projects-issues spring-projects-issues commented Feb 24, 2020

Jens Schauder commented

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

@spring-projects-issues
Copy link
Author

@spring-projects-issues spring-projects-issues commented Feb 26, 2020

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

 

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
in: relational in: repository type: enhancement
Projects
None yet
Development

No branches or pull requests

2 participants