The CREATE TABLE ` device_onoffline ` (
` id ` char (32) NOT NULL DEFAULT 'COMMENT' primary key ',
` device_id ` char (32) NOT NULL DEFAULT 'COMMENT' device id,
` device_serial ` char (9), NOT NULL DEFAULT 'COMMENT' equipment serial number,
` msg_time ` bigint (15) NOT NULL DEFAULT '0' COMMENT 'message triggering time,
` create_time ` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time,
PRIMARY KEY (` id `),
The KEY ` idx_device_time ` (` device_serial `, ` msg_time `)
) ENGINE=InnoDB
2, the existing timing task to delete data from the table, according to the condition, the WHERE device_serial='XXXXXX' AND msg_time & lt;=a value,
3, today to monitor deadlocks, deadlock log as follows,
The 2020-04-27 05:48:00 0 x7fc35fbe5700
* * * (1) the TRANSACTION:
TRANSACTION 1010208554, ACTIVE 0 SEC fetching rows
Mysql tables in use 1, locked 1
The LOCK WAIT 4 LOCK struct (s), the heap size of 1136, four row LOCK (s), the undo log entries 1
MySQL thread id 201626, OS thread handle 140477100840704, query id 1163073704 10.97.164.210 chain updating
The DELETE FROM device_onoffline
WHERE device_serial='C05910436'
AND msg_time & lt;=1587937624191
* * * (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD the 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) the TRANSACTION:
The TRANSACTION 1010208549, ACTIVE 0 SEC fetching rows, thread declared inside the InnoDB 4962
Mysql tables in use 1, locked 1
4 the lock struct (s), the heap size of 1136, 70 row lock (s), the undo log entries 19
MySQL thread id 202067, OS thread handle 140477101659904, query id 1163073695 10.97.164.210 chain updating
The DELETE FROM device_onoffline
WHERE device_serial='715229740'
AND msg_time & lt;=1587937612772
Doesn * * * (2) THE LOCK (S) :
RECORD the 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 the 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 the TRANSACTION (1)
4, for a great god, and this kind of circumstance how deadlocks, where conditions are indexed,
CodePudding user response:
From information on the lock for the same space data, on the same page should appear lock waits, deadlock appearance may code in delete data in a transaction, also has carried on the lock operations to other resources, the firm after lock advanced lines of transaction needs resources, to wait for each other unlock, screening the code,