一 实战
# 在客户端1,对 mylock 上写锁
mysql> lock tables mylock write;
Query OK, 0 rows affected (0.00 sec)
# 在客户端1,对 mylock 查询,成功
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a2 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (0.00 sec)
# 在客户端1,对 mylock 更新,成功
mysql> update mylock set name ='a2' where id =1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
# 在客户端1,对 dept 查询,失败
mysql> select * from dept;
ERROR 1100 (HY000): Table 'dept' was not locked with LOCK TABLES
# 在客户端2,对 dept 查询,成功
mysql> select * from dept limit 5;
+----+----------+------------+--------+
| id | deptName | address | ceo |
+----+----------+------------+--------+
| 1 | XkgdWALd | kPuELQZyuD | 330946 |
| 2 | EZhOwQRM | kOoeEHAEwt | 311519 |
| 3 | YXSwEFpJ | CIKzqiUVUL | 453423 |
| 4 | umbuTkwz | jxLmZowrtT | 121401 |
| 5 | EkoMSfXy | yVhYyuELUm | 287551 |
+----+----------+------------+--------+
5 rows in set (0.00 sec)
# 在客户端2,对 mylock 查询
# 查询阻塞
# 在客户端1,解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
# 在客户端2,mylock 阻塞解除,出结果
mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
| 1 | a2 |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
5 rows in set (38.48 sec)
二 小结
三 结论
读锁会阻塞写,但不会阻塞读。而写锁则会把读和写都阻塞。
四 表锁分析
1 哪些表加锁了
# 当 in_use 为 1,就是锁了
mysql> show open tables;
2 如何分析表锁定
mysql> show status like 'table_lock%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 263 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)