0

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: 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)
Improve this question

1 Answer 1

Reset to default
1

One way to try to avoid deadlocks is to speed up queries.

  • If you don't need LEFT, remove it. This might provide a more efficient way to handle JOIN ( SELECT ... ).
  • If the flags are 0/1, then do flag = 0 instead of flag != 1; this may be easier to optimize.
  • What indexes do you have on each table? (Please provide SHOW CREATE TABLE.) The most critical is fsol.
  • Focus on optimizing that subquery on fsol -- start by seeing if it takes a lot of time.
  • Would it work to collect data from each day into a Summary Table, then compute the monthly stuff from that?
Improve this answer
1
  • Thanks. We solved the problem by going with Bullet #5, except running the update query sequentially after the load job so that there wouldn't be any conflicts.
    – jwoww
    Commented Jul 3, 2017 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.