MyISAM 表锁的优先级问题

想验证 myisam 表的锁的优先级，做了下面的实验。使用的测试表，结构如下
mysql> show create table tx\G
*************************** 1. row ***************************
Table: tx
Create Table: CREATE TABLE `tx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

然后分别打开打开三个控制台 ABC ，按下面操作顺序执行：

1 、 A 运行：
mysql> lock table tx read;
Query OK, 0 rows affected (9 min 37.73 sec)

2 、 B 运行：
mysql> select * from tx;
这里在等待

3 、 C 运行：
mysql> insert into tx (num) value (1);
这里在等待

4 、 A 运行：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

此时看控制台 B ，显示
mysql> select * from tx;
Empty set (1 min 25.67 sec)

这里有个疑惑，写锁不是会插到读锁请求之前吗？但是显式的给 B 、 C 加上 lock 语句，就可以看到 C （写）先获得锁。
这是为什么呢？

MySQL

table

MyISAM

create

3 replies • 2016-07-14 00:16:43 +08:00

yangqi

Jul 13, 2016

"WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it.

LOCK TABLES acquires locks as follows:

1.Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined.

2.If a table is to be locked with a read and a write lock, put the write lock request before the read lock request.

3.Lock one table at a time until the session gets all locks."

Rosicky

Jul 13, 2016

手动加锁和自动加锁有什么区别？

Rosicky

Jul 14, 2016

贴下另一组测试，顺序运行

1 、 A 运行：
mysql> lock table tx read;
Query OK, 0 rows affected (9 min 37.73 sec)

2 、 B 运行：
mysql> lock table tx read;
这里在等待

3 、 C 运行：
mysql> lock table tx write;
这里在等待

4 、 A 运行：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

此时看控制台 C 已获得锁， B 等待。这个结果就和文档一样