Forum Stats

  • 3,873,281 Users
  • 2,266,532 Discussions
  • 7,911,496 Comments

Discussions

Solution for table lock or deadlock in Mysql Database

Kalpataru
Kalpataru Member Posts: 4,619 Bronze Crown

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 ?

Please reply.

Tagged:

Answers

  • zizu#1985
    zizu#1985 Member Posts: 26 Red Ribbon

    Hi,

    From documentation we have:

    "MySQL Cluster does not distinguish between deadlocks and lock wait timeouts as there is no direct deadlock detection. For this reason you may get a deadlock error when it is really a lock wait timeout and vice versa".

    I assume you are using InnoDB engine for your tables (you could check this in information_schema).

    For troubleshooting I recommend to enable getting deadlock details in mysql error log by setting variable innodb_print_all_deadlocks to value ON (by default only last one is visible by using some command).

    Default time for trying to aquire lock is 50 seconds (variable innodb_lock_wait_timeout).

    If this is lock situation increasing this value could be quick win.

    There is very nice article in documenation which could be handy:

    dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks-handling.html