MySQL: Deadlock in a single table UPDATE…WHERE

The next issue raises with this configuration:

• MySQL 5.7.10

• Spring 4.0.5

• Spring Batch 3.0.1

• Spring ThreadPoolTaskExecutor between 10 and 20 threads

The issue is a deadlock when some threads try to do an UPDATE...WHERE in a single table.

The table is:

``
CREATE TABLE IF NOT EXISTS invoice_events (
INTERNAL_ID bigint(20) NOT NULL,
FECHA_FAC datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PERIOD_TYPE varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
PRODUCT_ID varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
RATE_ID varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
INVOICE_INTERNAL_ID bigint(20) unsigned DEFAULT NULL,
COUNTRY_CODE varchar(4) COLLATE utf8_spanish_ci DEFAULT NULL,
SOURCE_MSISDN varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
TARGET_MSISDN varchar(100) CHARACTER SET utf8 DEFAULT NULL,
CATEGORY varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
SERVICE varchar(50) COLLATE utf8_spanish_ci DEFAULT NULL,
USAGE_TYPE varchar(50) CHARACTER SET utf8 DEFAULT NULL,
BT_COST double(22,6) DEFAULT NULL,
PRIMARY KEY (INTERNAL_ID,FECHA_FAC),
KEY IDX_INV_INT_ID (INVOICE_INTERNAL_ID),
KEY IDX_MSISDN (SOURCE_MSISDN),
KEY IDX_FECHA_FAC (FECHA_FAC`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci
PARTITION BY RANGE( TO_DAYS(FECHA_FAC) ) (
PARTITION p201511 VALUES LESS THAN (TO_DAYS('2015-12-01')),
PARTITION p201512 VALUES LESS THAN (TO_DAYS('2016-01-01')),
PARTITION p201601 VALUES LESS THAN (TO_DAYS('2016-02-01')),
PARTITION p201602 VALUES LESS THAN (TO_DAYS('2016-03-01')),
PARTITION p201603 VALUES LESS THAN (TO_DAYS('2016-04-01')),
PARTITION p201604 VALUES LESS THAN (TO_DAYS('2016-05-01')),
PARTITION p201605 VALUES LESS THAN (TO_DAYS('2016-06-01')),
PARTITION p201606 VALUES LESS THAN (TO_DAYS('2016-07-01')),
PARTITION p201607 VALUES LESS THAN (TO_DAYS('2016-08-01')),
PARTITION p201608 VALUES LESS THAN (TO_DAYS('2016-09-01')),
PA

where fecha_fac between '2016-02-01 00:00:00'
                    and '2016-05-31 23:59:59.999' 
  and source_msisdn = '239642983472' 
  and invoice_internal_id is null  
  and country_code = 'ES';

To make the query run faster, hence be less likely to Deadlock, add this composite index:

INDEX(source_msisdn, country_code, invoice_internal_id, -- in any order
      fecha_fac)  -- last

Also, I prefer time ranges to be done this way:

where fecha_fac >= '2016-02-01'
  and fecha_fac  < '2016-02-01' + INTERVAL 4 MONTH'

StackExchange Database Administrators Q#154455, answer score: 2