Concurrency when simulating autoincrement with triggers on MySQL
You can avoid the whole problem by using this pattern.  Old tables had

id MEDIUMINT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(id)


New table has

tenant_id SMALINT UNSIGNED,
id INT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY(tenant_id, id),
INDEX(id)


Notes:


INDEX(id) is sufficient to handle AUTO_INCREMENT.
The ids for a given tenant will have a lot of gaps and will grow faster than before.  So you may need a larger datatype.
Nothing prevents you from inserting a duplicate id, but that would not happen in ordinary programming.