MySQL Bugs: #58988: InnoDB Deadlock on SELECT - no FOR UPDATE or LOCK IN SHARE MODE clause

Bug #58988	InnoDB Deadlock on SELECT - no FOR UPDATE or LOCK IN SHARE MODE clause
Submitted:	16 Dec 2010 22:18	Modified:	17 Dec 2010 20:59
Reporter:	Paula Wing	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.41-3ubuntu12.8-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	deadlock, innodb

Description:
We have encountered deadlocks when running SELECT and INSERT, SELECT is in autocommit transaction, with no FOR UPDATE or LOCK IN SHARE MODE clause.

transaction isolation is repeatable read.

It is my understanding that the select should perform no locking, but it appears that it does.  Please advise.

Paula Wing
Kiva Systems

How to repeat:
start a transaction, update a parent table (i.e. one that has a FK referencing it on a child table).
in another connection (autocommit), select from the child table in a nested select?

We cannot reliably repeat, it occurs intermittently during testing.

------------------------
LATEST DETECTED DEADLOCK
------------------------
101216 14:06:12
*** (1) TRANSACTION:
TRANSACTION 0 6584119, ACTIVE 0 sec, process no 30732, OS thread id 139683681998608 starting index read
mysql tables in use 8, locked 5
LOCK WAIT 14 lock struct(s), heap size 3024, 34 row lock(s)
MySQL thread id 4009, query id 8153649 localhost 127.0.0.1 mhs Sending data
SELECT /*TaskConsistency-product*/ * FROM (SELECT   p.product_id,   p.available_quantity as p_available_quantity,   p.unavailable_quantity as p_unavailable_quantity,   ifnull((SELECT sum(gpm.available_quantity) FROM goo_product_map gpm JOIN goo g ON (g.goo_id = gpm.goo_id AND g.goo_type = 'P') JOIN pods on (g.pod_id = pods.pod_id and pods.status = 1)      WHERE product_id = p.product_id), 0) as bin_quantity,   ifnull((SELECT sum(milestones.untasked_quantity) from milestones JOIN jobs USING (job_id) where jobs.status = 2 and milestones.type IN ('G', 'R') and milestones.untasked_quantity != 0     AND (milestones.product_id = p.product_id)), 0) as p_untasked_quantity,   p.pickable_quantity as p_pickable_quantity,   pt.product_type_id,   pt.available_quantity as pt_available_quantity,   pt.pickable_quantity as pt_pickable_quantity,   pt.unavailable_quantity as pt_unavailable_quantity FROM products p JOIN product_types pt USING (product_t
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 87821 page no 3 n bits 120 index `PRIMARY` of table `mhs`.`milestones` trx id 0 6584119 lock mode S locks rec but not gap waiting
Record lock, heap no 52 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
 0: len 4; hex 80000018; asc     ;; 1: len 6; hex 000000647736; asc    dw6;; 2: len 7; hex 00000000391e5f; asc     9 _;; 3: len 1; hex 53; asc S;; 4: len 4; hex 80000012; asc     ;; 5: len 4; hex 80000001; asc     ;; 6: len 4; hex 80000000; asc     ;; 7: len 4; hex 80000001; asc     ;; 8: len 1; hex 80; asc  ;; 9: len 4; hex 80000001; asc     ;; 10: len 4; hex 80000000; asc     ;; 11: len 4; hex 80000001; asc     ;; 12: len 1; hex 81; asc  ;; 13: len 4; hex 80000002; asc     ;; 14: len 4; hex 80000008; asc     ;; 15: len 4; hex 80000000; asc     ;; 16: SQL NULL; 17: SQL NULL; 18: len 4; hex 80000001; asc     ;; 19: SQL NULL; 20: SQL NULL; 21: len 1; hex 81; asc  ;; 22: len 4; hex 80000000; asc     ;; 23: len 4; hex 80000004; asc     ;; 24: len 4; hex 4b495641; asc KIVA;; 25: len 4; hex 80000003; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 0 6584118, ACTIVE 0 sec, process no 30732, OS thread id 139683682600720 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3024, 9 row lock(s), undo log entries 2
MySQL thread id 3998, query id 8153662 localhost 127.0.0.1 mhs Updating
UPDATE jobs SET time_closed = NOW(), status = 1 WHERE job_id = 18
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 87821 page no 3 n bits 120 index `PRIMARY` of table `mhs`.`milestones` trx id 0 6584118 lock_mode X locks rec but not gap
Record lock, heap no 52 PHYSICAL RECORD: n_fields 26; compact format; info bits 0
 0: len 4; hex 80000018; asc     ;; 1: len 6; hex 000000647736; asc    dw6;; 2: len 7; hex 00000000391e5f; asc     9 _;; 3: len 1; hex 53; asc S;; 4: len 4; hex 80000012; asc     ;; 5: len 4; hex 80000001; asc     ;; 6: len 4; hex 80000000; asc     ;; 7: len 4; hex 80000001; asc     ;; 8: len 1; hex 80; asc  ;; 9: len 4; hex 80000001; asc     ;; 10: len 4; hex 80000000; asc     ;; 11: len 4; hex 80000001; asc     ;; 12: len 1; hex 81; asc  ;; 13: len 4; hex 80000002; asc     ;; 14: len 4; hex 80000008; asc     ;; 15: len 4; hex 80000000; asc     ;; 16: SQL NULL; 17: SQL NULL; 18: len 4; hex 80000001; asc     ;; 19: SQL NULL; 20: SQL NULL; 21: len 1; hex 81; asc  ;; 22: len 4; hex 80000000; asc     ;; 23: len 4; hex 80000004; asc     ;; 24: len 4; hex 4b495641; asc KIVA;; 25: len 4; hex 80000003; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 87892 page no 12 n bits 96 index `status` of table `mhs`.`jobs` trx id 0 6584118 lock_mode X locks rec but not gap waiting
Record lock, heap no 23 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;; 1: len 4; hex 80000012; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

mysql> show variables like 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name                           | Value                  |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index              | ON                     |
| innodb_additional_mem_pool_size         | 1048576                |
| innodb_autoextend_increment             | 8                      |
| innodb_autoinc_lock_mode                | 1                      |
| innodb_buffer_pool_size                 | 67108864               |
| innodb_checksums                        | ON                     |
| innodb_commit_concurrency               | 0                      |
| innodb_concurrency_tickets              | 500                    |
| innodb_data_file_path                   | ibdata1:10M:autoextend |
| innodb_data_home_dir                    |                        |
| innodb_doublewrite                      | ON                     |
| innodb_fast_shutdown                    | 1                      |
| innodb_file_io_threads                  | 4                      |
| innodb_file_per_table                   | ON                     |
| innodb_flush_log_at_trx_commit          | 2                      |
| innodb_flush_method                     |                        |
| innodb_force_recovery                   | 0                      |
| innodb_lock_wait_timeout                | 50                     |
| innodb_locks_unsafe_for_binlog          | OFF                    |
| innodb_log_buffer_size                  | 1048576                |
| innodb_log_file_size                    | 5242880                |
| innodb_log_files_in_group               | 2                      |
| innodb_log_group_home_dir               | ./                     |
| innodb_max_dirty_pages_pct              | 90                     |
| innodb_max_purge_lag                    | 0                      |
| innodb_mirrored_log_groups              | 1                      |
| innodb_open_files                       | 300                    |
| innodb_rollback_on_timeout              | OFF                    |
| innodb_stats_on_metadata                | ON                     |
| innodb_support_xa                       | ON                     |
| innodb_sync_spin_loops                  | 20                     |
| innodb_table_locks                      | ON                     |
| innodb_thread_concurrency               | 8                      |
| innodb_thread_sleep_delay               | 10000                  |
| innodb_use_legacy_cardinality_algorithm | ON                     |
+-----------------------------------------+------------------------+
35 rows in set (0.00 sec)

Full query for transaction 1 is as follows:

        SELECT /*TaskConsistency-product*/ * FROM (
                SELECT 
                  p.product_id, 
                  p.available_quantity as p_available_quantity, 
                  p.unavailable_quantity as p_unavailable_quantity, 
                  ifnull((SELECT sum(gpm.available_quantity)  
                            FROM goo_product_map gpm JOIN goo g ON (g.goo_id = gpm.goo_id AND g.goo_type = ?)
                            JOIN pods on (g.pod_id = pods.pod_id and pods.status = ?)
                    WHERE product_id = p.product_id), 0) as bin_quantity, +
                  ifnull((SELECT sum(milestones.untasked_quantity) 
                            from milestones JOIN jobs USING (job_id) 
                           where jobs.status =  ? 
                           and milestones.type IN (?,?)
                           and milestones.untasked_quantity != 0 
                          AND (milestones.product_id = p.product_id)), 0) as p_untasked_quantity, 
                  p.pickable_quantity as p_pickable_quantity, 
                  pt.product_type_id, 
                  pt.available_quantity as pt_available_quantity, 
                  pt.pickable_quantity as pt_pickable_quantity, 
                  pt.unavailable_quantity as pt_unavailable_quantity 
                FROM products p 
                JOIN product_types pt USING (product_type_id) 
                JOIN tasks USING (product_id) 
                WHERE tasks.task_id = ?) a 
        WHERE (p_available_quantity != bin_quantity - p_untasked_quantity) 
           OR p_available_quantity > p_pickable_quantity 
           OR pt_available_quantity > pt_pickable_quantity 
           OR p_unavailable_quantity > p_pickable_quantity 
           OR pt_unavailable_quantity > pt_pickable_quantity 
           OR p_pickable_quantity < 0 
           OR pt_pickable_quantity < 0 
           OR pt_pickable_quantity < p_pickable_quantity;

Looks like a duplicate of bug #46947. Please, check.

Yes, this duplicates 46759 - with binlog enabled, and STATEMENT level replication, there are locks on nested SELECT. 

Thanks for pointing me to that issue!

Duplicate of bug #46947.

Is this the same issue that was patched in 5.1.48 listed here: http://bugs.mysql.com/bug.php?id=46947

please ignore the last comment, posted to the wrong ticket, thanks!