deadlock when selecting max() for update on auto increment primary key

Question

I have a table defined in this way:

CREATE TABLE `measure` (
  `measureId` bigint NOT NULL,
  `sensorId` int NOT NULL,
  `timestamp` bigint NOT NULL,
  `data` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ALTER TABLE `measure`
  ADD PRIMARY KEY (`measureId`),
  ADD KEY `measure_index` (`sensorId`,`timestamp`);

ALTER TABLE `measure`
  MODIFY `measureId` bigint NOT NULL AUTO_INCREMENT;

measureId is mostly used as auto increment, but sometimes I need to specify measureId during insert. I use the following transaction for my application:

begin;
select max(measureId) from measure for update;
-- use the max id retrieved to create measurements
insert into measure values (max_id + 1, ...), (max_id + 2, ...), ...;
-- do other stuff
commit;

I use select ... for update to avoid insertions between selecting max(measureId) and and adding the new rows. Without using it, the transaction would fail since the id would be already taken via autoincrement (I use the default isolation REPEATABLE READS).

In a non concurrent environment the transaction succeeds, but I get a deadlock when this happens between two transactions:

T1: select max(measureId) ...;
T2: select max(measureId) ...; -- starts waiting
T1: into measure values (max_id + 1, ...), ...;
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Why should this be a dealock? T1 should already have the lock on the primary key index (link).

How can i fix this?

Edit: Added output of the innodb status monitor, not sure what is happening. I seems to me that transaction 2 (below) is waiting for a lock it already has.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-01-23 13:58:10 139850373236480
*** (1) TRANSACTION:
TRANSACTION 41530, ACTIVE 62 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 1251, OS thread handle 139850310264576, query id 5030779 172.19.0.1 root optimizing
select max(measureId) from measure for update

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 22722 n bits 360 index PRIMARY of table `WEATHER_STATION`.`measure` trx id 41530 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 22722 n bits 360 index PRIMARY of table `WEATHER_STATION`.`measure` trx id 41530 lock_mode X waiting
Record lock, heap no 290 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 80000000004aa795; asc      J  ;;
 1: len 6; hex 00000000a1f8; asc       ;;
 2: len 7; hex 82000000a913e6; asc        ;;
 3: len 4; hex 80000192; asc     ;;
 4: len 8; hex 8000000063cac0ed; asc     c   ;;
 5: len 4; hex 6666ea41; asc ff A;;


*** (2) TRANSACTION:
TRANSACTION 41529, ACTIVE 163 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 1250, OS thread handle 139850312378112, query id 5030780 172.19.0.1 root update
insert into measure values (4892566, 1, 2, 3)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 22722 n bits 360 index PRIMARY of table `WEATHER_STATION`.`measure` trx id 41529 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 290 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 80000000004aa795; asc      J  ;;
 1: len 6; hex 00000000a1f8; asc       ;;
 2: len 7; hex 82000000a913e6; asc        ;;
 3: len 4; hex 80000192; asc     ;;
 4: len 8; hex 8000000063cac0ed; asc     c   ;;
 5: len 4; hex 6666ea41; asc ff A;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 22722 n bits 360 index PRIMARY of table `WEATHER_STATION`.`measure` trx id 41529 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

Answer 1

Instead, get rid of measureid:

CREATE TABLE `measure` (
  `sensorId` int NOT NULL,
  `timestamp` bigint NOT NULL,
  `data` float NOT NULL,
  PRIMARY KEY(sensorId, timestamp)
) ENGINE=InnoDB

Consider using the TIMESTAMP(n) datatype, where n is the number of decimal places (fraction of a second). The max is 6 (microseconds). This will give you a variety of date/time functions that will probably be clearer than futzing with a BIGINT.

Suggest shrinking sensorId to a smaller datatype. For example, SMALLINT UNSIGNED would allow 65K sensors in a 2-byte column.

With those changes, the table (data+indexes) will take about half the disk space. This will have a favorable impact on speed.

With that, you won't need the SELECT (another speedup). And you can possibly do nothing more than the multi-row INSERT.