内建的builtin InnoDB,子查询阻塞更新

奇龙在做db变更的时候，update被阻塞：

root@127.0.0.1 : (none) 23:51:35> show variables like ‘%ignore_builtin%’;

| Variable_name | Value |

| ignore_builtin_innodb | OFF |

—>查看show processlist:

| 5696061853 | houyi | 10.242.219.1:42661 | dbname | Query | 22936 | Sending data | select * from xx_shot where id in (select parent_xx_shot from xx_shot where parent_xx_shot!=”” a |

|5696162576 | houyi | 10.242.219.1:44290 | dbname | Query | 22695 | Sending data | select id from xx_shot where id in (select parent_xx_shot from xx_shot where parent_xx_shot!=”” |

| 5696208999 | houyi | 10.242.219.1:45610 | dbname | Query | 22586 | Sending data | select id from xx_shot where id in (select parent_xx_shot from xx_shot where parent_xx_shot

—>查看show innodb status\G;

mysql tables in use 2, locked 14562 lock struct(s), heap size 489456, 604071 row lock(s)MySQL thread id 5696208999, query id 15226780028 10.242.219.1 houyi Sending dataselect id from xx_shot where id in (select parent_xx_shot from xx_shot where parent_xx_shot!=”” and gmt_create>’2012-04-27′ and xx_shot_progress<0 )Trx read view will not see trx with id >= 1 2411988144, sees < 1 2411827879—TRANSACTION 1 2411935441, ACTIVE 22560 sec, process no 1982, OS thread id 1188837696 fetching rows, thread declared inside InnoDB 228mysql tables in use 2, locked 15311 lock struct(s), heap size 636912, 1199769 row lock(s)

这个表确实被锁住了

—>kill 子查询

kill 5696061853 ;

kil 5696162576;

kil 5696208999;

root@127.0.0.1 : dbname 23:41:40> update xx_shot set xx_shot_progress=-6 where id=2083430;

Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: root@127.0.0.1 : dbname 23:41:42> commit;

Query OK, 0 rows affected (0.00 sec)
测试5.1 innodb db pluginroot@test 02:51:27>show global variables like ‘%igno%’; Variable_name | Value |

ignore_builtin_innodb | ON |

root@test 02:51:53>show global variables like ‘%version%’;

| Variable_name | Value |

| innodb_version | 1.0.9 |

session1：

root@test 02:46:31>select id from xx_shot where id in (select parent_xx_shot   from xx_shot where parent_xx_shot != “” and gmt_create > ‘2012-04-27’  and xx_shot_progress < 0 group by device_id HAVING count(device_id) = 1);

session2：

root@test 02:47:26>update xx_shot set xx_shot_progress=-6 where id=1163184;Query OK, 0 rows affected (0.00 sec)

关闭掉bultin，innodb_version为1.0.9中子查询并没有阻塞更新；
造成上面问题的还有一个主要原因为mysql处理子查询的不优秀：http://hidba.org/?p=412

root@test 03:21:06>explain select id  from xx_shot c1, (select parent_xx_shot-> from xx_shot where parent_xx_shot != “” and gmt_create  ‘2012-04-27’  and xx_shot_progress < 0  group by device_id  HAVING count(device_id) = 1) c2  where c1.id = c2.parent_xx_shot  ;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |

| 2 | DERIVED | xx_shot | ALL | NULL | NULL | NULL | NULL | 170786 | Using where; Using temporary; Using filesort |

改写sql后：root@test 03:21:06>explain select id  from xx_shot c1,  (select parent_xx_shot  from xx_shot where parent_xx_shot != “”  and gmt_create > ‘2012-04-27’ and xx_shot_progress < 0 group by device_id HAVING count(device_id) = 1) c2 where c1.id = c2.parent_xx_shot ;

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |

| 2 | DERIVED | xx_shot | ALL | NULL | NULL | NULL | NULL | 170786 | Using where; Using temporary; Using filesort |

执行时间：root@test 03:21:05>select id  from xx_shot c1,  (select parent_xx_shot  from xx_shot where parent_xx_shot != “”  and gmt_create > ‘2012-04-27’ and xx_shot_progress < 0 group by device_id HAVING count(device_id) = 1) c2 where c1.id = c2.parent_xx_shot ;

Empty set (0.17 sec)