Description:
When a deadlock occurs, the rollback transaction is selected incorrectly, under MySQL 8.0.25.
How to repeat:
test table:
```
CREATE TABLE `t1` (
`i` int NOT NULL,
PRIMARY KEY (`i`)
) ENGINE=InnoDB;
select * from t1;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
```
Session 1,2,3 executes the following SQL in chronological order.
session1:
```
begin; delete from t1 where i=1; select sleep(5); commit;
```
session2:
```
begin; insert into t1 select 1;
```
session3:
```
begin; insert into t1 select 1;
```
When session 1 executes commit, sometimes session 2 sends a deadlock error and the transaction is rolled back.
The error logs
```
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-05-25 22:56:28 140275664414464
*** (1) TRANSACTION:
TRANSACTION 280649741, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 140284642764544, query id 394 localhost root executing
insert into t1 select 1
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 71 page no 4 n bits 72 index PRIMARY of table `mymgr`.`t1` trx id 280649741 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000010ba6007; asc ` ;;
2: len 7; hex 01000000d02c5c; asc ,\;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 71 page no 4 n bits 72 index PRIMARY of table `mymgr`.`t1` trx id 280649741 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000010ba6007; asc ` ;;
2: len 7; hex 01000000d02c5c; asc ,\;;
*** (2) TRANSACTION:
TRANSACTION 280649740, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 331, OS thread handle 140273738405632, query id 392 localhost root executing
insert into t1 select 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 71 page no 4 n bits 72 index PRIMARY of table `mymgr`.`t1` trx id 280649740 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000010ba6007; asc ` ;;
2: len 7; hex 01000000d02c5c; asc ,\;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 71 page no 4 n bits 72 index PRIMARY of table `mymgr`.`t1` trx id 280649740 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000010ba6007; asc ` ;;
2: len 7; hex 01000000d02c5c; asc ,\;;
*** WE ROLL BACK TRANSACTION (2)
```
The transaction running time in session 2(TRANSACTION (2), running time is 3 seconds) is longer than that in session 3(TRANSACTION (1), running time is 2 seconds), but it is rolled back.
In this case, I think it's better to roll back TRANSACTION(1) which with less running time.
Suggested fix:
When a deadlock occurs, the transaction with less running time should be rolled back at the same cost.
