MySQL：單個表中的死鎖更新…在哪裡

此配置引發了下一個問題：

• MySQL 5.7.10
• 春天 4.0.5
• 春季批次 3.0.1
• 10到20個執行緒之間的Spring ThreadPoolTask​​Executor

當某些執行緒嘗試在單個表中執行 UPDATE…WHERE 時，問題是死鎖。

表格是：

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
 );

更新語句是：

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';

MySQL 對此語句的解釋是：

該問題的顯示狀態引擎是：

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;;

從 innodb 數據庫變數中，我注意到innodb_thread_concurrency值為 32。

選定的分區包含 21853907 行。

我試圖做一個簡單的 UPDATE 語句。我只使用 fecha_fac 過濾以訪問所需的分區，然後使用索引欄位進行過濾。

所以，我的第一個也是主要的問題是：我怎樣才能修復這個事務鎖？任何建議，提示，…？

任何其他重要的問題是：

• 從 EXPLAIN 輸出：即使在單個表更新中，類型（範圍）是否會更好？還是單表更新的最佳類型？
• 從 EXPLAIN 輸出：最終密鑰是 IDX_INV_INT_ID 而不是 IDX_MSISDN 是否正確？IDX_INV_INT_ID 索引一個空列。
• 它是否有助於將 innodb_thread_concurrency 參數設置為 0（無限並發）？
• SHOW STATUS 輸出中是否有任何數據可以幫助我而我沒有註意到？

除了我的問題，非常感謝任何幫助或建議。

提前致謝。

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';

為了使查詢執行得更快，從而減少死鎖的可能性，添加這個複合索引：

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

另外，我更喜歡以這種方式完成時間範圍：

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

我查看了索引，只有 IDX_MSISDN 保留。

數據庫設計和避免死鎖的其他良好做法是：

• 重新排序列。可能的 NULL 和/或未索引的列已移至末尾。
• 使用 PROCEDURE ANALYSE() 擬合列數據類型和大小。例如，SOURCE_MSISDN 已減少，那麼 key_len 也已減少。
• 同步交易。每個 Spring Batch 項目僅執行一個事務。在我的設計中，吞吐量下降了大約 5-10%，但它是可控的。

希望能幫助到你。

引用自：https://dba.stackexchange.com/questions/154455