MySQL表锁和行锁演示

Mysql中的锁按锁粒度从大到小分类：表锁，页锁和行锁；以及特殊场景下使用的全局锁，重点掌握表锁和行锁就可以了，今天我们就来简单聊聊这几种锁。

一、表锁

1、表锁的特点：

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

2、案例分析：

1）读锁演示

1、建表：

create table mylock(
 id int not null primary key auto_increment,
 name varchar(20)
)engine myisam;
 
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');
 
select * from mylock;

2、新建两个session窗口：session_1、session_2；

3、在session_1窗口中给mylock表添加读锁：

（注意：read锁可以被添加多个，比方说：此时还可以用相同的命令给session_2添加mylock表的读锁，此时上图中的In_user就变成了2）

4、使用session_1对其它表进行读操作：

操作失败，必须先释放mylock表的读锁，主要是为了避免死锁。

5、使用session_2对其它表进行读操作：

6、使用session_1对mylock表进行读、写操作：

7、使用session_2对mylock表进行读、写操作：

8、session_1释放mylock表的读锁：

此时，被阻塞的session_2窗口将修改成功：

2）写锁演示

1、session_1给mylock表添加写锁：

2、session_1对mylock表进行读、写操作：

3、session_2对mylock表进行读操作：

（读都被阻塞的话，就更不用说写了）

注意：如果持有表锁的session异常终止的话（比如说执行了“ctrl+z”），那么该session是不会主动释放锁的，这时候我们可以重启mysql服务器，但这不是一个值得的办法，其实我们可以在任意一个session会话中通过show processlist命令来查看线程Id，再通过kill 【线程Id】来关闭对应的会话。

3、总结：

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

结论：
结合上表，所以对MyISAM表进行操作，会有以下情况：
1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞。

---

二、行锁

1、特点：

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

2、案例分析：

1. 建表建检索：

create table mylock2 (a int(11),b varchar(16))engine=innodb;

insert into mylock2 values(1,'b2');
insert into mylock2 values(3,'3');
insert into mylock2 values(4,'4000');
insert into mylock2 values(5,'5000');
insert into mylock2 values(6,'6000');
insert into mylock2 values(7,'7000');
insert into mylock2 values(8,'8000');
insert into mylock2 values(9,'9000');
insert into mylock2 values(1,'b1');

create index test_innodb_a_ind on mylock2(a);

create index test_innodb_lock_b_ind on mylock2(b);

select * from mylock2;
## 结果：
## +------+------+
## | a    | b    |
## +------+------+
## |    1 | b2   |
## |    3 | 3    |
## |    4 | 4000 |
## |    5 | 5000 |
## |    6 | 6000 |
## |    7 | 7000 |
## |    8 | 8000 |
## |    9 | 9000 |
## |    1 | b1   |
## +------+------+
## 9 rows in set (0.00 sec)

为什么这里需要创建索引？
这里我们就需要知道InnoDB引擎行锁的特性了：

1. 在不通过索引条件查询的时候，InnoDB使用的确实是表锁！
2. 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键,是会出现锁冲突的。
3. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
4. 即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁,而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（explain查看），以确认是否真正使用了索引。

2. 创建两个session窗口session_1和session_2，并设置不自动提交：set autocommit = 0;

3. session_1进行一条记录进行写操作（未提交）
   

4. session_2对mylock2表进行一些读写操作
   

5. 无索引或索引失效都会导致行锁升级未表锁：
   
   

3、行锁分析：

通过命令：show status like 'innodb_row_lock%';可以来分析系统上的行锁的争夺情况；

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
最后可以通过
SELECT * FROM information_schema.INNODB_TRX\G;
来查询正在被锁阻塞的sql语句。

4、总结：

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

三、页锁

除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

使用页级锁定的主要是BerkeleyDB存储引擎。

四、全局锁

首先全局锁，是对整个数据库实例加锁。使用场景一般在全库逻辑备份时。

MySQL提供加全局读锁的命令：

Flush tables with read lock (FTWRL)（客户端发生异常断开，那么MySQL会自动释放这个全局锁）

set global readonly=true（客户端发生异常断开，数据库依旧会保持readonly状态，会导致整个库长时间处于不可写状态，试想一下微信只能看，不能打字）

这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等修改数据库的操作都会被阻塞。

风险：

1. 如果在主库备份，在备份期间不能更新，业务停摆；
2. 如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟同步。

参考文章：
面试让HR都能听懂的MySQL锁机制，欢声笑语中搞懂MySQL锁