MySQL锁

一：概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，如何保证数据的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
在MySQL中的锁，按照锁的粒度来分，可以分为以下三类：
1.全局锁：锁定数据库中的所有表
2.表级锁：每次操作锁住整张表
3.行级锁：每次操作锁住对应的行数据
以下实例都是基于此表进行，表可能比较简单。

create table user(
	id int auto_increment primary key,
    	name varchar(20)
);
insert into user(id,name)values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
insert into user(id,name)values(4,'bier'),(5,'helo'),(6,'mofi');

二：锁

1.全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例只能处于只读状态，其他语句都将被阻塞。典型的应用场景是做全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据完整。
基本语法

#获取全局读锁
flush tables with read lock;
 
#解锁
unlock tables;

打开两个服务窗口，建立连接，在一个 MySQL会话中执行 flush tables 命令。获得全局读锁。

flush tables with read lock;

在另一个MySQL会话中进行输入。

lock tables user write;

你会发现光标一直在闪烁，说明此时这条指令被阻塞了，我们在第一个会话中执行 show processlist 查看线程状态

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 7
   User: root
   Host: localhost:52492
     db: mysq
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 10
   User: root
   Host: localhost:52642
     db: mysq
Command: Query
   Time: 112
  State: Waiting for global read lock
   Info: lock tables user write
2 rows in set (0.00 sec)

会发现线程 10 的状态为 Waiting for global read lock，需要我们在第一个会话中输入解锁指令，才会获取到锁。
进行备份

#注意：mysqldump是Mysql提供的一种工具，无法在会话中直接执行，回退到cmd界面执行，会在D盘中生成user.sql文件
mysqldump -uroot -ppassword user > D:\user.sql;

以上的备份存在着一些问题，如果使用全局锁：
1.如果在主库上备份，那么备份期间都不能执行更新，业务基本上就得停摆。
2.如果在从库上备份，那么备份期间从库不能执行主库同步过来的二进制文件(binlog),会导致主从延迟。
解决方法：

#在使用备份语句时，加上参数 --single-transaction 来完成不加锁的一致性数据备份
 
mysqldump --single-transaction -uroot -ppassword user > D:\user.sql;

2.表级锁

表级锁，每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度低。应用在MyISAM、InnoDB、BDB等存储引擎中。
表级锁，主要分为以下三类：
1.表锁
2.元数据锁(meta data lock MDL)
3.意向锁

2.1 表锁

对于表锁，又可以分为 表共享读锁(read lock)和 表独占写锁(write lock)。
注意：读锁不会阻塞其他客户端的读操作，但是会阻塞写操作。写锁即会阻塞其他客户端的读操作，也会阻塞其他客户端的写操作。
表共享读锁(read lock)
使用了表共享读锁，多个客户端可以进行读操作，但是都不能进行写操作
语法

#加锁
lock table user read;
#释放锁
unlock tables;

在会话输入以下命令，给表加共享读锁。

lock table user read;

数入查询语句进行读操作，

mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | bier     |
|  5 | helo     |
|  6 | mofi     |
+----+----------+
6 rows in set (0.00 sec)

发现可以正常读取数据，那么我们再输入修改语句进行写操作，会发现光标再闪烁，查询会挂起

mysql> update user set name = 'xixi' where id = 6;

表独占写锁(write lock)
使用表独占写锁，拥有锁的客户端能进行读操作和写操作，其他客户端即不能进行写操作也不能进行读操作。
语法

#加锁
lock table user write;
#释放锁
unlock tables;

在会话窗口输入以下命令给表加表独占写锁

lock table user write;

在当前会话进行查找和修改操作，发现可以查询出来

mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | bier     |
|  5 | helo     |
|  6 | xixi     |
+----+----------+
6 rows in set (0.00 sec)
 
mysql> update user set name = 'mofi' where id =6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

那么我们在另一个会话窗口进行查询和修改操作，发现查询被阻塞。

mysql> select * from user;

2.2 元数据锁(meta data lock MDL)

元数据实际上指的就是表结构，MDL加载过程是系统自动控制的，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是管理数据库对象的并发问题和确保元数据的一致性，在表上有活动事务的时候，不可以对元数据进行写操作。为了避免DML于DDL冲突，保证读写的正确性。
在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加DML读锁(共享)，当对表的结构进行变更操作时，加DML写锁(排他)。

在一个会话窗口中开启事务并进行查找

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | bier     |
|  5 | helo     |
|  6 | mofi     |
+----+----------+
6 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

在另一个会话窗口开启事务并进行修改操作

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update user set name='xixi' where id=6;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

发现两个窗口都是没有发生阻塞现象的，这是因为修改和查找产生的锁类型是兼容的。
那么此时我们在一个窗口中开启事务并进行查询操作

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | bier     |
|  5 | helo     |
|  6 | xixi     |
+----+----------+
6 rows in set (0.00 sec)

在另一个会话窗口开启事务并增加一个字段 java

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table user add column java int;

发现出现了阻塞现象，这是因为有未提交的事务， alter 操作产生的排它锁和 select操作产生的共享读锁互斥。

2.3 意向锁

为了避免在DML执行的时候，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行的数据是否加索，使用意向锁来减少表锁的检查。

意向锁可以分为意向共享锁(Is) 和 意向排他锁(Ix)
意向共享锁(IS)：由语句 select...lock in share mode 添加
与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。
意向排他锁(IX)：由 insert、update、delete、select...for update添加
与表锁共享锁(read)及表锁排他锁(write)都互斥，意向锁之间不会排斥。
在会话窗口开启事务并进行查询，会添加意向共享锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from user lock in share mode;
+----+----------+------+
| id | name     | java |
+----+----------+------+
|  1 | zhangsan | NULL |
|  2 | lisi     | NULL |
|  3 | wangwu   | NULL |
|  4 | bier     | NULL |
|  5 | helo     | NULL |
|  6 | xixi     | NULL |
+----+----------+------+
6 rows in set (0.00 sec)

此时在另一个窗口开启事务并创建排它锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> lock tables user write;
 

请求被挂起，发生了阻塞现象，说明了意向共享锁与排它锁互斥。

3.行锁

行级锁，每次操作锁住对应的行数据，锁粒度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDB引擎中。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加索来实现的，而不是对记录加索。
对于行级锁，主要分为以下三类：
1.行锁(Record Lock)
2.间隙锁(Gap Lock)
3.临键锁(Next-key Lock)

3.1 行锁(Record Lock)

顾名思义，行锁是锁定单个行记录的锁，防止其他事务对此进行update 和 delete，在RC、RR隔离级别下支持。

行锁又分为共享锁(S) 和 排它锁(X)
共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
1.针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2.InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时会升级为表锁。

3.2间隙锁(Gap Lock)

锁住索引记录间隙，确保索引记录间隙不变，防止其他事务在这个间隙进行insert,产生幻读，在RR隔离级别下支持。间隙锁唯一目的就是防止其他事务插入间隙，造成幻读现象，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行,InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。
1.索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁。
2.索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
3.索引上的范围查询(唯一查询)，会访问到不满足条件的第一个值为止。

3.3临键锁(Next-key Lock)

行锁和间隙锁的组合，同时锁住数据，并锁住数据前面的间隙Gap,在RR隔离级别下支持。