Which of those would you like to discuss further? If not, maybe a minor change will make it work 'correctly'.
#Wordpress mysql deadlock code#
Check the other code - perhaps it works correctly even when there is a missing row from ctsa. When the row is deleted from ctsa, delete the corresponding row from ts.
If this is a big table, let's talk about ways to walk through it in chunks.
#Wordpress mysql deadlock full#
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.
#Wordpress mysql deadlock update#
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 kommentar(er)
