4

There is a table named company_competitors in our database. there is a job which truncates and loads this table daily. The table has two columns company_id and competitor_id both references another table companies.

CREATE TABLE `company_competitors` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) DEFAULT NULL,
  `competitor_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_rails_company_id_c1ac450a` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_rails_competitor_id_772a45c6` FOREIGN KEY (`competitor_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=268477 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

The steps included in truncate and load are:

  1. create a new table company_competitors_new
  2. add foreign key constraints on both the columns (company_id, competitor_id) one by one.
  3. load the data into the new table.
  4. swap the tables company_competitors and company_competitors_new.

Queries:

"CREATE TABLE company_competitors_new LIKE company_competitors;

alter table company_competitors_new ADD CONSTRAINT fk_rails_company_id_53f8f57a foreign key (company_id) references companies(id);'

The companies table might be accessed by some else while adding foreign key. So, When the second query is running the database gets stuck in a deadlock and no queries on companies table get executed.

show full processlist shows all queries saying Waiting to acquire Metadata lock and I have to kill all select queries so that adding of foreign key gets finished.

I need help in understanding the reason for deadlock here and how can I deal with it. I would also like to hear if there is a better way to truncate and load with 0 downtime.

rajat
  • 3,415
  • 15
  • 56
  • 90
Yash Thenuan
  • 591
  • 5
  • 17
  • 3
    You're not telling the whole story. When you add your foreign key directly after creating the table, adding the foreign key executes almost instantly since the table is empty. And on a sidenote, you're not having trouble with a deadlock. That's a completely different thing. – fancyPants Apr 30 '19 at 10:51
  • It should ideally execute immediately, but it doesn't sometimes and gets stuck in `Waiting to acquire Metadata lock` – rajat May 01 '19 at 08:59
  • this article saved me on many occasions. [pt-online-schema-change] (https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html) – ninjabber May 04 '19 at 12:39

1 Answers1

2

Personally if deadlock occurs rarely i just reprepare a new transaction and relaunch queries (it's not the best wanted solution but works in this cases). However if your deadlock appear sistematically as you mentioned you can check for more info with command SHOW ENGINE INNODB STATUS to see some details of recent deadlocks. My advice is also to put the block which creates company_competitors_new between

SET foreign_key_checks=0;

...(queries)...

SET foreign_key_checks=1;

BUcorp
  • 350
  • 2
  • 12