1

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;

3 Answers 3

0

Now you know why SELECT COALESCE(MAX(ticket_no), 0) + 1 FROM entries is not the right way to emulate a sequence; it's slow and prone to lock contention and race conditions. You'll have to rethink your approach.

Firstly, the rule that says "ticket number starts at 1 and would increment by 1 every time it is generated" seems arbitrary. You can just as well uniquely identify tickets by a regular auto_increment column.

If for some reason you insist on implementing this rule, you might choose to maintain the "next ticket number" pseudo-sequence as a column in the events table, but that won't guarantee a gapless sequence either.

3
  • I can't use the auto_increment since the ticket no should restart a 1 on new events. Jul 7, 2021 at 15:38
  • I don't see why this is the case, as I mentioned in my post.
    – mustaccio
    Jul 7, 2021 at 15:38
  • And you must use InnoDB.
    – Rick James
    Jul 14, 2021 at 14:30
0

Use InnoDB; Use transactions; use FOR UPDATE.

Give this a try... Have another table. Based on your example, it would have

event_id  order_id  tkt_start  tkt_ct
1         1         1          2
1         2         3          1
1         3         4          1
1         6         5          1
2         4         1          1
2         5         2          1

PRIMARY KEY(event_id, tkt_start) 

Use one transaction to insert one row into this table. It will involve a MAX and other messy stuff. But, by inserting only one row, it will be less likely to be deadlocked. Possibly it can be a single statement.

You must write code to check for errors and restart the transaction if it was a victim of a deadlock.

Then, in a separate transaction build the multiple rows in your table. This is unlikely to block because these insert(s) are unlikely to conflict with other actions.

You must not have a transaction that spans the time where a user is checking with their spouse to see if the tickets will be OK. If you need to "reserve" tickets that might later get canceled, a different locking mechanism is needed than just a single InnoDB transaction.

If you need a "reserve / chat / confirm" sequence, then you must be willing to lose the consecutive nature of ticket numbers. (This is required when selling seats; I don't understand your need for no-gaps in ticket numbers.)

-1

Serialize your requests to avoid deadlocks

SELECT events.id FROM event WHERE events.id = :eventId FOR UPDATE

The above will cause every other person attempting to place an order for a given event to wait while this first thread finishes. That will prevent deadlocks, but doesn't help on speed, and will potentially slow it, as only one thread can operate at a time.

Calculate ticket numbers in your app

So, to make up for potential speed loss, stop making the MySQL do all the work for computing the ticket numbers. You have a good index, but it still takes more time than whatever is the equivalent of your application language's $ticketNumber++. Since requests are serialized you don't have to worry about having duplicate values generated.

SELECT MAX(entries.ticket_no) FROM entries WHERE entries.event_id = :eventId

Take the result from this query, and then compile the SQL statement in a loop in your application until you build a string that looks like

insert into
  `entries` (`order_id`, `event_id`, `ticket_no`)
values
  (123, 1, 5),
  (123, 1, 6),
  (123, 1, 7),
  ...

(In this example, the highest ticket_no was 4, from the MAX query above.)

Then you just have one insert, it should execute very quickly because there is no longer a trigger running for each row, and when you commit the transaction then the lock from the FOR UPDATE query is released and the next thread (if any) can execute.

This should be quite fast, and satisfy your business rule of each event having ticket numbers ranging from 1 to [max number of tickets].

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.