Sample Header Ad - 728x90

Does sorting reduce MySQL deadlock likelihood of INSERT/UPDATE batches?

0 votes
0 answers
42 views
We know that MySQL INSERT/ON DUPLICATE KEY UPDATE statements create gap locks which may lead to deadlocks between multiple threads. If the INSERT/UPDATE is done in batches (either in a separate transaction for each batch or all batches in the same transaction), do we reduce the likelihood of deadlocks by sorting the data first? Here's an example where that seems to be the case, but I don't understand why. - https://github.com/fleetdm/fleet/issues/1146#issuecomment-865134315 - CREATE TABLE for the above example:
CREATE TABLE label_membership (
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  label_id int(10) unsigned NOT NULL,
  host_id int(10) unsigned NOT NULL,
  PRIMARY KEY (host_id,label_id),
  KEY idx_lm_label_id (label_id),
  CONSTRAINT fk_lm_host_id FOREIGN KEY (host_id) REFERENCES hosts (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_lm_label_id FOREIGN KEY (label_id) REFERENCES labels (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- Full schema: https://github.com/fleetdm/fleet/blob/8b4c6a1dd7d2683b9e7e6ebe22f372f0a419e6d8/server/datastore/mysql/schema.sql#L266
Asked by Victor L (97 rep)
Dec 3, 2024, 09:51 PM
Last activity: Dec 6, 2024, 08:38 PM