MySQL高级知识（十六）——表锁

此博客的内容主要来源于尚硅谷的视频中，在此记录，以备以后自己查看。

1. 特点

表锁特点：

• 偏向MyISAN存储引擎，开销小，加锁快；

• 无死锁；

• 锁定粒度大，发生锁冲突的概率最高，并发度最低

2. 案例分析

2.1. 准备

1. 创建表SQL

drop table if exists mylock;
create table mylock (
    id int primary key auto_increment,
    name varchar (20) not null
) engine myisam default charset = utf8;
insert into mylock (name) values ('a');
insert into mylock (name) values ('b');
insert into mylock (name) values ('c');
insert into mylock (name) values ('d');
insert into mylock (name) values ('e');

2. 手动增加表锁命令

#命令格式
lock table tablename1 read(write), tablename2 read(write);

3. 查看表是否被加锁

show open tables;

如果In_use显示不为0，则表示表被加锁。

4. 释放锁命令

unlock tables;

2.2. 表锁（read）案例

我们创建两个会话，session_1与session_2，其中session_1会话对mylock加上读的表锁。

下面每一行都有两个会话的对比情况。

会话1（session_1）	会话2（session_2）
获取表mylock 的read锁定	待session_1开启读锁后，session_2再连接终端
当前session可以查询该表记录	其他session也可以查询该表的记录
当前session不可以查询其他没有锁定的表	其他session可以查询或更新未锁定的表
当前session中插入或者更新锁定表会提示错误：	其他session插入或者更新锁定表会一直等待获得锁
释放锁：	session2获取表，插入操作完成：

总结：

• 加锁会话：

  1. 在会话1上面给mylock表加上读锁，那么会话1对此表读数据没有问题；

  2. 在该会话中对此表进行更新插入操作，系统会提示表被加锁，不能进行操作。因为在表未解锁之前不能进行这些操作。

  3. 在该会话中不能够对其他表进行读取。因为该会话对mylock表加了读锁，在未解锁之前不能操作其他表。

• 其他会话：

  1. 在该会话中可以对被加上锁的表mylock进行查询操作；

  2. 在该会话中可以对其他未被加锁的表进行任何操作，查询、插入、更新都可以；会话1加锁，但是不影响其他会话对其他表的所有操作

  3. 在该会话中对加锁的表进行更新修改操作，会出现阻塞情况，因为加锁会话对表加锁，其他会话是不能够对该表进行更新操作；当加锁会话对表进行解锁后，该其他会话的阻塞情况就会继续进行，操作可以执行结束。

• 加锁会话：
  1. 大体就是加锁的会话，这个会话可以且只能够对被加上锁的表进行查询操作；
  2. 不能够对其他表做任何操作
• 其他会话：
  1. 大体上其他会话，可以对被加上锁的表进行查询操作；
  2. 可以对其他表做任何操作
  3. 对加锁表进行更新操作都会进入阻塞状态，直到表被解锁之后继续执行

2.3. 表锁（write）案例

我们创建两个会话，session_1与session_2，其中session_1会话对mylock加上写的表锁。

下面每一行都有两个会话的对比情况。

会话1（session_1）	会话2（session_2）
获得表mylock的write锁定	待session_1开启读锁后，session_2再连接终端
当前会话对锁定的表的查询+更新+插入操作都可以执行：	其他会话对锁定表的查询被阻塞，需要等待锁被释放；（在锁表钱，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下，session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞。
释放锁：	session2获取锁，查询返回：

总结：

• 在当前被添加写锁的会话中，只能对被添加上写锁的表进行操作；

• 在其他的会话中，对被加上写锁的表的操作都将被阻塞，等到锁被释放之后可以执行；

• 在其他的会话中，可以对其他任意表进行任意操作。

2.4. 表锁定分析

1. 使用如下命令查看表是否被锁定：

show open tables where In_use > 0;

2. 使用如下命令分析表锁：

show status like 'table%';

注意：

• Table_locks_immediate：产生表级锁定的次数，表示可立即获取锁的查询次数，每立即获取锁一次该值加1。

• Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁该值加1），此值高则说明存在较严重的表级锁争用情况。

3. 总结

• MyISAM在执行查询语句（SELECT）前，会自动给设计的所有表加读锁；

• 在执行增删改操作前，会自动给设计的表加写锁加写锁。

---

• 对MyISAM表加读锁，不会阻塞其他进程对同一表（mylock）的读操作，但是会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作。

• 在加读锁并未释放锁时，该进程不能对同一表（mylock）进行写操作，并且也不能对其他表进行操作。

• 对MyISAM表加写锁，会阻塞其他进程对同一表（mylock）的读和写操作，只有当读锁释放后，才会执行其他进程的写操作。

• 在加写锁并未释放锁时，该进程不能对其他表进行操作。

---

简而言之：就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成长时间阻塞。