数据库锁实战二

小鱼儿 2022-09-12 09:51 144阅读 0赞

# 一 实战 #

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

# 二 小结 #

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

# 三 结论 #

读锁会阻塞写，但不会阻塞读。而写锁则会把读和写都阻塞。

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

# 四 表锁分析 #

### 1 哪些表加锁了 ###

# 当 in_use 为 1，就是锁了
    mysql> show open tables;

### 2 如何分析表锁定 ###

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/20e7089584fe42138356c20cf8d681fd.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/d7f3adf736944dcab2f08b2be92f0077.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/73cacb3541f540359bee3d44cd4ad7e6.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220828/96766e95b6fe48ecbc340131f3f61cf6.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAY2hlbmdxaXVtaW5n_size_20_color_FFFFFF_t_70_g_se_x_16 4]: /images/20220828/d6f7a6aa2e7c4a75b187e81cee623fa5.png