1、表資訊：
CREATE TABLE `device_onoffline` (
`id` char(32) NOT NULL DEFAULT '' COMMENT '主鍵',
`device_id` char(32) NOT NULL DEFAULT '' COMMENT '設備id',
`device_serial` char(9) NOT NULL DEFAULT '' COMMENT '設備序列號',
`msg_time` bigint(15) NOT NULL DEFAULT '0' COMMENT '訊息觸發時間',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
PRIMARY KEY (`id`),
KEY `idx_device_time` (`device_serial`,`msg_time`)
) ENGINE=InnoDB
2、現有定時任務從表中洗掉資料，根據條件，WHERE device_serial = 'XXXXXX' AND msg_time <= 某個值。
3、今天監控報產生死鎖，死鎖日志如下，
2020-04-27 05:48:00 0x7fc35fbe5700
*** (1) TRANSACTION:
TRANSACTION 1010208554, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 201626, OS thread handle 140477100840704, query id 1163073704 10.97.164.210 chain updating
DELETE FROM device_onoffline
WHERE device_serial = 'C05910436'
AND msg_time <= 1587937624191
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1153 page no 5 n bits 104 index PRIMARY of table `chain`.`device_onoffline` trx id 1010208554 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1010208549, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4962
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 70 row lock(s), undo log entries 19
MySQL thread id 202067, OS thread handle 140477101659904, query id 1163073695 10.97.164.210 chain updating
DELETE FROM device_onoffline
WHERE device_serial = '715229740'
AND msg_time <= 1587937612772
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1153 page no 5 n bits 104 index PRIMARY of table `chain`.`device_onoffline` trx id 1010208549 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1153 page no 6 n bits 104 index PRIMARY of table `chain`.`device_onoffline` trx id 1010208549 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (1)
4、求教各位大神，這種情況下是怎么產生死鎖的。where條件都已加索引。
uj5u.com熱心網友回復：
從 資訊上看鎖申請的是同一個空間的同一個頁上的資料。應該會出現鎖等待。死鎖出現可能代碼中在洗掉資料的事務中，還對其他的資源進行了鎖操作，后進行的事務所鎖住了先進行的事務需要的資源，以致于相互等待對方解鎖。排查下代碼吧。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/19830.html
標籤：MySQL
上一篇：SQL陳述句