Mysql concurrent inserts and locking

Question

We have 2 tables

 CREATE TABLE `Queue_token` (
  `token_id` int(11) NOT NULL AUTO_INCREMENT,
  `token_queue_id` int(11) NOT NULL,
  `total_process_time` smallint(6) NOT NULL,
  `token_user` int(11) DEFAULT NULL,
  `created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `join_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `join_time` time NOT NULL,
  `app_type` tinyint(1) NOT NULL DEFAULT '1',
  `is_advance` tinyint(1) NOT NULL DEFAULT '0',
  `is_confirmed` tinyint(1) NOT NULL DEFAULT '1',
  `token_user_group` int(11) DEFAULT NULL,
  `uuid` binary(16) DEFAULT NULL,
   PRIMARY KEY (`token_id`),
  KEY `join_date_idx` (`join_date`),
  KEY `queue_join_date` (`token_queue_id`,`join_date`),
  KEY `token_user` (`token_user`),
  KEY `fk_token_user_group` (`token_user_group`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `Live_token_sequence` (
  `token_id` int(11) NOT NULL,
  `queue_id` int(11) NOT NULL,
  `sequence` int(11) NOT NULL,
  `time_slot_id` mediumint(9) NOT NULL,
  `time_slot_sequence` tinyint(4) NOT NULL,
  `created_on` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`token_id`),
  KEY `queue_sequence` (`queue_id`,`sequence`),
  KEY `queue_time_slot` (`time_slot_id`),
  CONSTRAINT `token_id_seq_fk` FOREIGN KEY (`token_id`) REFERENCES     `Queue_token` (`token_id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Based on the number of tokens generated in the Queue_token table for a date we generate a unique sequence for each token in Live_token_sequence table.

To generate the sequence we first fetch the count of tokens from the Queue_token table, and the next token generated gets the count + 1 sequence.

We facing an issue where on concurrent inserts, tokens are getting the same, sequence. If we try to use SELECT FOR UPDATE we face deadlocks as the count query above does a join with other tables too.

How do we go about this?

Update------ The count query

```

select count(`sminq`.`Queue_token`.`token_id`) 
from   `sminq`.`Queue_token` 
join `sminq`.`Live_token_sequence` 
on `sminq`.`Queue_token`.`token_id` = `sminq`.`Live_token_sequence`.`token_id` 
join `sminq`.`Calendar_time_slot` 
on `sminq`.`Live_token_sequence`.`time_slot_id` = `sminq`.`Calendar_time_slot`.`slot_id` 
join `sminq`.`Live_token_status` on `sminq`.`Queue_token`.`token_id` = `sminq`.`Live_token_status`.`token_id` 

left outer join `sminq`.`Status_code` 
on (`sminq`.`Live_token_status`.`token_status_id` = `sminq`.`Status_code`.`status_id` 
and `sminq`.`Status_code`.`status_type` not in (?)) 

where (`sminq`.`Queue_token`.`join_date` >= ? and `sminq`.`Queue_token`.`join_date` < ? 
and `sminq`.`Live_token_sequence`.`queue_id` = ? and `sminq`.`Calendar_time_slot`.`group_id` = ?) for update

After including the new indexes, explin output

+------+-------------+---------------------+--------+----------------   ----------------------------------------------+------------+---------+--- -------------------------------------+------+-------------+
| id   | select_type | table               | type   | possible_keys                                                   | key        | key_len | ref                                    | rows |   Extra       |
+------+-------------+---------------------+--------+----------------  ----------------------------------------------+------------+---------+---  -------------------------------------+------+-------------+
|    1 | SIMPLE      | Calendar_time_slot  | ref    |   slot_group,group_slot                                        | group_slot   | 4       | const                                  |    6 | Using index |
|    1 | SIMPLE      | Live_token_sequence | ref    |   PRIMARY,queue_sequence,queue_time_slot,queue_slot,slot_queue | queue_slot   | 7       | const,sminq.Calendar_time_slot.slot_id |    1 | Using index |
|    1 | SIMPLE      | Queue_token         | eq_ref |   PRIMARY,join_date_idx                                        | PRIMARY      | 4       | sminq.Live_token_sequence.token_id     |    1 | Using where |
+------+-------------+---------------------+--------+----------------   ----------------------------------------------+------------+---------+---   -------------------------------------+------+-------------+

Answer 1

A little easier to read:

select  count(qt.`token_id`)
    from  `Queue_token` AS qt
    join  `Live_token_sequence` AS seq  ON qt.`token_id` = seq.`token_id`
    join  `Calendar_time_slot`  AS cts  ON seq.`time_slot_id` = cts.`slot_id`
    join  `Live_token_status`  AS stat  ON qt.`token_id` = stat.`token_id`
    left outer join  `Status_code` AS code
               ON ( stat.`token_status_id` = code.`status_id`
               and  code.`status_type` not in (?) )
    where  (qt.`join_date` >= ?
       and  qt.`join_date` < ?
       and  seq.`queue_id` = ?
       and  cts.`group_id` = ?
           )
    for update

Beware of not in (?); if the binding is given '1,2,3', it might lead to not in ('1,2,3'), which is not the same as not in (1,2,3), nor not in ('1','2','3').

Instead of COUNT(token_id), you probably want COUNT(DISTINCT token_id)

Without the rest of the transaction, it is hard to discuss the locking.

Meanwhile, let's look at possible speedups.

Two or three tables seem to be in a 1:1 relationship on token_id. Is the some reason for not merging the tables?

Suggested additional indexes:

seq:  INDEX(queue_id, time_slot_id), INDEX(time_slot_id, queue_id)
(In doing so, KEY `queue_time_slot` (`time_slot_id`) can be removed.)
cts:  INDEX(group_id, slot_id), INDEX(slot_id, group_id)

Remove the LEFT JOIN Status_code ... -- it does nothing but inflate the COUNT.

Please provide EXPLAIN SELECT ...;

Answer 2

"To generate the sequence we first fetch the count of tokens from the Queue_token table, and the next token generated gets the count + 1 sequence. We facing an issue where on concurrent inserts, tokens are getting the same, sequence."

This is a terribly bad design.

If a row is deleted, you are guaranteed to generate a duplicate.

If you have two threads running 'simultaneously' and doing the 'same' thing, they will almost certainly generate duplicate values. This is because of transaction_isolation_mode, and probably cannot be fixed by FOR UPDATE.

Replace it with the use of an AUTO_INCREMENT column. token_id is such, I don't know why it does not suffice. AUTO_INCREMENT is guaranteed to generate distinct values, regardless of concurrency and deletes. (It does not say which of two 'concurrent inserts' will have the smaller id.)