4

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')),
    PARTITION p201609 VALUES LESS THAN (TO_DAYS('2016-10-01')),
    PARTITION p201610 VALUES LESS THAN (TO_DAYS('2016-11-01')),
    PARTITION p201611 VALUES LESS THAN (TO_DAYS('2016-12-01')),
    PARTITION future VALUES LESS THAN MAXVALUE
  );

The UPDATE statement is:

update invoice_events  
set invoice_internal_id = 978202
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';

The MySQL's EXPLAIN for this statement is: enter image description here

The SHOW STATUS ENGINE of the issue is:

2016-06-03 11:08:23 0x7f8bcc1aa700
*** (1) TRANSACTION:
TRANSACTION 7031093, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 406 lock struct(s), heap size 41168, 2884 row lock(s), undo log entries 375
MySQL thread id 4726, OS thread handle 140238271616768, query id 10226209 172.30.6.9 BDUSER updating
update invoice_events  set invoice_internal_id = 978173 where fecha_fac between '2016-02-01 00:00:00' and '2016-05-31 23:59:59.999' and source_msisdn in ( '239642983345' )  and invoice_internal_id is null  and country_code = 'ES'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12252 page no 54015 n bits 512 index IDX_MSISDN of table `my_schema`.`invoice_events` /* Partition `p201603` */ trx id 7031093 lock_mode X waiting
Record lock, heap no 101 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 16; hex 33303835303732313033323532303132; asc 3085072103252012;;
 1: len 8; hex 80000000005d2fa3; asc      ]/ ;;
 2: len 5; hex 9998fe0000; asc      ;;

*** (2) TRANSACTION:
TRANSACTION 7031094, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
424 lock struct(s), heap size 41168, 3945 row lock(s), undo log entries 628
MySQL thread id 4731, OS thread handle 140238401480448, query id 10226408 172.30.6.9 BDUSER updating
update invoice_events  set invoice_internal_id = 978202 where fecha_fac between '2016-02-01 00:00:00' and '2016-05-31 23:59:59.999' and source_msisdn in ( '239642983472' )  and invoice_internal_id is null  and country_code = 'ES'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12252 page no 54015 n bits 512 index IDX_MSISDN of table `my_schema`.`invoice_events` /* Partition `p201603` */ trx id 7031094 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

From innodb database variables, I notice innodb_thread_concurrency value is 32.

The selected partitions contains 21853907 rows.

I tried to make a simple UPDATE statement. I filter with fecha_fac only to access the required partitions, and then filter with indexed fields.

So, my first and main question is: How can I fix this transaction lock? Any advice, tip, ...?

Any other important questions are:

  • From the EXPLAIN output: could type (range) be better, even in an single table update? Or is it the best type possible for a single-table update?
  • From the EXPLAIN output: is it right that final key was IDX_INV_INT_ID instead of IDX_MSISDN? IDX_INV_INT_ID indexes a null column.
  • Does it help innodb_thread_concurrency parameter was setted to 0 (infinite concurrency)?
  • Is there any data in the SHOW STATUS output, that could help me and I didn't notice?

In addition to my questions, any help or advice is very appreciated.

Thanks in advance.

2
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' 
  • thank for your tip. I'm doing some test with the new index. So, why does it make the query faster? Why is less likely to deadlock? And why to include "fecha_fac", when this is the partition criteria field, at the end of the index? Excuse me for these questions, but I'm very very interested. Thanks. – yaki_nuka Jun 12 '18 at 13:11
  • If nothing else is running, you can't deadlock. So, speeding up other queries makes it less likely that other transactions are running. – Rick James Jun 12 '18 at 16:29
  • For that date range (exactly months), the partition pruning is just as useful as the extra column in the index. I assume that you might want other ranges (eg one week). In such a case, pruning helps, then the index finishes the task. – Rick James Jun 12 '18 at 16:35
0

I reviewed indexes, and only IDX_MSISDN stays.

Other good practices for DB design and to avoid deadlock I have made are:

  • Reorder columns. Possible NULL and/or not-indexed columns has been moved to the end.
  • Fit column data type and size with PROCEDURE ANALYSE(). For example, SOURCE_MSISDN has been reduced, so then, key_len reduced too.
  • Synchronized transactions. Only one transaction per Spring Batch item is executed. In my design, the throughput has decreased about 5-10%, but it's manageable.

Hope it helps.

  • Ordering the columns does not have any effect in MySQL. Shrinking VARCHAR sizes is beneficial in certain edge cases, but "key_len" is bogus, and does not actually indicate any improvement. The composite index I proposed should still be better (by some amount) than your single-column index. – Rick James Jun 12 '18 at 16:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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