Solution for table lock or deadlock in Mysql Database

[Kalpataru Rath]

Hi All,

We are using MySQL Database version 8.0.30 and it's running on AWS cloud platform. I am new to MySQL Database.

Basically Our application built in Java with spring boot, and this application is a database driven app. Every where from this app the MySQL store procedures are written for the business logic and for do select, update and inserting data to or from the MySQL DB.

There are so many around 500 to 600 users are using this app.

Suppose for one user executing the following SPs(Store procedures).

Procedure P1

Begin

INSERT INTO sp_ev_log();

UPDATE sp_det;

UPDATE sp_pro_status;

SELECT * FROM sp_ev_log;

END P1;



The below procedures is called from the app at the same time for other other purposes.

Procedure P2

UPDATE sp_det;

Call procedure P3;

In P3 also the same table is updating

UPDATE sp_det;

Some other stuffs are done here.

End Calling P3;

Call P4;

UPDATE sp_det;

Some other stuffs are done here.

End Calling P4;

UPDATE sp_ev_stg seg

JOIN sp_ev se

Where done;

INSERT INTO sp_ev;

SELECT Some columns

FROM sp_ev_stg seg

LEFT JOIN sp_ev se;

DELETE FROM sp_ev se;

UPDATE sp_ev se

JOIN mas_ev me

ON se.ev_code = me.ev_code

JOIN (

SELECT Some_Columns

FROM sp_ev se1

WHERE se1.sp_id = sp_id

) ise

ON se.sp_id = ise.sp_id

AND se.ev_code = ise.ev_code;



Some other codes and logic here;

Then

DELETE FROM sp_ev se;

END procedure P2;

At last from the java app error i am getting the following error

2022-08-02T16:36:49.122+05:30 2022-08-02 11:06:49.122 ERROR 9 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : Deadlock found when trying to get lock; try restarting transaction



If run the following command in MySQL workbench then the following tables are locked

for so many times

show open tables where in_use > 1;


What is best solution to this deadlock problem and how to solve this ?

Regards
Kalpataru