I try to run multiple INSERT SELECT statements at the same time and run into deadlock issues. I want to insert the calculated median min, max, avg of multiple sensors and units concurrently.
The idea is to insert the median of every device/unit combination every minute(to eliminate false readings). The min, max, avg of each value should be calculated and inserted every hour.
10.3.39-MariaDB - Ubuntu 20.04
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-05-06 20:08:00 0x7fa7a95c3700
*** (1) TRANSACTION:
TRANSACTION 1077705, ACTIVE 1 sec inserting
mysql tables in use 48, locked 48
LOCK WAIT 546 lock struct(s), heap size 90232, 104961 row lock(s), undo log entries 1
MySQL thread id 5195, OS thread handle 140358081038080, query id 452803 localhost 127.0.0.1 USERNAME Creating sort index
//INSERT STATEMENT
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 566 page no 849 n bits 1160 index fk_device of table `home_prod`.`tbl_value` trx id 1077705 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000004; asc ;;
*** (2) TRANSACTION:
TRANSACTION 1077706, ACTIVE 0 sec setting auto-inc lock
mysql tables in use 48, locked 48
543 lock struct(s), heap size 90232, 104959 row lock(s)
MySQL thread id 5197, OS thread handle 140358077658880, query id 452804 localhost 127.0.0.1 USERNAME Creating sort index
//INSERT STATEMENT
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 566 page no 849 n bits 1160 index fk_device of table `home_prod`.`tbl_value` trx id 1077706 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 4; hex 80000004; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `home_prod`.`tbl_value` trx id 1077706 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
I can see the exclusive lock in trx1 and the shared lock in trx2. I reseached read commited snapshop isolation, but is this the optimal solution / possible solution for my issue or do I need some code optimisation ?
Could it be becouse of ORDER BY or aggregate Funktions like MAX() ? Advice on how to solve the issue would be greatly appriciated:)
The TRANSACTION 2 is holding the S gap lock on tbl_value because of the subquery:
SELECT
datavalue
FROM
tbl_value
Transaction 1 acquired the auto_increment lock and got to the point it needed to insert into this range.
Transaction 2 got to the point of inserting, needing the Auto Increment lock that Transaction 1 had, and then hence deadlock.
To avoid the transactions acquiring gap locks, a READ COMMITTED transaction isolation mode can be selected instead of the default (higher) REPEATABLE READ.
This can be applied on the next transaction (and not set for the session) with:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ref: SET TRANSACTION