一、简介

类似于Java中同步代码块用到的锁，MySQL中为了保证数据一致性也使用锁，一般有

全局锁：每次操作锁住数据库所有的表，不能进行DML、DDL操作，事务都阻塞，
表级锁：每次操作锁住整张表，
行级锁：每次操作锁住对应的行数据

二、全局锁

1、使用全局锁

（1）

启动三个cmd窗口，第一个登录切换数据库后，做锁操作

image.png

（2）

第二个登录切换数据库后，执行查询可以，执行插入就卡住

image.png

（3）

第三个是cmd指令，执行备份数据库，不用进入mysql指令

image.png

（4）

之后就可以去d盘看有没有一个叫scott_copy.sql的文件

（5）

在第一个窗口中释放锁

image.png

（6）

再去第二个窗口看一下，发现刚才卡住的插入语句终于执行完了

image.png

2、不使用全局锁备份数据

使用全局锁有一些弊端

备份时不能办理业务，
从库备份时，不能执行主库传递过来的更新日志文件，会造成主从延迟

为了避免这个问题，当数据库引擎是InnoDB时，可以在备份时加上参数--single-transaction，保证不加锁也能完成一致性

image.png

三、表级锁

锁定粒度大，发生冲突概率最高，并发度最低，主要有

表锁
元数据锁
意向锁

1、表锁

（1）表共享读锁

①

为表加锁后，当前客户端能查询，插入报错

image.png

②

其他客户端也能查询，插入时会卡住

image.png

③

锁解除后，刚才卡住的插入语句也执行了

image.png

image.png

（2）表独占写锁

写锁把上边的read换成write就可以了，这里记下现象，加锁的客户端可以读可以插入，另一个客户端读、写都会卡住

2、元数据锁（又叫mdl）

元数据锁不用手动，访问表时系统自动加的，作用是维护元数据表的数据一致性，当表有活动事务时，不可以对元数据写入操作。
对表做DML操作时，加MDL读锁（共享），当对表做DDL时，加MDL写锁（排它）

sql类型                                                 锁类型                                  说明
lock tables read/write                shared_read_only/shared_no_read_write
select、select...lock in share mode                 shared_read              与shared_write兼容，与exclusive互斥
insert、update、delete、select...for update        shared_write            与shared_write兼容，与exclusive互斥  
alter table...                                       exclusive                            与其他mdl都互斥                   

（1）试验一：

两个窗口都开启事务，窗口一执行select，窗口二执行select和insert，都成功了，说明是互相兼容的

image.png

image.png

最后执行commit;即可。

（2）试验二：

窗口一开启事务，执行select，窗口二执行alter修改表结构，窗口一没提交会发现卡住，证明互斥了

image.png

image.png

当窗口一提交，窗口二才会向下执行。

可以通过select查询存在的锁的类型

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

（需要指出，mysql5.7中需要开启锁监控才能记录）

update performance_schema.setup_instruments set enabled = 'YES' where  name like '%lock%';

image.png

image.png

3、意向锁

（1）简介

为了避免在执行DML语句时，加的行锁与表锁有冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行是否加了行锁，使用意向锁降低表锁的检查，提高性能

image.png

假设一个窗口要把50部门的地点改为BENXI，会首先给该行数据加一个行级锁，再给dept表加一个意向锁，另一个窗口假设要来给dept表加一个表锁，此时若窗口一的意向锁和窗口二的表锁兼容，则可以加，不兼容，则加不上
意向锁分为两种：

意向共享锁（IS），由语句select、select...lock in share mode添加，与表锁共享锁（read）兼容，与表锁排它锁（write）互斥
意向排它锁（IX），由语句insert、update、delete、select...for update添加，与表锁共享锁（read）和表锁排它锁（write）都互斥，意向锁之间不互斥

使用如下语句检查意向锁

//mysql8以上
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

//mysql8以下
SELECT * FROM performance_schema.data_locks; -- 8.0

（2）试验

①试验一

窗口一开启事务并以锁模式查询，在查询语句后加上lock in share mode就会加上行锁和意向共享锁

image.png

窗口二查询表（意向）锁和行锁

image.png

②试验二

窗口一不动

image.png

窗口二给表加一个独占写锁，就会卡住，因为窗口一的锁没有释放

image.png

此时窗口一提交就好了

image.png

image.png

③试验三

窗口一执行DML语句

image.png

窗口二查看锁的类型

image.png

④试验四

窗口一不动

image.png

窗口二给表加一个共享读锁，就会卡住，因为窗口一的锁没有释放

image.png

image.png

image.png

最后在窗口二执行unlock tables;释放锁即可

四、行级锁

行级锁，每次执行锁住对应一行数据，锁定粒度最小，发生冲突概率最低，并发度最高，应用在InnoDB引擎中
InnoDB引擎中数据是基于索引存储的，行锁是通过对索引中的索引项加锁来实现的，而不是对记录加的锁，行级锁有以下三种

行锁，锁定单行记录，防止其他事务对该记录update和delete，在RC和RR隔离级别下都支持
间隙锁，锁定索引记录间的间隙（不含该记录），确保索引记录间隙不变防止其他事务在间隙执行insert，产生幻读，RR级别下支持
临键锁，行锁和间隙锁的结合，同时锁住数据和数据间的间隙Gap，RR级别支持

1、行锁

（1）简介

行锁有两种

共享锁（S）允许一个事务读一行，防止其它事务获取相同数据集的排它锁，即当前共享锁兼容其他请求共享锁，共享锁与排它锁互斥
排它锁（X）允许获取排它锁的事务更新数据，阻止其它事务获取相同数据集的共享锁和排它锁，即当前排它锁与其它请求共享锁和排它锁都互斥

不同语句锁的类型具体划分：

INSERT                                           排它                                  自动加锁
UPDATE                                           排它                                  自动加锁
DELETE                                           排它                                  自动加锁
SELECT                                        不加任何锁
SELECT...LOCK IN SHARE MODE                      共享                      需要手动在SELECT后加LOCK IN SHARE MODE
SELECT...FOR UPDATE                              排它                           需要手动在SELECT后加FOR UPDATE

（2）试验

①试验一

窗口一执行简单查询

image.png

窗口二查询锁状态

image.png

②试验二

窗口一加共享锁查询

image.png

窗口二查询锁状态

image.png

结论：可以发现加了一个共享锁

窗口二再加共享锁查询

image.png

结论：可以发现两个共享锁兼容

窗口二提交，再看锁状态

image.png

结论：可以发现只剩窗口一加的共享锁了

③试验三

窗口一不动，窗口二执行update语句

image.png

结论：会卡住，证明窗口一共享锁和窗口二排它锁互斥，当窗口一提交，窗口二的update才会执行下去

image.png

image.png

image.png

image.png

④试验四

窗口一执行update，没提交

image.png

窗口二也执行update，就会卡住

image.png

image.png

image.png

⑤试验五

窗口一以非索引字段dname为查询条件更新数据

image.png

窗口二更改另一条数据，两个窗口操作的是不同的数据，也被卡住了

image.png

InnoDB是针对索引加的锁，如果不通过索引条件检索记录，InnoDB会将表中所有记录加锁，此时就升级成表锁

image.png

image.png

image.png

image.png

2、间隙锁、临键锁

InnoDB在RR级别运行，使用next-key锁进行索引和搜索扫描，防止幻读

唯一索引上的等值查询，给不存在的记录加锁时，优化为间隙锁
普通索引上的等值查询，向右遍历时最后一个元素不满足查询条件，next-key lock退化为间隙锁
唯一索引上的范围查询，会访问到不满足条件的第一个元素为止

（2）试验

①试验一

deptno为主键，有唯一索引，55值不存在，就会在50和60之间加一个间隙锁（GAP）

image.png

窗口二查看锁

image.png

image.png

窗口一提交，窗口二的插入才能正常执行

image.png

image.png

②试验二

给dept加一个int型的age字段，加入如下值

image.png

窗口一中给该字段加一个普通索引

image.png

image.png

image.png

说明：
①给查询到的数据这一行加一个行锁，锁住该行
②给查询到的数据这一行加一个临键锁，锁住该行数据，锁住该行和上行数据间的间隙
③给查询到的数据这一行和下一行之间间隙加一个间隙锁，锁住这个间隙

③试验三

窗口一非等值比较

image.png

image.png

说明：
①给40这条数据加一个行锁
②给60这条数据以及它与上一条数据之间的间隙加一个临键锁
③supremum pseudo-record正无穷，给正无穷以及正无穷到60这条数据之间的间隙加一个临键锁
④分别给50以及与它上一条数据的间隙、58以及与它上一条数据的间隙各加一个临键锁

总结：间隙锁唯一目的是防止其它事务插入间隙，间隙锁可以共存，一个事务的间隙锁不会组织另一个事务在同一个间隙上采用间隙锁