关于mysql MYISAM引擎的锁问题
CREATE TABLE `citymy` (
`city_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`city` varchar(50) CHARACTER SET utf8 NOT NULL,
`country_id` smallint(5) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
UNLOCK TABLES
LOCK TABLE citymy READ;
mysql> delete from citymy;
mysql>show processlist;
+----+------+--------------------+--------+---------+------+------------------------------+--------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+--------+---------+------+------------------------------+--------------------+
| 3 | root | localhost | sakila | Query | 1287 | Waiting for table level lock | delete from citymy |
| 8 | root | 10.180.2.152:51043 | sakila | Sleep | 350 | | NULL |
| 9 | root | 10.180.2.152:51044 | NULL | Sleep | 1701 | | NULL |
| 40 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+--------------------+--------+---------+------+------------------------------+--------------------+
4 rows in set (0.00 sec)
mysql> drop table citymy;
mysql> show processlist;
+----+------+--------------------+--------+---------+------+---------------------------------+--------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+--------------------+--------+---------+------+---------------------------------+--------------------+
| 3 | root | localhost | sakila | Query | 1468 | Waiting for table level lock | delete from citymy |
| 8 | root | 10.180.2.152:51043 | sakila | Sleep | 531 | | NULL |
| 9 | root | 10.180.2.152:51044 | NULL | Sleep | 1882 | | NULL |
| 40 | root | localhost | NULL | Query | 0 | init | show processlist |
| 43 | root | localhost | sakila | Query | 94 | Waiting for table metadata lock | drop table citymy |
| 44 | root | localhost | sakila | Query | 26 | Waiting for table metadata lock | drop table citymy |
+----+------+--------------------+--------+---------+------+---------------------------------+--------------------+
6 rows in set (0.00 sec)
