mysql innodb锁查询_在5.1版本上 innodb有种查询会锁表

本帖最后由 0715208 于 2012-11-29 10:54 编辑

环境：mysql 版本 5.1(在 5.5的版本上是没有这样的情况了).

今天看到一个查询语句竟然会被锁住，下面是当时运行sql的情况：

mysql> SELECT  sql_no_cache dw.NOWPRICE,

dw.DOWNRATE,

dw.GOODSID,

dw.WEBSITID,

->        dw.DOWNRATE,

->        dw.GOODSID,

->        dw.WEBSITID,

->        c.GOODSTITLE,

->        d.WEBSITNAME,

->        e.PICURL AS GOODSPICURL,

->        (SELECT sum(f.COMMENTNUM)

->           FROM SAVEGOODSPRICE f

->          WHERE f.GOODSID = dw.GOODSID AND f.STATUS = 1)

->           AS COMNUM

->   FROM (SELECT a.DOWNRATE,

->                a.GOODSID,

->                a.WEBSITID,

->                a.NOWPRICE

->           FROM    SAVEGOODSDOWPRICE a

->                INNER JOIN

->                   SAVEGOODSANDTYPE b

->                ON a.GOODSID = b.GOODSID

->          WHERE     a.GOODSID != 52829

->                AND b.STATUS = 1

->                AND a.STATUS = 1

->               -- AND b.GTYPE3 = 193

->               -- AND a.DOWNRANK >= 1

->         ORDER BY a.ID DESC

->          LIMIT 5) dw

->        JOIN SAVEGOODSBASE c

->           ON dw.GOODSID = c.ID

->        JOIN SAVEWEBSIT d

->           ON d.ID = dw.WEBSITID

->        JOIN SAVEGOODSPIC e

->           ON e.GOODSID = c.ID AND e.ISMAINPIC = 1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

通过这个错误的信息得出，这个select确实等待所要的资源，超过了innodb_lock_wait_timeout时间报错了，这个想不明白，这些表都是innodb，都知道innodb对select是使用mvcc的啊，应该是不可能

被锁住的。如果select语句被锁住了的话，那么这个select到底要得到哪些锁才能运行呢？所以在测试环境上，先运行这个sql，在对着里面的每个表进行select.....from   for update;

最后得到的结果是这里面只有SAVEGOODSPRICE 表被锁住了，这个sql可以看到有一个特殊的情况 就是对SAVEGOODSPRICE表，这个表是在from前面。

所以假设：

难道对于查询语句像：select  table2.id , (select name  from table1 where table1.cusid=table2.id)  from table2  这样类型的查询语句，这个会对table1表就行锁表。

所以在测试环境，进行测试后：得到的结果就是对于table1这个表会加共享锁的，这个共享锁可以是是表锁，也可以是行锁，这个要那看table1 的查询。

下面问了证实上面说的，就进行一个简单的测试：

1：测试前的准备：

CREATE TABLE `PUBZONE` (

`ZONECODE` smallint(6) NOT NULL AUTO_INCREMENT,

`NAME` varchar(30) NOT NULL,

PRIMARY KEY (`ZONECODE`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

insert into `PUBZONE`(`NAME`) values ('北京市');

insert into `PUBZONE`(`NAME`) values ('天津市');

insert into `PUBZONE`(`NAME`) values ('河北省');

insert into `PUBZONE`(`NAME`) values ('山西省');

insert into `PUBZONE`(`NAME`) values ('内蒙古');

CREATE TABLE `CUSUSER` (

`ID` smallint(6) NOT NULL AUTO_INCREMENT,

`NAME` varchar(30) NOT NULL,

`ZONECODE` smallint(6) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

insert into CUSUSER  values(null,'赵',1);

insert into CUSUSER  values(null,'李',2);

insert into CUSUSER  values(null,'王',3);

insert into CUSUSER  values(null,'程',8);

2：

session1：

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from PUBZONE p  for update ;

+----------+-----------+

| ZONECODE | NAME      |

+----------+-----------+

|        1 | 北京市    |

|        2 | 天津市    |

|        3 | 河北省    |

|        4 | 山西省    |

|        5 | 内蒙古    |

+----------+-----------+

5 rows in set (0.00 sec)

session2:

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql>    select   c.NAME,c.ZONECODE ,(select  NAME  from PUBZONE p where p.ZONECODE=c.ZONECODE)  as ZONENAME from CUSUSER c;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session1：

mysql>rollback;

session2

mysql>rollback;

可以看出来对于这种类型的查询会在PUBZONE 表上加共享锁，这里说名下，如果PUBZONE 使用的查询可以是行锁的话 那么就是锁住的行锁，如果不能使用索引的话，那就是表锁。

看到这里其实我们都知道上面的那个sql其实没有必要那么去写的：我们可以写成下面这个形式：

mysql>  select   c.NAME,c.ZONECODE ,p.NAME from CUSUSER c left join PUBZONE  p  on p.ZONECODE=c.ZONECODE;

+------+----------+-----------+

| NAME | ZONECODE | NAME      |

+------+----------+-----------+

| 赵   |        1 | 北京市    |

| 李   |        2 | 天津市    |

| 王   |        3 | 河北省    |

| 程   |        8 | NULL      |

+------+----------+-----------+

4 rows in set (0.00 sec)

也可以得到结果，而这个sql是没有锁表的情况的，我这个只是简单的测试例子。基本上对于这类的查询都可以进行转换的，但是有的开发为了简单(就上最开始的那个sql那样)，图省事会写上面的那

种类型的sql。

总结：

1：对于select  table2.id , (select name  from table1 where table1.cusid=table2.id)  from table2会锁表的。这样的话对于并发不是很好。

1：对于select  table2.id , (select name  from table1 where table1.cusid=table2.id)  from table2  这样类型的查询语句尽量的少写，或者不写。最好进行使用left join进行转化。

2：我这个版本是在，5.1的版本上，但是我在5.5上进行测试的时候就没有这种情况了。看样子mysql的升级了。