数据库锁实战二

一 实战

# 在客户端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)