Deadlock Found In Mysql

Question

I have two procedures, one which UPDATE's a column in a table by selecting and calculating those values from 3 different tables and this procedure runs for every 1 min (24 X 7).

Another procedure, which INSERT's the result into a new table by selecting the data from one of the tables from the above mentioned 3 tables and this procedure runs daily once in the morning.

The issue is when both the procedures happen to run at same time in the morning, there is a deadlock found while one of the transaction is holding a lock on particular key.

How to avoid this ?

update 
table1 as p
right join table2 as a on a.col = p.col
left join table3 as b on a.col = b.col 
and b.date = (select min(tdate) from  table3  where  tdate between date(concat(year(current_date - interval 1 year), '-12-31')) and(current_date) and col = a.col for update)
left join table4 c on a.col = c.col
left join (select col, ifnull(sum(col1), 0) amt from table5 where rdate between date(concat(year(current_date - interval 1 year), '-12-31')) and (current_date) group by col for update) d 
on a.ticker = d.ticker
set  p.col1 = ((round(ifnull(d.amt,0),2) + c.val - b.val) / b.val) * 100


insert into new_table (col1,col2,tr_date)  
select sm.col,s.val,s.tr_date 
from 
table3 as s,
table2 as sm 
where 
sm.col=s.col and
s.val = (select max(val) from table3 as  q where q.tr_date between (current_date-interval 1 year) and (current_date) and q.col =sm.col) 
group by sm.col,s.val

This are the two transactions I am using,

"table3" is the one which is being used in both the transactions and there are "where" conditions being used in both.

Please advise.

Thanks

Answer 1

Deadlock happen when two transactions wait on each other to acquire a lock. Example:

• Tx 1: lock A, then B
• Tx 2: lock B, then A

There are numerous questions and answers about deadlocks. Each time you insert/update/or delete a row, a lock is acquired. To avoid deadlock, you must then make sure that concurrent transactions don't update row in an order that could result in a deadlock. Generally speaking, try to acquire lock always in the same order even in different transaction (e.g. always table A first, then table B).

Another reason for deadlock in database can be missing indexes. When a row is inserted/update/delete, the database needs to check the relational constraints, that is, make sure the relations are consistent. To do so, the database needs to check the foreign keys in the related tables. It might result in other lock being acquired than the row that is modified. Be sure then to always have index on the foreign keys (and of course primary keys), otherwise it could result in a table lock instead of a row lock. If table lock happen, the lock contention is higher and the likelihood of deadlock increases.

Source