锁是为了保证数据库中的数据一致性，使各种数据一致性 共享资源在接受采访时有序设计规则。

MySQL不同的存储引擎支持不同的锁定机制，InnoDB支持行锁，有时会升级到表锁，Myisam只支持表锁。

• 表锁：表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表时，必须等前一个事务释放了锁才能进行对表进行访问。

表锁的特点： 成本小，锁快，不会出现死锁。锁粒度大，锁冲突概率小，并发度相对较低

• 行锁：锁是指锁定表的一行或多行记录。当其他事务访问同一表时，只要锁定的记录不能访问，其他记录就可以正常访问。

锁的特点： 成本大，锁慢，会出现死锁。锁粒度小，锁冲突概率大，并发性高。

今天我们主要讲锁InnoDB引擎来讲，因为它支持行锁和表锁。

基于锁的属性分类：

• 共享锁
• 排他锁

基于锁的粒度分类：

• 行级锁（InnoDB）
• 表级锁（InnoDB和Myisam）
• 页级锁（BDB引擎）
• 记录锁
• 间隙锁
• 临键锁
• MDL锁
• 全局锁

基于锁的状态分类：

• 意向共享锁
• 意向排它锁

读锁（共享锁，shared lock）简称S锁，一个事务获得了数据行的读锁，其他事务也可以获得相应的读锁，但是你得不到写锁，也就是说，当一个事务读取一个数据行时，其他事务也可以读取，但不能添加或删除数据行。

注意： 读锁是共享锁，可同时持有多个事务。当共享锁持有一个或多个事务时，锁定的数据无法修改。

简而言之：可以读多个事务，但只能写一个事务。

读锁是通过select... lock in share mode；句子在被读取的行记录或行记录的范围内增加一个读锁，以便其他事务可以读取，但如果你想申请加写锁，它将被堵塞。

事务一：

begin; SELECT * FROM demo_info where id=1 lock in share mode; 

事务二：

begin; UPDATE demo_info set key1='aa' where id=1 ; 

卡住了，说明程序被阻塞，确实加了锁。

s锁是可以被多个事务同时获取的，我们在两个不同的事务中分别对同一行数据加上s锁，结果都可以成功，如下图：

写锁（排它锁或者独占锁，exclusive）简称X锁，一个事务获取了一个数据行的写锁，即可以读该行的记录，又可以修改该行的记录。但其他事务就不能在获取该行的其他任何锁。包括S锁，直到当前事务将锁释放。这保证了其他事务在当前事务释放锁之前不能再修改数据。

注意： 写锁是独占锁，只有一个事务可以持有，当这个事务持有写锁时，被锁的数据就不能被其他事务修改。

1. 一些DML语句的操作都会对行记录加写锁。

事务一：

begin;
UPDATE demo_info set key1='aa' where id=1 ;

事务二：

begin;
UPDATE demo_info set key1='bb' where id=1 ;

卡住了，说明程序在加锁，确实加了锁，但是我们发现，其他事务还能读，有点不符合逻辑，这是因为MySQL实现了MVCC模型，后边会详细介绍。

2. 比较特殊的就是 select ... for update;它会对读取的行记录加一个写锁，那么其他任何事务就不能对被锁定的行上加任何锁，要不然会被阻塞。

事务一：

begin;
SELECT * FROM demo_info where id=1 for update;

事务二：

begin;
UPDATE demo_info set key1='bb' where id=1 ;

卡住了，说明加了锁了。

3. X锁是只能被一个事务获取，我们在两个的不同事务中分别对同一行数据加上X锁，发现后者会被阻塞，如下图。

记录锁也属于行锁的一种，只有InnoDB才支持，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。

精准条件命中，并且命中的条件字段是唯一索引。

加了记录锁之后，数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

我们使用以下四个案例来验证记录锁的存在：

1. 两个事务修改同一行记录，该场景下，where条件中的列不加索引。

事务一：

begin;
UPDATE demo_info set key1='aa' where common_field='1' ;

事务二：

begin;
UPDATE demo_info set key1='bb' where common_field='1' ;

发现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，这一行数据被锁住了。

2. 两个事务修改同表不同行记录，此时where条件也不加索引。

事务一：

begin;
UPDATE demo_info set key1='aa' where common_field='1' ;

事务二：

begin;
UPDATE demo_info set key1='bb' where common_field='2' ;

发现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显。表被锁住了。

3. 两个事务修改同一行记录，where条件加上索引

事务一：

begin;
UPDATE demo_info set key1='aa' where common_field='1' ;

事务二：

begin;
UPDATE demo_info set key1='bb' where common_field='1' ;

发现事务二卡住了，只有事务一提交，事务二才能继续执行。很明显，这一行数据被锁住了。

4. 两个事务修改同表不同行记录，此时where条件加索。

事务一：

begin;
UPDATE demo_info set key1='aa' where common_field='1' ;

事务二：

begin;
UPDATE demo_info set key1='bb' where common_field='2' ;

发现都可以顺利修改，说明锁的的确是行。

综上所述：

• 行锁是加在索引上的，这是标准的行级锁。
• 如果where条件没有索引，那么行锁会升级为表锁。

间隙锁属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙，则会形成一个区间，遵循左开有闭原则。

范围查询并且查询未命中记录，查询条件必须命中索引，间隙锁只会出现在REPEATABLE_READ（重复读）的事务级别中。

间隙锁帮我们解决了mysql在RR级别下的一部分幻读问题，间隙锁锁定的是记录范围，不包含记录本身，也就是不允许在某个范围内插入或者删除数据。

间隙锁生成的条件：

1. A事务使用where进行范围检索时未提交事务，此时B事务向A满足检索条件的范围插入或者删除数据。
2. where条件必须有索引。

事务一：

begin;
select * from student where id between 7 and 15 lock in share mode;

事务二：

insert into student values (12,'tom',66,'d');

发现卡住了，第一个事务会将id在7到15之间的数据全部锁定，不允许在缝隙间插入。

事务三：

insert into student values (18,'tom',66,'d');

插入一个id为18的数据，竟然成功了，因为18不在事务一的检索的范围

临键锁也属于行锁的一种，并且它是InnoDB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，

临键锁会把查询出来的数据锁住，同时也会把该范围内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

它的封锁范围，既包含索引记录，又包含索引区间。

触发条件：范围查询并命中，查询命中了索引。

结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读，重复读，幻读问题，加了临键锁之后，在范围区间内数据不允许被修改和插入。

注意：

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

MySQL 5.5引入了meta data lock，简称MDL锁，用于保证表中元数据的信息。在会话A中，表开启了查询事务后，会自动获得一个MDL锁，会话B就不可以执行任何DDL语句，不能执行为表中添加字段的操作，会用MDL锁来保证数据之间的一致性。

元数据就是描述数据的数据，也就是你的表结构。意识是在你开启了事务之后获得了意向锁，其他事务就不能更改你的表结构。MDL锁都是为了防止在事务进行中，执行DDL语句导致数据不一致。

页级锁是MySQL中锁粒度介于行级锁和表级锁中间的一种锁，表级锁速度快，但冲突多，行级锁冲突少，但是速度慢，所以取了折中的页级锁，一次锁定相邻的一组记录，

页级锁的特点： 开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般。

如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁，你们不能对整个表加共享锁或者排他锁了，那么后面需要对整个表加锁的人只需要获取这个状态，就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是意向锁。

意向共享锁： 当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

意向排他锁： 当一个事务试图对整个表进行加排他锁之前，首先需要获得这个表的意向排他锁。

全局锁：Flush tables with read lock，加锁之后整个数据库实例都处于只读状态，所有的数据变更操作都会被挂起。一般用于全库逻辑备份。

发生死锁的必要条件有4个，分别为互斥条件、不可剥夺条件、请求与保持条件和循环等待条件：

• 互斥条件，在一段时间内，计算机中的某个资源只能被一个进程占用。此时，如果其他进程请求该资源，则只能等待。
• 不可剥夺条件，某个进程获得的资源在使用完毕之前，不能被其他进程强行夺走，只能由获得资源的进程主动释放。
• 请求与保持条件，进程已经获得了至少一个资源，又要请求其他资源，但请求的资源已经被其他进程占有，此时请求的进程就会被阻塞，并且不会释放自己已获得的资源。
• 循环等待条件，系统中的进程之间相互等待，同时各自占用的资源又会被下一个进程所请求。例如有进程A、进程B和进程C三个进程，进程A请求的资源被进程B占用，进程B请求的资源被进程C占用，进程C请求的资源被进程A占用，于是形成了循环等待条件，如图1-7所示。

我模拟了一个死锁场景，如下： InnoDB使用的是行级锁，在某种情况下会产生死锁问题，所以InnoDB存储引擎采用了一种叫作等待图（wait-for graph）的方法来自动检测死锁，如果发现死锁，就会自动回滚一个事务。

在MySQL中，通常通过以下几种方式来避免死锁。

• 尽量让数据表中的数据检索都通过索引来完成，避免无效索引导致行锁升级为表锁。
• 合理设计索引，尽量缩小锁的范围。
• 尽量减少查询条件的范围，尽量避免间隙锁或缩小间隙锁的范围。
• 尽量控制事务的大小，减少一次事务锁定的资源数量，缩短锁定资源的时间。如果一条SQL语句涉及事务加锁操作，则尽量将其放在整个事务的最后执行。

如何查看死锁

• 使用命令show engine innodb status 查看最近的一次死锁。
• InnoDB Lock Monitor打开锁监控，每15s输出一次日志，使用完毕后建议关闭，否则会影响数据库性能。

对待死锁常见的两种策略

• 通过innodblockwait_timeout来设置超时时间，一直等待直到超时。
• 发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其他事务继续执行。

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

• 第一种情况是：事务需要更新大部分或者全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
• 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚，这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因为事务回滚带来的开销。

在InnoDB下 ，主动上表锁的方式如下：

lock tables teacher write,student read;
select * from teacher;
commit;
unlock table

使用时有几点需要额外注意：

• 使用lock tables 虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层MySQL Server层负责的，仅当autocommit=0（手动提交事务），innodb_table_lock=1（默认设置）时，InnoDB层才能感知MySQL加的表锁，MySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。
• 在使用lock tables 对InnoDB加锁时要注意，事务结束前，不要用unlock tables释放表锁，因为unlock tables会隐含地提交事务，commit和rollback不能释放用lock tables 加的表级锁，必须用unlock tables释放表锁，正确的方式见如下语句。

SET AUTOCOMMIT=0;

LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];

COMMIT;
UNLOCK TABLES;

• 表锁的力度很大，慎用。

乐观锁大多是基于数据版本记录机制实现，一般是给数据库表增加一个version字段。

• 读取数据时，将此版本号一同读出。
• 更新时，对此版本号加1，此时将提交数据的版本号与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号等于数据表当前版本号，则予以更新，否则认为是过期数据。

事务一：

select * from ydl_student where id = 1;

事务二：

select * from ydl_student where id = 1;
update ydl_student set score = 99,version = version + 1 where id = 1 and version = 1;
commit;

事务一：

update ydl_student set score = 100,version = version + 1 where id = 1 and version = 1;
commit;

发现更新失败，应为版本号被事务二、提前修改了，这使用了不加锁的方式，实现了一个事务修改期间，禁止其他事务修改的能力。

总有刁民想害朕！！！

悲观锁依靠数据库提供的锁机制实现，MySQL中的共享锁和排它锁都是悲观锁，数据库的增删改操作默认都会加排它锁，而查询不会加任何锁，此处不赘述。

mysql给我们提供了很多有用的日志，这是mysql服务层给我们提供的：

二进制日志（binnary log）是以事件形式记录了对MySQL数据执行修改的所有操作。

binlog记录了所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（例如INSERT、UPDATE、DELETE…）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的 所有语句。

binlog是MySQL Server层维护的，跟采用何种存储引擎没有关系，记录的是所有的更新操作的日志记录。binlog是在事务最终commit前写入的。我们执行SELECT等不涉及数据更新的语句是不会记binlog的，而涉及到数据更新则会记录。需要注意的是，对支持事务的引擎比如InnoDB来说，必须要提交了事务才会记录binlog。

binlog文件写满以后，会自动切换到下一个日志文件继续写，而不会覆盖以前的日志，这个也区别redo log，undo log是循环写入的，即后面写入的可能会覆盖前面写入的。

binlog有两个常用的使用场景：

• 主从复制：后面会专门有一个章节代领大家搭建一个主从同步的两台mysql服务。
• 数据恢复：通过mysqlbinlog工具来恢复数据。

mysql8中的binLog默认是开启的，5.7默认是关闭的，可以通过参数log_bin控制：

1. 确认binlog是否开启，log_bin变量的值为ON代表binlog日志是开启状态。

show variables like 'log_bin';

如果没有开启，可以在配置文件 [mysqld] 下写入如下内容并重启mysql服务：

# 开启 Binlog 并写明存放日志的位置
log_bin = /usr/local/mysql/log/bin-log

2. 为了防止干扰，我们flush刷新log日志，自此刻会产生一个新编号的binlog日志文件：

flush logs;

3. 查看所有binlog日志列表

show master logs;

4. 查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值，这一步可有可无：

show master status;

5. 执行sql

先创建表，并插入一些数据：

DROP TABLE IF EXISTS ydl_student;
CREATE TABLE `ydl_student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `score` int(255) DEFAULT NULL,
  `grade` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (1, 'lucy', 80, 'a');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (2, 'lily', 90, 'a');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (3, 'jack', 60, 'c');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (4, 'hellen', 40, 'd');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (5, 'tom', 60, 'c');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (6, 'jerry', 10, 'd');
INSERT INTO `ydl_student`(`id`, `name`, `score`, `grade`) VALUES (7, 'sily', 20, 'd');

执行删除操作，假装误删除，直接全部删除也可以，把表删了都行，一样的道理：

delete from ydl_student where id in (3,5);

6. 查看binlog日志，我们因为刷新了日志，所以本次操作都会在最新的日志文件上：

因为binlog的日志文件是二进制文件，不能用文本编辑器直接打开，需要用特定的工具来打开，MySQL提供了mysqlbinlog来帮助我们查看日志文件内容。

# 查看全部的日志信息
mysqlbinlog -v C:\Users\DXH\Desktop\bin-log.000002

文件内容 真实的情况下，我们的日志文件比较复杂，内容比较多使用时间范围查询后任然可能需要花费时间去排查问题，这里我们找到了误删除的位置：

# 指定位置范围
mysqlbinlog -v C:\Users\DXH\Desktop\bin-log.000002 --start-position=0 --stop-position=986

# 指定时间范围
mysqlbinlog -v C:\Users\DXH\Desktop\bin-log.000002 --start-datetime="2022-07-08 15:18:00" --stop-datetime="2022-07-08 18:18:00" 

截取其中的一段进行分析：

# at 2324
#220708 18:00:46 server id 1  end_log_pos 2430  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1657274446/*!*/;
delete from ydl_student where id in (3,5)

上面输出包括信息：

• position：位于文件中的位置，即第一行的（# at 2324），说明该事件记录从文件的第2324个字节开始。
• timestamp：事件发生的时间戳，即第二行的（#220708 18:00:46 ）
• server id ：服务器标识。
• end_log_pos:表示下一个事件的结束位置（即当前事件的结束位置+1）。
• thread_id：执行该事件的线程id（thread_id=1 ）
• exec_time: 事件执行的花费时间。
• error_code: 错误码，0意味着没有发生错误。
• type:事件类型Query

7. 执行恢复，通过上一步的操作，我们找到了删除的位置2254（即第一个红框），执行下面的语句：

mysqlbinlog -v C:\Users\DXH\Desktop\bin-log.000002 --stop-position=2254 -v | mysql -uroot -p密码

8. 至此，数据已完全恢复了：

binlog的数据恢复的本质，就是将之前执行过的sql，从开始到指定位置全部执行一遍，如果报错【当前表已经存在】，就将数据库的表删除，重新恢复。

binlog有三种格式，使用变量binlog_format查看当前使用的是哪一种。

• Statement（Statement-Based Replication,SBR）：每一条会修改数据的SQL都会记录在binlog中。
• Row（Row-Based Replication,RBR）：不记录SQL语句的上下文信息，仅保存那条记录被修改。
• Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体，当前默认的选项，5.7中默认row。

我们举一个例子来说明row和statement的区别，在下面的插入语句中我们有一个函数uuid()，如果日志文件仅仅保存sql语句，下一次执行的结果可能不一致，所以Row格式的文件，他保存的是具体哪一行，修改成了什么数据，记录的是数据的变化，不是简单的sql：

insert into ydl_student values (8,UUID(),45,'d');

Statement和row的优劣

• Statement模式只记录执行的SQL，不需要记录每一行数据的变化，因此极大的减少了binlog的日志量，避免了大量的I/O操作。提升了系统的性能。
• 由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。所以使用 Statement 格式会出现一些数据一致性问题。
• 从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。
• 不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。

• binlog文件以一个值为0Xfe62696e的魔数开头，这个魔数对应0xfebin。

• binlog由一系列的binlog event构成。每个binlog event包含header和data两部分。

  • header部分提供的是event的公共的类型信息，包括event的创建时间，服务器等等。
  • data部分提供的是针对该event的具体信息，如具体数据的修改。

常见的事件类型有：

• FORMAT_DESCRIPTION_EVENT：该部分位于整个文件的头部，每个binlog文件都必定会有唯一一个该event
• WRITE_ROW_EVENT：插入操作。
• DELETE_ROW_EVENT：删除操作。
• UPDATE_ROW_EVENT：更新操作。记载的是一条记录的完整的变化情况，即从前量变为后量的过程。
• ROTATE_EVENT：Binlog结束时的事件，用于说明下一个binlog文件。

一个event的结构如下，我们在恢复数据的时候已经看到了：

• 每个日志的最后都包含一个rotate event用于说明下一个binlog文件。
• binlog索引文件是一个文本文件，其中内容为当前的binlog文件列表，比如下面就是一个mysql-bin.index文件的内容。

二进制日志文件并不是每次写的时候同步到磁盘，因此当数据库所在的操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中。这给恢复和复制带了问题，参数sync_binlog=[N]表示每写多少次就同步到磁盘，如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这是写操作不使用操作系统的缓冲来写二进制日志，备注：该值默认为0，采用操作系统机制进行缓冲数据同步）。

数据库单点部署的问题

• 服务器宕机，会导致业务停顿，影响客户体验。
• 服务器损坏，数据丢失，不能及时备份，造成巨大损失。
• 读写操作都在同一台服务器，在并发量大的情况下性能存在瓶颈。

那么我们就可以使用mysql的binlog搭建一个一主多从的mysql集群服务。这样的服务可以帮助我们异地备份数据、进行读写分离，提高系统的可用性。

（1） 主从复制工作原理剖析

MySQL的主从复制中主要有三个线程，Master（binlog dump thread），Slave（I/O thread，SQL thread）。Master一条线程和Salve中的两条线程。

• 主节点binlog，主从复制的基础是主库记录数据库的所有变更记录到binlog中，binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构和内容的一个文件。
• Master数据库只要发生变化，立马记录到Binary log日志文件中。
• Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志。
• Salve数据库I/O获取到的二进制日志，保存到自己的Relay log