Deadlock occurs when competing with Jdbc Repository Update. [DATAJDBC-488]

[spring-projects-issues]

MyeongHyeonLee opened DATAJDBC-488 and commented

Deadlock occurs when UPDATE races with the same ID of an AggregateRoot with an association.

When UPDATE is executed, the WritingContext executes in the following order.

1. deleteReferenced
2. updateRoot
3. insertReferenced

If many requests occur at the same time, a deadlock occurs between the Root and Referenced tables.

Deadlock occurs in the test case below.

https://github.com/spring-projects/spring-data-jdbc/pull/191/files#diff-96c4ebdaa99aec19171b092995e4e765R70

---

Affects: 2.0 M3 (Neumann)

Reference URL: https://github.com/spring-projects/spring-data-jdbc/pull/191/files#diff-96c4ebdaa99aec19171b092995e4e765R70

Referenced from: pull request #191

Backported to: 1.1.5 (Moore SR5)

[spring-projects-issues]

Mark Paluch commented

Care to elaborate on how changing the order of execution fixes the issue? Right now it appeals that the root would hold the lock instead of assuming the DELETE would lock the reference tables

[spring-projects-issues]

MyeongHyeonLee commented

Deadlock can occur when  there is no data in reference table.

• DELETE Reference First Deadlock Scenario

• initial state:  Root id: 1, Reference: empty
• https://github.com/spring-projects/spring-data-jdbc/pull/191/files#diff-96c4ebdaa99aec19171b092995e4e765R72

 

	time 1	time 2	time 3	time 4	time 5	time 6	result
Transaction 1	DELETE Reference (empty NO-LOCK)	UPDATE Root (Acquire Root Table LOCK)	INSERT Reference	Commit
(Release Lock)
Transaction 2		DELETE Reference (empty NO-LOCK)	UPDATE Root (Waiting Transaction 1 Root Table LOCK)		UPDATE Root
(Acquire Root Table LOCK)	INSERT
Reference
(Waiting Transaction 3 Reference Table LOCK)	DEAD LOCK
WITH
TRANSACTION 3
Transaction 3					DELETE Reference
(Acquire Reference Table LOCK)	UPDATE
Root
(Waiting Transaction 2 Root Table LOCK)	DEAD LOCK
WITH
TRANSACTION 2

 

• UPDATE Root First Scenario

	time 1	time 2	time 3	time 4	time 5	time 6	time 7	time 8	time 9	time 10	result
Transaction 1	UPDATE Root
(Acquire Root Table LOCK)	DELETE Reference	INSERT Reference	Commit
(Release LOCK)
Transaction 2		UPDATE Root
(Waiting Transaction 1 Root Table LOCK)			UPDATE Root
(Acquire Root Table LOCK)	DELETE Reference	INSERT
Reference	Commit
(Release
LOCK)
Transaction 3					UPDATE Root
(Waiting Transaction 2 Root Table LOCK)				UPDATE Root
(Acquire Root Table LOCK)	DELETE Reference	...

 

 

[spring-projects-issues]

Mark Paluch commented

Thanks a lot for the detail. That makes a lot of sense. Just waiting for Jens Schauder to approve that we haven't overseen anything that might interfere here

[spring-projects-issues]

Jens Schauder commented

That's a good catch. Thanks for the great work