I'm trying to solve a deadlocking issue that happens with some automated jobs kicked off via an ETL job (through Pentaho) and separately through Looker. As a result, we have some, but not tons of control over the timing and connection settings of the queries.
I think I read elsewhere on Stackoverflow about INSERT deadlocking with SELECT if there aren't indexes available other than the primary key, causing the SELECT to lock the table. We've had plenty of indexes on this table, but this still deadlocks at least once a day (running hourly).
Note: Query #1 already runs in 200ms. Query #2, on the other hand, runs hundreds of times over the course of a minute while the ETL job inserts new rows from another system.
I've pasted the full MySQL deadlock log at the bottom.
Query #1
select dit.territory_code
, dit.region
, dit.region_type
, dit.rep_name
, diq.month_starting
, sum(quota) as quota
, coalesce(sum(net_revenue), 0) as net_revenue
from dim_intuitive_territory dit
inner join dim_intuitive_quota diq
on diq.intuitive_territory_key = dit.intuitive_territory_key
left join
(
select date_format(som_salesorderdate, '%Y-%m-01') as sale_month_starting_date
, dist.intuitive_territory_key
, sum(case when fsol.sod_shipcomplete = 1 then fsol.shl_shipqty else fsol.sod_requiredqty end * SOD_UnitPrice * (1 - SOD_DiscPercent)) as net_revenue
from fct_intuitive_sales_order_line fsol
inner join dim_intuitive_ship_to dist
on fsol.master_intuitive_ship_to_key=dist.intuitive_ship_to_key
where som_salesorderdate >= '2017-05-01'
and fsol.deleted_flag = 0
and fsol.ivm_reversedflag != 1
and fsol.soi_cancelledflag != 1
and fsol.intuitive_item_key != '37819'
group by 1, 2
) b
on dit.intuitive_territory_key = b.intuitive_territory_key
and b.sale_month_starting_date = diq.month_starting
where dit.active_flag = 1
and diq.month_starting >= '2017-05-01'
group by 1, 2, 3, 4, 5
Explain for Query #1:
Query #2
UPDATE fct_intuitive_sales_order_line
SET SOI_RecordID = ?
, SOM_SalesOrderID = ?
, SOM_SalesOrderDate = ?
, SOI_LineQty = ?
, SOD_DiscPercent = ?
, SOD_RequiredQty = ?
, SOD_UnitPrice = ?
, ShipDate = ?
, intuitive_customer_key = ?
, intuitive_item_key = ?
, SOI_PromoCode = ?
, IVM_ReversedFlag = ?
, SOI_MiscLineDescription = ?
, intuitive_ship_to_key = ?
, CSA_RecordID = ?
, SOI_CancelledFlag = ?
, SOM_CustomerPOID = ?
, IVL_COGSAmt = ?
, ShipName = ?
, SOM_LastModifiedDate = ?
, deleted_flag = ?
, SOD_ShipComplete = ?
, SHL_ShipQty = ?
, shipment_lines = ?
, IVM_InvoiceID = ?
WHERE ( ( SOI_RecordID = ? ) ) AND ( ( SOI_LineQty = ? ) ) AND ( ( SOD_RequiredQty = ? ) ) AND ( ( IVM_ReversedFlag = ? ) ) AND ( ( IVM_InvoiceID = ? ) )
Full deadlock log:
2017-05-15T16:10:48.401677Z 24288 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2017-05-15T16:10:48.403477Z 24288 [Note] InnoDB:
*** (1) TRANSACTION:
TRANSACTION 38383190, ACTIVE 0 sec fetching rows
mysql tables in use 5, locked 5
LOCK WAIT 262 lock struct(s), heap size 24784, 11094 row lock(s)
MySQL thread id 24297, OS thread handle 139943486347008, query id 19590255 localhost 127.0.0.1 looker Sending data
-- Building field_sales::intuitive_territory_performance on instance 57faff1a6bea6b81599a8afb67add055
CREATE TEMPORARY TABLE looker_scratch.intuitive_territory_performance
<replaced with full query. Mysql left a truncated one>
select dit.territory_code
, dit.region
, dit.region_type
, dit.rep_name
, diq.month_starting
, sum(quota) as quota
, coalesce(sum(net_revenue), 0) as net_revenue
from dim_intuitive_territory dit
inner join dim_intuitive_quota diq
on diq.intuitive_territory_key = dit.intuitive_territory_key
left join
(
select date_format(som_salesorderdate, '%Y-%m-01') as sale_month_starting_date
, dist.intuitive_territory_key
, sum(case when fsol.sod_shipcomplete = 1 then fsol.shl_shipqty else fsol.sod_requiredqty end * SOD_UnitPrice * (1 - SOD_DiscPercent)) as net_revenue
from fct_intuitive_sales_order_line fsol
inner join dim_intuitive_ship_to dist
on fsol.master_intuitive_ship_to_key=dist.intuitive_ship_to_key
where som_salesorderdate >= '2017-05-01'
and fsol.deleted_flag = 0
and fsol.ivm_reversedflag != 1
and fsol.soi_cancelledflag != 1
and fsol.intuitive_item_key != '37819'
group by 1, 2
) b
on dit.intuitive_territory_key = b.intuitive_territory_key
and b.sale_month_starting_date = diq.month_starting
where dit.active_flag = 1
and diq.month_starting >= '2017-05-01'
group by 1, 2, 3, 4, 5
</replaced>
2017-05-15T16:10:48.404177Z 24288 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 117699 n bits 128 index PRIMARY of table `colorescience2`.`fct_intuitive_sales_order_line` trx id 38383190 lock mode S locks rec but not gap waiting
Record lock, heap no 25 PHYSICAL RECORD: n_fields 46; compact format; info bits 0
0: len 4; hex 001559e4; asc Y ;;
1: len 6; hex 00000249ae52; asc I R;;
2: len 7; hex 7100008faa12b9; asc q ;;
<snip what looks like a bunch of useless rows>
2017-05-15T16:10:48.410207Z 24288 [Note] InnoDB: *** (2) TRANSACTION:
TRANSACTION 38383186, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
96 lock struct(s), heap size 24784, 111 row lock(s), undo log entries 38
MySQL thread id 24288, OS thread handle 139943487411968, query id 19590590 localhost 127.0.0.1 dbuser Searching rows for update
UPDATE fct_intuitive_sales_order_line
SET SOI_RecordID = ?
, SOM_SalesOrderID = ?
, SOM_SalesOrderDate = ?
, SOI_LineQty = ?
, SOD_DiscPercent = ?
, SOD_RequiredQty = ?
, SOD_UnitPrice = ?
, ShipDate = ?
, intuitive_customer_key = ?
, intuitive_item_key = ?
, SOI_PromoCode = ?
, IVM_ReversedFlag = ?
, SOI_MiscLineDescription = ?
, intuitive_ship_to_key = ?
, CSA_RecordID = ?
, SOI_CancelledFlag = ?
, SOM_CustomerPOID = ?
, IVL_COGSAmt = ?
, ShipName = ?
, SOM_LastModifiedDate = ?
, deleted_flag = ?
, SOD_ShipComplete = ?
, SHL_ShipQty = ?
, shipment_lines = ?
, IVM_InvoiceID = ?
WHERE ( ( SOI_RecordID = ? ) ) AND ( ( SOI_LineQty = ? ) ) AND ( ( SOD_RequiredQty = ? ) ) AND ( ( IVM_ReversedFlag = ? ) ) AND ( ( IVM_InvoiceID = ? ) )
2017-05-15T16:10:48.411033Z 24288 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 64 page no 117699 n bits 128 index PRIMARY of table `colorescience2`.`fct_intuitive_sales_order_line` trx id 38383186 lock_mode X locks rec but not gap
Record lock, heap no 25 PHYSICAL RECORD: n_fields 46; compact format; info bits 0
0: len 4; hex 001559e4; asc Y ;;
1: len 6; hex 00000249ae52; asc I R;;
<snip what looks like a bunch of useless rows>
2017-05-15T16:10:48.415147Z 24288 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 64 page no 117561 n bits 120 index PRIMARY of table `colorescience2`.`fct_intuitive_sales_order_line` trx id 38383186 lock_mode X locks rec but not gap waiting
Record lock, heap no 28 PHYSICAL RECORD: n_fields 46; compact format; info bits 0
0: len 4; hex 001557e3; asc W ;;
1: len 6; hex 000002499def; asc I ;;
<snip what looks like a bunch of useless rows>
2017-05-15T16:10:48.419888Z 24288 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)
