关于mysql中myisam中表级锁

mysql为myisam类型提供了表级别锁定。


允许多个线程同时读取数据，比如select之间，不需要锁等待。按个排队就行。


但是如果碰到更新操作。比如update就会排斥其他查询如select


因为更新操作有默认的优先级，意思就是表锁释放之后，财会轮到读取操作
，就像红绿灯转弯，右转让左转弯。

测试方法：

首先建立个表count_t

mysql> show create table count_t
    -> ;
+---------+------------------------------------------------------------
-----------------------------------------------------------------------
----------------------------+
| Table   | Create Table


                            |
+---------+------------------------------------------------------------
-----------------------------------------------------------------------
----------------------------+
| count_t | 

CREATE TABLE `count_t` (
  `count` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100001 DEFAULT CHARSET=gbk |
+---------+------------------------------------------------------------
-----------------------------------------------------------------------
----------------------------+
1 row in set (0.00 sec)

然后向表中插入10w条数据

<?php
ini_set("memory_limit","-1");
$db_host='localhost';
$db_user='root';
$db_password='root';
$db_name='demo';
$mysqli = new mysqli($db_host, $db_user, $db_password, $db_name); 

for ($i=1; $i<=100000;$i++) {
$key=rand(1,999);
 $sql[]="('$key')";
 }
$result=$mysqli->query('insert into count_t (count) values '.implode(',',$sql));
if ($result){
         echo "执行成功";
}else {
         printf("Errormessage: %s\n", mysqli_error($mysqli));
}
$mysqli->close();

然后写个php脚本，供更新操作使用：ab压力测试，拖住时间。

php脚本 mysqlupdate.php

<?php
ini_set("memory_limit","-1");
$db_host='localhost';
$db_user='root';
$db_password='root';
$db_name='demo';
$mysqli = new mysqli($db_host, $db_user, $db_password, $db_name); 
$result=$mysqli->query('update  count_t set count =count+1');
if ($result){
         echo "执行成功";
}else {
         printf("Errormessage: %s\n", mysqli_error($mysqli));
}
$mysqli->close();

我用的phpstudy：

D:\phpStudy\Apache\bin>

ab -n 500 -c 500 http://localhost/demo/mysqlupdate.php

ab压力测试的同时在新开的mysql终端中执行下面语句，执行过程发现mysql无反应。然后在新开的mysql终端中查看mysql的processlist

mysql> select * from count_t where id=12221;
+-------+-------+
| count | id    |
+-------+-------+
|   801 | 12221 |
+-------+-------+
1 row in set (33.08 sec)

然后在新开的mysql终端中查看mysql的processlist，如下所示：

mysql> show processlist;
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------------------+
| Id  | User | Host            | db   | Command | Time | State                        | Info                                 |
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------------------+
|   1 | root | localhost:63551 | demo | Sleep   | 1046 |                              | NULL                                 |
|   5 | root | localhost:63664 | demo | Sleep   |  234 |                              | NULL                                 |
|   7 | root | localhost:63696 | NULL | Sleep   | 1046 |                              | NULL                                 |
|   8 | root | localhost:63828 | demo | Query   |    0 | NULL                         | show processlist                     |
|  12 | root | localhost:64211 | demo | Query   |   16 | Waiting for table level lock | select * from count_t where id=12221 |
| 864 | root | localhost:51108 | demo | Query   |    1 | Updating                     | update  count_t set count =count+1   |
| 865 | root | localhost:51110 | demo | Query   |    1 | Waiting for table level lock | update  count_t set count =count+1   |
| 866 | root | localhost:51112 | demo | Query   |    1 | Waiting for table level lock | update  count_t set count =count+1   |
| 867 | root | localhost:51115 | demo | Query   |    1 | Waiting for table level lock | update  count_t set count =count+1   |
| 868 | root | localhost:51117 | demo | Query   |    1 | Waiting for table level lock | update  count_t set count =count+1   |
| 869 | root | localhost:51119 | demo | Query   |    1 | Waiting for table level lock | update  count_t set count =count+1   |
| 870 | root | localhost:51121 | demo | Query   |    0 | Waiting for table level lock | update  count_t set count =count+1   |
+-----+------+-----------------+------+---------+------+------------------------------+--------------------------------------+
12 rows in set

其中waiting的就是。

刚刚看到updateing的那句，就是正在执行的update操作，然后逐级释放，最后到select