How to avoid deadlocks when generating consecutive ticket numbers?

I have implemented a ticketing system where when you join an event and placed an order depending on the quantity, a *n* of entries will be generated. Example: you place an order with 10 quantity, a 10 ticket number would be generated. The rule is, ticket number starts at 1 and would increment by 1 every time it is generated. The ticket number always starts with 1 on each event and each orders can have multiple entries. |event_id |ticket_no |order_id | |-----------|--------------|--------------| |1|1|1| |1|2|1| |1|3|2| |1|4|3| |1|5|6| |2|1|4| |2|2|5| So currently, in order to generate a ticket no. for each event. I used a MySQL trigger to increment its value: CREATE TRIGGER number_generator BEFORE INSERT ON entries FOR EACH ROW BEGIN SET NEW.ticket_no = (SELECT COALESCE(MAX(ticket_no), 0) + 1 FROM entries WHERE event_id = NEW.event_id); END; Here is a sample raw query of how I trigger ticket no. generation insert into entries (order_id, event_id, ticket_no) values (123, 1, 1), (123, 1, 1), (123, 1, 1), ... The problem: When multiple users place an order at the same time, the system would randomly throw a deadlock exception: > Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction In my old implementation, which uses MyISAM tables with auto increment on second column, I wouldn't encounter this issue, and the ticket number generation was faster. How can I resolve the deadlock and achieve MyIsam speed in inserting, but with InnoDB? ------------------------------------------------------------------------------------------- Orders Table Definition CREATE TABLE orders ( id bigint unsigned NOT NULL AUTO_INCREMENT, event_id bigint unsigned NOT NULL, first_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, last_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, email varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL PRIMARY KEY (id), KEY owning_event (event_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci Entries Table Definition CREATE TABLE entries ( event_id bigint unsigned NOT NULL, ticket_no bigint unsigned NOT NULL, order_id bigint unsigned NOT NULL, PRIMARY KEY (event_id,ticket_no), KEY entries_event_id_index (event_id), KEY entries_order_id_index (order_id), CONSTRAINT entries_order_id_foreign FOREIGN KEY (order_id) REFERENCES orders (id), CONSTRAINT entries_event_id_foreign FOREIGN KEY (event_id) REFERENCES events (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci Old entries Table Definition CREATE TABLE entries_old ( event_id BIGINT UNSIGNED NOT NULL, ticket_no MEDIUMINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, PRIMARY KEY (event_id, ticket_no) ) ENGINE=MyISAM;