mysql insert...select statement causing deadlock in concurrent request

Question

MySQL version 5.7 Engine : InnoDB

I have table called "md_waiting_slot_count" and it has following columns:

cell | popupDate | userId | creationTime

Now I have following query:

insert into md_waiting_slot_count 
select cell, '2017-08-31' as pd, 'abnc' as ui, '2017-08-26 15:55:51'  
from 
(select sum(slotcount) as tt, cell from 
( select 0 as slotcount, cell_str as cell, 'master' from cell where 
cell_str in 
("Gujarat_Jamnagar_Jamnagar_00-18_Male","Gujarat_Jamnagar_Jamnagar_19-
22_Male")  

union all  

select slotcount, cell, wting from
(select count(*) as slotcount, cell as cell, 'waiting' as wting from 
md_waiting_slot_count where 
cell in(SELECT cell_str as cell FROM cell where cell_str 
in("Gujarat_Jamnagar_Jamnagar_00-18_Male","Gujarat_Jamnagar_Jamnagar_19-
22_Male")) 
and popupDate='2017-08-31' and creationTime > DATE_SUB(NOW(), INTERVAL 
20 MINUTE) group by cell ) as t1

union all  

select filledslotcount as slotcount, id as cell, 'final' from 
md_slot_count where id in(
SELECT cell_str as cell FROM cell where cell_str 
in("Gujarat_Jamnagar_Jamnagar_00-18_Male","Gujarat_Jamnagar_Jamnagar_19-
22_Male")) 
and popupSlotDate='2017-08-31' ) t group by cell having tt < 4) as ft 
order by cell, pd, ui
on duplicate key update creationTime = "'2017-08-26 15:55:51'";

Here 2 other table also used which are as follow

md_slot_count id| popupDate| state| district| taluka| ageGroup| gender| filledSlotCount

cell cell_str| state| district| taluka| ageGroup| gender

This insert...select statement causing deadlock after 3-4 successful run.

Help me with this. How to see "last deadlock log" in MySQL?

I want to do something like this

Transaction 1 --> evaluate above query --> insert row

Transaction 2 --> evaluate above query --> insert row

Here when second transaction evaluate query it has to consider the data inserted by previous transaction. Here I want to allow max 4 transaction to insert row, no more than that. So the evaluated query allow to insert then only insert.

Now in parallel request if those 2 process of query evaluation and insertion is separate and no consider previous transaction data, then more then 4 transaction can come and insert data.

So the ultimate goal is to

If one transaction begin and read data and fulfil the condition then insert data and mean while no one else make insertion, and as the first transaction complete, the second transaction has to consider all the updated data only. So either complete or nothing for one transaction and other transaction has to wait. I do not achieve in concurrent request, as all read together and so it read old data so all are able to add data in table. So I take this whole in one single query.

Answer 1

You want to insert into md_waiting_slot_count some data calculated from md_waiting_slot_count. So deadlock is unavoidable. Try to create a temporary table containing your values and then insert your values from your temporary table.