[Data Base] Case study with frequent busy conditions


In the first new development project, an error log in a busy state frequently occurred with the function I manufactured.


As a result of the investigation, the cause was that deadlock occurred between other functions.

Survey method

The order of applying pessimistic locks was identified. Example) Promotion function: Confirm the target employee → Confirm the store manager group to which you belong → Select the assigned area → Select the assigned store        t_staff → t_maneger_group → t_area → t_shop Retirement function: Confirmation of affiliated store → Confirmation of superior of target employee → Deletion of target employee        t_shop → t_manger_group → t_staff

(It may be difficult to understand if you compare it with force, but I will write it down.)


It is common to manage the table by id etc. and unify the lock order for the entire project.


To some extent, it was obvious that the cause was deadlock, but I was at a loss because I couldn't find the function that caused it. When I had no choice but to ask my seniors for help, I could easily find the function of the cause, so as a memorandum of investigation method.

This time, the schedule was too tight and I started running loosely with the coding rules. Many other annoying things happened. I thought it might be annoying to make it with a sword, but I learned a lot because there are many rudimentary mistakes when it's too freedom.

I will give it to you without knowing it in the first post, but I hope you can read it to the end.

Recommended Posts

[Data Base] Case study with frequent busy conditions