-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
The 2017-11-27 11:26:22 2 b80ab881700
* * * (1) the TRANSACTION:
TRANSACTION 84983140, ACTIVE 3.138 SEC inserting
Mysql tables in use 1, locked 1
The LOCK WAIT 13 LOCK struct (s), heap size 2936, 9 row LOCK (s), the undo log entries 102
LOCK BLOCKING MySQL thread id: 4188408 block 4188409
MySQL thread id 4188409, OS thread handle 0 x2b80c834c700, query id 1119236930 119.23.142.11
All5 update
INSERT INTO t_flow_real_time (` spot `, ` group_quantity `, ` booking `, ` statistics_time `)
Values (1, 1, '2017-11-24', '2017-11-27 11:26:21. 007')
ON the DUPLICATE KEY UPDATE
` group_quantity `=group_quantity + 1
* * * (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD the LOCKS space id 1258 page no 4 n bits 104 index ` idx_unique ` of table ` a_analysis `. ` t_flow_real_time ` TRX id 84983140 lock_mode X waiting
Record the lock, heap no 33 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 80000001; Asc;;
1: len 3; Hex fc378; Asc x;;
2: len 4; Hex 8000984 d; Asc M;;
* * * (2) the TRANSACTION:
TRANSACTION 84983143, ACTIVE 1.554 SEC inserting
Mysql tables in use 1, locked 1
13 the lock struct (s), heap size 2936, 9 row lock (s), the undo log entries 46
MySQL thread id 4188408, OS thread handle 0 x2b80ab881700, query id 1119237503 119.23.142.11
All580 update
INSERT INTO t_flow_real_time (` spot `, ` group_quantity `, ` booking `, ` statistics_time `)
Values (1, 1, '2017-11-26', '2017-11-27 11:26:22. 407')
ON the DUPLICATE KEY UPDATE
` group_quantity `=group_quantity + 1
Doesn * * * (2) THE LOCK (S) :
RECORD the LOCKS space id 1258 page no 4 n bits 104 index ` idx_unique ` of table ` a_analysis `. ` t_flow_real_time ` TRX id 84983143 lock_mode X
Record the lock, heap no 33 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 80000001; Asc;;
1: len 3; Hex fc378; Asc x;;
2: len 4; Hex 8000984 d; Asc M;;
* * * (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD the LOCKS space id 1258 page no 4 n bits 104 index ` idx_unique ` of table ` a_analysis `. ` t_flow_real_time ` TRX id 84983143 lock_mode X waiting
Record the lock, heap no 34 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 80000001; Asc;;
1: len 3; Hex fc37a; Asc z;;
2: len 4; Hex 8000987 b; Asc {;
* * * WE ROLL BACK the TRANSACTION (2)
CodePudding user response:
You should put table structure starts,CodePudding user response:
Supplementary table structure:The Create Table
The CREATE TABLE ` t_flow_real_time ` (
` id ` int (11), NOT NULL AUTO_INCREMENT COMMENT 'primary key',
` spot ` int (11) the DEFAULT NULL COMMENT 'area',
` group_quantity ` int (11) the DEFAULT '0' COMMENT 'team number today,
` quantity ` int (11) the DEFAULT '0' COMMENT 'today's vote,
` consume_quantity ` int (11) the DEFAULT '0' COMMENT 'cancel after verification number,
` consume_group_quantity ` int (11) the DEFAULT '0' COMMENT 'number verification team,
` refund_quantity ` int (11) the DEFAULT '0' COMMENT 'ticket number,
` refund_group_quantity ` int (11) the DEFAULT '0' COMMENT 'refund team number,
` booking ` date DEFAULT NULL COMMENT 'visit date,
` statistics_time ` datetime DEFAULT NULL COMMENT 'statistical time,
PRIMARY KEY (` id `),
The UNIQUE KEY ` idx_unique ` (` spot `, ` booking `) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=46451 DEFAULT CHARSET=utf8 COMMENT='real-time traffic data'
CodePudding user response:
Show the processlist.Check the deadlock when sending SQL
CodePudding user response:
The log is there missing? By providing the log analysis as follows:
Transaction 2 (84983143) tx_id: there should be two SQL, use sql1 and sql2 said,
Sql1 corresponding idx_unique database structure is:
Hex 80000001
Hex 8 fc378
Hex 8000984 d
Sql2 corresponding idx_unique database structure is:
Hex 80000001
Hex 8 fc37a
Hex 8000987 b
Transaction 1 (tx_id: 84983140) from the log see only a single SQL, expressed with sql1,
Sql1 corresponding idx_unique database structure is:
Hex 80000001
Hex 8 fc378
Hex 8000984 d
Results:
Can see from the log analysis:
1, transaction 1 sql1 are waiting for the transaction 2 sql1 corresponding secondary indexes (idx_unique (hex 80000001, hex 8000984 d)) X lock on record,
2, transaction 2 sql2 is waiting for the SQL=sql2 corresponding secondary indexes (idx_unique (8000987 b) hex 80000001, hex) X lock on record,
Hence transaction 1 waiting for transaction 2, and transaction 2 waiting for another transaction, if here's another transaction is a transaction 1, that is completely a deadlock; If another transaction is a transaction 3, that is, such as transaction released transaction 2 wait 3 X lock, transaction 2 and then released the transaction execution wait 1 X lock, then transaction 1 can normal execution,
Attach the analysis process, by attachment for reference and correct,