title: MySQL deadlocks with composite primary key and trigger autoincrement
body: <p>I have standalone server and 2000 users online (not so many).
  MySQL DB 5.6 with table request_action (composite PK without autoincrement BUT increment is in trigger, you can see it below):</p>
  
  <pre><code>  CREATE TABLE `request_action` (
    `ra_id` bigint(20) NOT NULL,
    `cl_id` int(11) NOT NULL DEFAULT '0',
    `ra_r_id` bigint(20) NOT NULL,
    `ra_tr_id` bigint(20) DEFAULT '0',
    `ra_ss_id` bigint(20) NOT NULL DEFAULT '0',
    `ra_h_id` int(11) NOT NULL DEFAULT '0',
    `ra_uch_id` bigint(20) DEFAULT '0',
    `ra_u_id` int(11) DEFAULT '0',
    `ra_datetime` datetime NOT NULL,
    `ra_uct_id` int(11) NOT NULL DEFAULT '0',
    `ra_text` longtext NOT NULL,
    `ra_datetime_reply` datetime NOT NULL,
    `ra_reply` longtext NOT NULL,
    `ra_line_breaks` tinyint(4) NOT NULL DEFAULT '0',
    `ra_plan` tinyint(4) NOT NULL DEFAULT '0',
    `ra_shw` tinyint(4) NOT NULL DEFAULT '1',
    `ra_to_u_id` int(11) DEFAULT '0',
    `ra_created_at` datetime DEFAULT NULL,
    `ra_seen` tinyint(4) NOT NULL DEFAULT '0',
    `ra_seen_u_id` bigint(20) NOT NULL DEFAULT '0',
    PRIMARY KEY (`cl_id`,`ra_id`),
    KEY `rm_r_id` (`ra_r_id`),
    KEY `ra_u_id` (`ra_u_id`),
    KEY `ra_plan` (`ra_plan`),
    KEY `ra_rat_id` (`ra_ss_id`),
    KEY `ra_h_id` (`ra_h_id`),
    KEY `ra_tr_id` (`ra_tr_id`),
    KEY `ra_id` (`ra_id`),
    KEY `ra_datetime` (`ra_datetime`,`ra_seen`),
    KEY `ra_shw` (`ra_shw`,`ra_seen`,`ra_to_u_id`),
    KEY `ra_r_id` (`ra_r_id`,`ra_tr_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>Trigger on this table (BEFORE INSERT):</p>
  
  <pre><code>if (cast(NEW.ra_id as UNSIGNED) = 0) then
  SET NEW.ra_id = (SELECT COALESCE(MAX(ra_id)+1, 1) FROM request_action WHERE cl_id = NEW.cl_id);
  end if
  </code></pre>
  
  <p>AND I have deadlocks many times in a day (( For example, 100 in a day.</p>
  
  <pre><code>LATEST DETECTED DEADLOCK
  ------------------------
  2019-02-21 21:09:34 7f5e11f3b700
  *** (1) TRANSACTION:
  TRANSACTION 2947112777, ACTIVE 0 sec inserting
  mysql tables in use 11, locked 11
  LOCK WAIT 5 lock struct(s), heap size 1184, 3 row lock(s)
  MySQL thread id 19952598, OS thread handle 0x7f5e10e38700, query id 248552715 192.168.0.7 vh_uon_com_ru
  insert into request_action (
                      ra_r_id,
                      ra_u_id,
                      ra_datetime,
                      ra_text,
                      ra_datetime_reply,
                      ra_reply,
                      ra_plan,
                      cl_id,
                      ra_tr_id,
                      ra_ss_id,
                      ra_h_id,
                      ra_uch_id,
                      ra_to_u_id,
                      ra_uct_id,
                      ra_shw
                  ) values (
                      40053,
                      906,
                      '2019-02-21 21:09:34',
                      'Звонок',
                      '2019-02-21 21:09:34',
                      '',
                      '0',
                      698,
                      0,
                      0,
                      0,
                      171114,
                      0,
  
  *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 2320 page no 546708 n bits 104 index `PRIMARY` of table `request_action` trx id 2947112777 lock_mode X locks gap before rec insert intention waiting
  *** (2) TRANSACTION:
  TRANSACTION 2947112774, ACTIVE 0 sec inserting
  mysql tables in use 11, locked 11
  5 lock struct(s), heap size 1184, 3 row lock(s)
  MySQL thread id 19952597, OS thread handle 0x7f5e11f3b700, query id 248552705 192.168.0.7
  insert into request_action (
                      ra_r_id,
                      ra_u_id,
                      ra_datetime,
                      ra_text,
                      ra_datetime_reply,
                      ra_reply,
                      ra_plan,
                      cl_id,
                      ra_tr_id,
                      ra_ss_id,
                      ra_h_id,
                      ra_uch_id,
                      ra_to_u_id,
                      ra_uct_id,
                      ra_shw
                  ) values (
                      25182,
                      906,
                      '2019-02-21 21:09:34',
                      'Звонок',
                      '2019-02-21 21:09:34',
                      '',
                      '0',
                      698,
                      0,
                      0,
                      0,
                      171113,
                      0,
  
  *** (2) HOLDS THE LOCK(S):
  RECORD LOCKS space id 2320 page no 546708 n bits 104 index `PRIMARY` of table `request_action` trx id 2947112774 lock mode S locks gap before rec
  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 2320 page no 546708 n bits 104 index `PRIMARY` of table `request_action` trx id 2947112774 lock_mode X locks gap before rec insert intention waiting
  *** WE ROLL BACK TRANSACTION (2)
  </code></pre>
  
  <p>In my.cf we have the following options:</p>
  
  <pre><code>max_connections = 10000
  key_buffer_size = 1024M
  join_buffer_size = 256M
  read_buffer_size = 256M
  sort_buffer_size = 256M
  tmp_table_size = 512M
  read_rnd_buffer_size = 8M
  max_heap_table_size = 512M
  
  thread_cache_size = 8192
  query_cache_type = 1
  
  query_cache_size = 15G
  wait_timeout = 6000
  connect_timeout = 15
  interactive_timeout = 60
  max_allowed_packet = 512M
  bulk_insert_buffer_size = 64M
  
  innodb_log_file_size                    = 512M
  innodb_log_buffer_size                  = 2G
  innodb_buffer_pool_size                 = 20G
  </code></pre>
  
  <p>Could you please help me with deadlock issue? How can I fix it? Should I rerun queries in deadlocks?</p>