允许多个线程同时读取数据,比如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();
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