
create database if not exists bilibili; #创建数据库bilibili
use bilibili; #选择bilibili数据库，即可进行下面的代码演示
drop database if exists bilibili; #代码演示结束后删除bilibili数据库。这个小文件全部代码演示完再删
show databases;  #查看是否删除完成

-- 准备数据
create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null,'黛绮丝','2022567890'),(null,'谢逊','2022567891'),(null,'殷天正','2022567892'),(null,'韦一笑','2022567893');

create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values (null,'Java'),(null,'PHP'),(null,'MySQL'),(null,'Hadoop');

create table student_course( #学生表和课程表的中间表
    id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,3,1),(null,4,4);

create table score(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '分数表';
insert into score values (null,'Tom',67,88,95),(null,'Rose',23,66,90),(null,'Jack',56,90,76);

create table stu(
    id int primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄'
) comment '简单学生表';
insert into stu values (1,'tom',1),(3,'cat',3),(8,'rose',8),(11,'jetty',11),(19,'lily',19),(25,'luci',25);


# 此时在server窗口执行更新语句，就可以正常执行
update student set name = '张三' where id = 2;

-- -------------------------------------------------表级锁 表锁-----------------------------------------------------

/*介绍
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中

表级锁的分类
1. 表锁
2. 元数据锁(meta data lock，简称MDL)
3. 意向锁

这节课我们讲表锁，对于表锁，可分为两类
1. 表共享读锁(read lock)，简称读锁
2. 表独占写锁(write lock)，简称写锁

表锁的语法如下
1. 加锁
2. 释放锁(直接断开该客户端会间接释放锁)

总结:
1. 读锁不会阻塞当前客户端和其他客户端的读，但是会阻塞当前客户端和其他客户端的写
2. 写锁既会阻塞其他客户端的读，也会阻塞其他客户端的写，不会阻塞当前客户端的读和写*/

#这节课需要使用终端或命令行。开两个会话，模拟给表加锁之后，其他会话能不能对这个表进行增删改查
#为了下面描述方便，两个命令行窗口分别叫server端、client端

# mysql -h192.168.127.138 -uroot -p
# use bilibili;

# 【读锁演示如下】

# 在server端对score表进行加锁，加的是读锁

# 此时server端和client端对score表，能进行'读'操作
select * from score;

# 此时server端和client端对score表，不能进行'写'操作
update score set math = 100 where id = 2;

# 在server端释放读锁，即关闭读锁

# 【写锁演示如下】

# 在server端对score表进行加锁，加的是写锁

# 此时server端对score表，能进行'读'和'写'操作
select * from score;
update score set math = 100 where id = 3;

# 此时client端对score表，不能进行'读'和'写'操作
select * from score;
update score set math = 100 where id = 1;

# 在server端释放读锁，即关闭读锁

-- ------------------------------------------------表级锁 元数据锁---------------------------------------------------

/*元数据锁(简称MDL)
MDL加锁过程是系统自动控制的，无需显式使用，在访问一张表的时候，元数据锁会自动加上。MDL锁主要作用是
维护表元数据的数据一致性。在表上有活动事务的时候，不可以对元数据进行写入操作

元数据锁的作用: 为了避免DDL与DML冲突，保证读写的正确性
DDL语句的作用: 对数据库对象(数据库、表、列、索引等)进行创建、删除、修改
DML语句的作用: 用于添加、修改、删除和查询数据库记录，并检查数据完整性

什么是元数据
简单理解为就是表结构，当某张表存在未提交的事务，那么我们不能修改这张表的表结构

在MySQL5.5中引入了MDL
当对一张表进行增删改查的时候，会自动加MDL读锁，读锁之间可以兼容
当对表结构进行变更操作的时候，会自动加MDL写锁，写锁之间不可以兼容，写锁与读锁之间也不可以兼容
如下表格

对应SQL                                        锁类型                                  说明
select、select...lock in share mode           SHARED_READ                             与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert、update、delete、select...for update    SHARED_WRITE                            与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table ...                               EXCLUSIVE                               与其他的MDL都互斥

主要看上面表格的下三行。上面的SHARED_READ是读锁，SHARED_WRITE是写锁，EXCLUSIVE是排他锁。其中读锁和写锁是共享锁

如何查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;*/

#这节课需要使用终端或命令行。开两个会话，模拟给表加锁之后，其他会话能不能对这个表进行增删改查
#为了下面描述方便，两个命令行窗口分别叫server端、client端

# mysql -h192.168.127.138 -uroot -p
# use bilibili;

# 【共享锁+共享锁演示如下】
# 在server端开启一个事务，并执行一个查询操作
begin;
select * from score;
# 在client端开启一个事务，并执行一个查询操作，并执行一个更新操作
begin;
select * from score;
update score set math = 60 where id = 1;
# 总结:在事务中执行select语句(会加上元数据锁中的SHARED_READ共享锁)、update语句时(会加上元数据锁中的SHARED_WRITE共享锁)，由于共享锁之间是兼容的，所以server端和client端不报错不堵塞
# 演示之后我们在server端和client端执行提交事务
commit;

# 【共享锁+排他锁演示如下】
# 在server端开启一个事务，并执行一个查询操作
begin;
select * from score;
# 在client端执行修改表结构的操作，例如新增一列(也叫字段)，字段名为java
alter table score add column java int;#发现这行被堵塞了，执行不下去
# 总结:在事务中执行select语句(会加上元数据锁中的SHARED_READ共享锁)、alter语句时(会加上元数据锁中的EXCLUSIVE排他锁)，由于共享锁与排他锁之间不兼容，所以server端执行之后，client端执行会堵塞
# 当server窗口的事务提交之后，client窗口的语句才会正常执行。在server窗口执行下面的提交语句，观察client窗口
commit;

# 【如何查看当前数据库的表当中涉及到的元数据锁】
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

# 【有趣验证】
# 在client端执行查看元数据锁语句
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
# 此时的结果是输出一张表，有多个字段，其中object_schema表示对应的数据库名、object_name表示对应的表名、lock_type表示是元数据锁的哪种锁
# 然后在server端执行开启事务，并且执行一条查询语句
begin;
select * from score;
# 此时去client端执行查看元数据锁语句，会发现多一条元数据锁记录，因为我们刚刚在server端执行了事务里面的查询语句
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
# 最后在client端开启一个事务，并且执行一条更新语句
begin;
update score set math = 10 where id = 1;
# 此时在client端执行查看元数据锁语句，会发现又多一条元数据锁记录，因为我们刚刚在client端执行了事务里面的查询语句
# 到这里这节课就演示完了，我们在server端和client端执行提交事务
commit;

-- ------------------------------------------------表级锁 意向锁----------------------------------------------------

/*意向锁
作用是为了避免DML语句在执行时，加的行锁与表锁的冲突。在InnoDB引擎中引入了意向锁，使得表锁不用检查每行数
据是否加锁，使用意向锁来减少表锁的检查，从而提高性能

DDL语句的作用: 对数据库对象(数据库、表、列、索引等)进行创建、删除、修改
DML语句的作用: 用于添加、修改、删除和查询数据库记录，并检查数据完整性

意向锁分为两种
1. 意向共享锁(简称IS): 由语句select...lock in share mode添加
2. 意向排他锁(简称IX): 由语句insert、update、delete、select...for update添加
当我们使用对应语句的时候，就会自动加上对应的意向锁，意向锁就是分上面的两种

意向锁(这节课)与表锁(上节课)的兼容性，如下
1. 意向共享锁: 与表锁共享锁兼容，与表锁排他锁互斥
2. 意向排他锁: 与表锁共享锁、排他锁都互斥。意向锁之间不互斥

如何查看意向锁，以及查看行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;*/

#这节课需要使用终端或命令行。开两个会话，模拟给表加锁之后，其他会话能不能对这个表进行增删改查
#为了下面描述方便，两个命令行窗口分别叫server端、client端

# mysql -h192.168.127.138 -uroot -p
# use bilibili;

# 【意向共享锁+读锁演示如下】

#在server端开启事务，并执行一条带意向共享锁的查询语句，最后查看行锁的加锁情况
begin;#注意不管是之前的课还是现在的课，begin开启事务这行是不会产生锁的，只是简单的开启事务，产生锁是因为在事务里面执行了特定的语句
select * from score where id = 1;#这样写是不会加上锁的，应该写下面那行
select * from score where id = 1 lock in share mode;#加的是行锁中的意向共享锁
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
# 上面那行的返回结果是一张表，其中lock_type表示锁的类型(RECORD是行锁，TABLE是表锁)，lock_mode表示锁模式(IS是意向共享锁，IX是意向排他锁)

#在client端给score表加上读锁
lock tables score read;#发现此时可以加锁成功。原因score表有我们加的意向共享锁，此时再加上读锁，这两个锁是兼容的，所以不会报错
#演示结束后，在client端释放锁。由于client只有读锁，所以释放的是client端的score表的读锁。score的意向共享锁是server端添加的，所以不会被client端释放

# 【意向共享锁+写锁演示如下】

#在client端给score表加上写锁
lock tables score write;#发现此时被堵塞，执行不下去。原因score表有我们加的意向共享锁，此时再加上写锁，这两个锁是互斥的，所以后者会被阻塞
#只有当server端的事务提交之后，client的写锁才会正常执行。在server端提交事务，如下
commit;#提交事务之后，server端的score表的锁会被全部释放
#演示结束后，在client端释放锁

# 【意向排他锁+读锁演示如下】

#在server端开启事务，执行一个update语句，当执行完update之后会自动为score表的id为1的这行加上行锁，与此同时会自动为score表加上意向排他锁
begin;
update score set math = 99 where id = 1;
#在server端查看score表的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

#在client端给score表加上读锁
lock tables score read;#发现此时被堵塞，执行不下去。原因score表有我们加的意向排他锁，此时再加上读锁，这两个锁是互斥的，所以后者会被阻塞
#只有当server端的事务提交之后，client的读锁才会正常执行。在server端提交事务，如下
commit;#提交事务之后，server端的score表的锁会被全部释放。在哪个会话提交事务，哪个会话的锁就会被释放，不影响其他会话
#演示结束后，在client端释放锁

# 【意向排他锁+写锁演示如下】
# 结论是写锁会被阻塞，具体的代码演示跟上面的【意向排他锁+读锁】一模一样，只需要把read改为write即可

-- ------------------------------------------------行级锁 概述------------------------------------------------------


/*介绍
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。
在InnoDB引擎的数据是基于索引组织的(也就是基于聚集索引来存储的，锁的是聚集索引B+树底部节点的某一个值)，
行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。
对于行级锁，主要分为以下三类:

什么是幻读: 前面学过这个。一个事务在查询时发现没有对应的数据行，但插入时却发现这行数据已经存在，事务就感觉自己眼花幻象了
什么是间隙: 在B+树的底部叶子节点中，数据和数据之间的间隙，以及叶子和叶子之间的间隙
行锁锁的是什么: 在B+树的底部叶子节点中，一个叶子可能有多个数据(key)，行锁锁的是单独一个数据(key)，一个key里面存放的就是表的一整行数据

1. 行锁(Record Lock)也叫记录锁: 锁定个行记录的锁，防止其他事务对此行进行update和delete操作，
在RC、RR隔离级别下都支持
2. 间隙锁(Gap Lock): 锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，也就是避免产生幻读
在RR隔离级别下都支持
3. 临键锁(Next-Key Lock): 行锁和间隙锁的组合，同时锁住数据和数据前面的间隙
在RR隔离级别下都支持

RR隔离级别: repeatable read事务隔离级别
RC隔离级别: read commited事务隔离级别
我们在很前面就学过的MySQL的四种隔离级别，可回去看看*/

-- ------------------------------------------------行级锁 行锁------------------------------------------------------

/*行锁。Innno引擎实现了以下两种类型的行锁:
1. 共享锁(S): 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
简单说就是共享锁之间兼容，共享锁与排他锁互斥
2. 排他锁(X): 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
简单说就是如果第一个事务获取到某行数据的排他锁，那么后续其他事务就不可以再获取这行数据的
共享锁及排他锁


当我们执行对应的SQL语句，就会产生对应的锁，可以是自动加的锁，也可以是手动加的锁。如下

SQL语句                        行锁类型          说明
insert                         排他锁           自动加锁
update                         排他锁           自动加锁
select                         不加任何锁        无
select...lock in share mode    共享锁           需要手动在select之后在lock in share mode
select...for update            排他锁           需要手动在select之后加for update

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

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

我们用代码演示上面的两种行锁，见对于sql文件*/

#这节课需要使用终端或命令行。开两个会话，模拟给表加锁之后，其他会话能不能对这个表进行增删改查
#为了下面描述方便，两个命令行窗口分别叫server端、client端

# mysql -h192.168.127.138 -uroot -p
# use bilibili;

# 【共享锁+共享锁演示如下】

# 在server端开启事务，并执行一条查询语句，最后查看行锁的加锁情况
begin;
select * from stu where id = 1;#简单的select语句并不会使id为1的那行加上行锁
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

# 在server端执行一条可以加上共享锁的语句，并查看行锁的加锁情况
begin;
select * from stu where id = 1 lock in share mode;
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
# 会发现上面那行返回的结果是一张表，在lock_type字段有一个RECOED(行锁)，lock_mode字段有S(行锁中的共享锁)和REC_NOT_GAP(没有间隙)

# 在client端开启事务，并执行一条可以加上共享锁的语句，并查看行锁的加锁情况
begin;
select * from stu where id = 1 lock in share mode;#不会堵塞，可以正常执行。原因:id为1的行在上面加了行锁，现在又加了行锁，行锁之间兼容
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
# 演示结束后，在client端释放锁。由于client只有共享锁，所以释放的是client端的stu表的共享锁。client端不会释放server端的锁(自己只能释放自己会话的锁)
commit;

# 【共享锁+排他锁演示如下】

# 在client端执行一条可以加上排他锁的语句
update stu set name = 'Java' where id = 3;
# 上面那行不阻塞，原因:server端是给id为1的加了共享锁，id为3的行是还没有锁的，这里只在id为3的行加了排他锁，此时id为3的行只有一个锁，所以正常执行不堵塞
update stu set name = 'Java' where id = 1;#堵塞，不能往下执行。原因: id为1的行已经在server端加了共享锁，这里又加排他锁，这两个锁不兼容
#只有当server端的事务提交之后，client的排他锁才会正常执行。在server端提交事务，如下
commit;#提交事务
#演示结束后，在client端提交事务，此时client端口的锁就会被全部释放。在哪个会话提交事务，哪个会话的锁就会被释放，不影响其他会话
commit;

# 【排他锁+排他锁演示如下】

# 在server端开启事务，执行一条可以加上排他锁的语句
begin;
update stu set name = 'hello' where id = 1;

# 在client端开启事务，执行一条可以加上排他锁的语句
begin;
update stu set name = 'world' where id = 1;#堵塞，不能往下执行。原因:id为1的这行在上面加了排他锁，这里又加上排他锁，互斥，这两个锁不兼容
#只有当server端的事务提交之后，client的排他锁才会正常执行。在server端提交事务，如下
commit;#提交事务
#演示结束后，在client端提交事务，此时client端口的锁就会被全部释放。在哪个会话提交事务，哪个会话的锁就会被释放，不影响其他会话
commit;

#【演示'InnoDB的行锁升级为表锁'，如下】

# 在server端开启事务，执行一条更新语句
begin;
update stu set name = 'haha' where name = 'lily';#看似是加的行锁

# 在client端开启事务，执行一条更新语句
begin;
update stu set name = 'hehe' where id = 1;#结果是被堵塞，原因:上面在server端进行更新name的时候，表中的name字段没有设置索引，
# 此时就会对所有记录(表中的每一行数据)加锁，从而由行锁升级为表锁
# 解决:给stu表的name字段创建一个索引。在server端输入如下
commit;#由于client还被阻塞中，所以先在server端提交一下事务，使client端恢复正常
create index idx_stu_name on stu(name);

# name字段有索引之后，再次执行上面两部分的操作，就不会阻塞了。如下
# 在server端开启事务，执行一条更新语句
begin;
update stu set name = 'good' where name = 'haha';
# 在client端开启事务，执行一条更新语句
begin;
update stu set name = 'good' where id = 1;#不会堵塞，正常执行
#演示完之后，在server端和client端都提交事务，不要影响后续代码演示
commit;

-- ------------------------------------------------行级锁 间隙锁&临键锁------------------------------------------------------

/*1. 间隙锁(Gap Lock): 锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，也就是避免产生幻读
在RR隔离级别下都支持
2. 临键锁(Next-Key Lock): 行锁和间隙锁的组合，同时锁住数据和数据前面的间隙
在RR隔离级别下都支持

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

我们会在代码演示那里主要演示上面三种情况

注意:间隙锁唯一目的是防止其他事务插入间隙。
间隙锁可以共存(兼容)，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

如何查看意向锁及行锁的加锁情况，如下
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;*/

#这节课需要使用终端或命令行。开两个会话，模拟给表加锁之后，其他会话能不能对这个表进行增删改查
#为了下面描述方便，两个命令行窗口分别叫server端、client端

# mysql -h192.168.127.138 -uroot -p
# use bilibili;

# 查看stu表的数据
select * from stu;

#【间隙锁演示如下】 + 【第一种情况: 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁】

# 在server端开启事务，执行一条更新语句，
begin;
update stu set age = 10 where id = 5;#由于没有id为5的数据，但是这行又支持成功了，那么加的是什么锁呢
# 上面那行是把5旁边的两个数据的间隙加了一个间隙锁。也就是id=3 和 id=8 之间加了一个间隙锁。如下可以查看一下锁
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
# 上面查看的结果是一张表。其中lock_type字段是锁类型为RECORD。lock_mode字段是锁模式(X表示排他锁，GAP表示间隙锁)。lock_data字段为8表示 id为8 与 id为3 之间加的间隙锁
# 注意间隙锁锁的不是id为8 和 id为3的行数据，而是锁的是id为8 和 id为3之间的间隙，不包含8和3

# 在client端开启事务，执行一条插入语句
begin;
insert into stu values(7,'Ruby',7);#被堵塞，不能往下执行。原因:上面的server端在id为3~8之间生成了间隙锁，这里不能往该间隙插入数据
#只有当server端的事务提交之后，也就是释放了间隙锁，client端的插入语句才会正常执行。在server端提交事务，如下
commit;#提交事务
#演示完之后，在client端也提交事务，不要影响后续代码演示
commit;

#【临键锁演示如下】 + 【第二种情况: 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁】

#由于要模拟普通索引(非唯一索引)所以我们先对stu表的age字段建一个普通索引。在server端输入如下
create index idx_stu_age on stu(age);

# 在server端开启事务，根据age字段执行一条等值查询的语句
begin;
# select * from stu where age = 3;#这条语句是不会加任何锁的，我们要使用下面那行加共享锁的语句
select * from stu where age = 3 lock in share mode;
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;#查看加锁情况
# 总结: 把'age=3'与'前面一个数据'之间的间隙锁住，把'age=3'的这一行给数据锁住，把'age=3'与'后面一个数据'之间的间隙锁住
#演示完之后，把server端的事务提交一下，不要影响后续代码演示
commit;

#【临键锁演示如下】 + 【第三种情况: 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止】

# 在server端开启事务，执行一条范围查询的语句
select * from stu where id >= 19 lock in share mode;#要写lock in share mode，不然是不加锁的
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;#查看加锁情况
# 总结: 把'id=19'的这一行给数据锁住，把'id=19'与'后面一个数据'之间的间隙锁住，把'id=19'的'后面一个数据'与'正无穷'之间的间隙锁住
# 也就是锁的是: id=19、id=19~25、id=25~正无穷大
#演示完之后，把server端的事务提交一下。
commit;
