MySQL Forums
Forum List  »  InnoDB

Solution for table lock or deadlock in Mysql Database
Posted by: Kalpataru Rath
Date: August 09, 2022 05:57AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Solution for table lock or deadlock in Mysql Database
113
August 09, 2022 05:57AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.