Databases
StackOverflow2013
Postsdbo
POMySQL deadlock for simple delete
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL deadlock for simple delete
    primarykey
    data
    text
    <p>How can such a simple delete query cause a deadlock in MySQL and what would I do about it, specifically?</p> <pre><code>DELETE FROM view_offers WHERE view_id = 55 </code></pre> <p>For the table</p> <pre><code>CREATE TABLE `view_offers` ( `view_id` int(11) NOT NULL DEFAULT '0', `user_id` int(11) DEFAULT NULL, `offer_id` bigint(20) unsigned NOT NULL DEFAULT '0', `offer_source` int(11) NOT NULL, `offer_source_id` bigint(20) unsigned NOT NULL, `product_id` bigint(20) unsigned DEFAULT NULL, `shop_id` bigint(20) unsigned DEFAULT NULL, `category_id` mediumint(8) unsigned DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `relevance` float NOT NULL DEFAULT '0', PRIMARY KEY (`view_id`,`offer_id`), KEY `index_view_offers_on_view_id_and_product_id` (`view_id`,`product_id`), KEY `index_view_offers_on_view_id_and_shop_id` (`view_id`,`shop_id`), KEY `index_view_offers_on_view_id_and_category_id` (`view_id`,`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; </code></pre> <p>I am aware of the concept of deadlocks per se in the context of programming. I would understand a deadlock in my database if I locked multiple tables simultaneously in different orders, but there is only one table in this query...</p> <p>Is this happening on key level instead of table level? There must be dozens if not a hundred different queries in my Rails app.</p> <p>How do I find out the other query/queries involved in the deadlock?</p> <h2>EDIT: This is the deadlock info from <code>SHOW InnoDB STATUS</code></h2> <p>As far as I understand this: - thread 1 has no locks and waits to lock PRIMARY - thread 2 has lock PRIMARY and waits to lock PRIMARY This, however does not make sense to me as it does not sound like a deadlock.</p> <pre><code>------------------------ LATEST DETECTED DEADLOCK ------------------------ 130214 18:06:52 *** (1) TRANSACTION: TRANSACTION 0 6720854, ACTIVE 2 sec, process no 10951, OS thread id 140317946369792 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 419387, query id 2333457 10.0.3.3 foo updating DELETE FROM view_offers WHERE view_id = 55 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6987532 n bits 168 index `PRIMARY` of table `foo`.`view_offers` trx id 0 6720854 lock_mode X waiting Record lock, heap no 41 PHYSICAL RECORD: n_fields 12; compact format; info bits 0 0: len 4; hex 80000037; asc 7;; 1: len 8; hex 00005f9c7d5f9261; asc _ }_ a;; 2: len 6; hex 000000668d4c; asc f L;; 3: len 7; hex 80001ec007080d; asc ;; 4: len 4; hex 80000039; asc 9;; 5: len 4; hex 80000002; asc ;; 6: len 8; hex 000000000c001798; asc ;; 7: len 8; hex 022c4f4dc34a1e6e; asc ,OM J n;; 8: len 8; hex 02796b4da8fe914c; asc ykM L;; 9: len 3; hex 019342; asc B;; 10: len 30; hex 4e722e2035382050686f746f202831376d6c29202d2054696e74656e6661; asc Nr. 58 Photo (17ml) - Tintenfa;...(truncated); 11: len 4; hex 00000000; asc ;; *** (2) TRANSACTION: TRANSACTION 0 6720844, ACTIVE 3 sec, process no 10951, OS thread id 140336508557056 inserting, thread declared inside InnoDB 500 mysql tables in use 2, locked 2 7762 lock struct(s), heap size 817136, 82058 row lock(s), undo log entries 28808 MySQL thread id 419384, query id 2333448 10.0.3.2 foo Sending data INSERT INTO view_offers SELECT 55, 57, uiid, source, source_id, 100, 200, category_id, name, relevance FROM `offers` WHERE `offers`.`source` IN (2) AND (((product_source = 2 AND product_source_id IN (1,2,3,.. *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 6987532 n bits 152 index `PRIMARY` of table `foo`.`view_offers` trx id 0 6720844 lock_mode X locks rec but not gap Record lock, heap no 41 PHYSICAL RECORD: n_fields 12; compact format; info bits 0 0: len 4; hex 80000037; asc 7;; 1: len 8; hex 00005f9c7d5f9261; asc _ }_ a;; 2: len 6; hex 000000668d4c; asc f L;; 3: len 7; hex 80001ec007080d; asc ;; 4: len 4; hex 80000039; asc 9;; 5: len 4; hex 80000002; asc ;; 6: len 8; hex 000000000c001798; asc ;; 7: len 8; hex 022c4f4dc34a1e6e; asc ,OM J n;; 8: len 8; hex 02796b4da8fe914c; asc ykM L;; 9: len 3; hex 019342; asc B;; 10: len 30; hex 4e722e2035382050686f746f202831376d6c29202d2054696e74656e6661; asc Nr. 58 Photo (17ml) - Tintenfa;...(truncated); 11: len 4; hex 00000000; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 6987532 n bits 184 index `PRIMARY` of table `foo`.`view_offers` trx id 0 6720844 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 41 PHYSICAL RECORD: n_fields 12; compact format; info bits 0 0: len 4; hex 80000037; asc 7;; 1: len 8; hex 00005f9c7d5f9261; asc _ }_ a;; 2: len 6; hex 000000668d4c; asc f L;; 3: len 7; hex 80001ec007080d; asc ;; 4: len 4; hex 80000039; asc 9;; 5: len 4; hex 80000002; asc ;; 6: len 8; hex 000000000c001798; asc ;; 7: len 8; hex 022c4f4dc34a1e6e; asc ,OM J n;; 8: len 8; hex 02796b4da8fe914c; asc ykM L;; 9: len 3; hex 019342; asc B;; 10: len 30; hex 4e722e2035382050686f746f202831376d6c29202d2054696e74656e6661; asc Nr. 58 Photo (17ml) - Tintenfa;...(truncated); 11: len 4; hex 00000000; asc ;; *** WE ROLL BACK TRANSACTION (1) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJan
    1. USJan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COmaybe with: SHOW InnoDB STATUS; or with http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks
      singulars
      1. POMySQL deadlock for simple delete
      1. USDaan Geurts
    2. COThank you, Daan. I edited in the output. The specific queries and keys are given, but I don't see why they would deadlock.
      singulars
      1. POMySQL deadlock for simple delete
      1. USJan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload