KEY `FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl` (`topic_subscription_id`),ĬONSTRAINT `FK_endpoint_to_topic_subscription_ref_endpoints` FOREIGN KEY (`endpoint_id`) REFERENCES `endpoints` (`id`),ĬONSTRAINT `FK_endpoint_to_topic_subscription_ref_org_tbl` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`),ĬONSTRAINT `FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl` FOREIGN KEY (`topic_subscription_id`) REFERENCES `topic_subscriptions` (`id`)Īs you can see I am not doing any explicit locking here or SELECT FOR UPDATE in the query above. KEY `FK_endpoint_to_topic_subscription_ref_org_tbl` (`org_id`), PRIMARY KEY (`endpoint_id`,`topic_subscription_id`), `topic_subscription_id` varchar(16) NOT NULL, KEY `FK_topic_subs_refs_virtual_brokers_table` (`virtual_broker_id`),ĬONSTRAINT `FK_topic_subs_refs_virtual_brokers_table` FOREIGN KEY (`virtual_broker_id`) REFERENCES `virtual_brokers` (`id`),ĬONSTRAINT `FK_topic_subscriptions_references_organizations_table` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`)ĬREATE TABLE `endpoint_to_topic_subscription_associations` ( KEY `FK_topic_subscriptions_references_organizations_table` (`org_id`), UNIQUE KEY `UK_ts_vbid_on_topic_subscriptions` (`topic_subscription`,`virtual_broker_id`), `virtual_broker_id` varchar(16) NOT NULL, `topic_subscription` varchar(255) NOT NULL, Here are the DDLs for the 2 tables in question: CREATE TABLE `topic_subscriptions` ( ] (conn=2699877) Deadlock found when trying to get lock try restarting transaction nested exception is : (conn=2699877) Deadlock found when trying to get lock try restarting transaction ON ts.id=topic_subscription_id WHERE ctsa.topic_subscription_id IS NULL It runs within the transaction as the last step.Īnd, as a result, I am seeing occasional dead lock exceptions coming from MySQL: : PreparedStatementCallback ĭELETE ts FROM topic_subscriptions AS ts LEFT JOINĮndpoint_to_topic_subscription_associations AS ctsa I have recently updated my production code with the new query that is supposed to clean up orphaned records.
0 kommentar(er)
