Just looking at those Deletes, I see two issues. One will be automatically killed (by InnoDB) the other will to the task assigned. Those two DELETEs are each doing a full table scan looking for certain rows to delete. `topic_subscriptions` trx id 22501319 lock_mode X waiting RECORD LOCKS space id 861 page no 5 n bits 192 index PRIMARY of table. *** (2) WAITING FOR THIS LOCK TO BE GRANTED: `endpoint_to_topic_subscription_associations` trx id 22501319 lock_mode X locks rec but not gap RECORD LOCKS space id 824 page no 5 n bits 568 index FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl of table. TRANSACTION 22501319, ACTIVE 0 sec starting index readĢ4 lock struct(s), heap size 1136, 21 row lock(s), undo log entries 13 `endpoint_to_topic_subscription_associations` trx id 22501318 lock mode S waiting *** (1) WAITING FOR THIS LOCK TO BE GRANTED: MySQL thread id 2699877, OS thread handle 47459092735744, query id 13624170039 172.25.150.126 Sending dataĭELETE ts FROM topic_subscriptions AS ts LEFT JOIN endpoint_to_topic_subscription_associations AS ctsa ON ts.id=topic_subscription_id WHERE ctsa.topic_subscription_id IS NULL LOCK WAIT 24 lock struct(s), heap size 1136, 881 row lock(s), undo log entries 8 ![]() TRANSACTION 22501318, ACTIVE 0 sec starting index read ![]() Please help me understand the issue here. I do use SELECT FOR UPDATE mechanism in other transactions but it doesn't involve the mentioned tables directly.Īlso, the default isolation level is REPEATABLE_READ. 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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |