Mysql高级之事务、锁、日志详解

一、事务

1.事务特性及保证事务特性的原理

1. 原子性：当前事务的操作要么全部成功，要么全部失败。原子性由undo log实现，undo log记录了每次操作之前的数据版本，如果某一操作失败，可以根据undo log回滚到最初状态。
2. 一致性：事务执行前后，变化一致。由其它三个特性和正确逻辑来保证。
3. 隔离性：在事务执行时，他们内部的操作不能互相干扰。隔离性由Mysql的各种锁和MVCC机制实现（MVCC机制是多版本并发控制，专门用来保证并发事务时读已提交和读未提交隔离性。具体是通过一致性视图 read view和undo 版本链通过一致性算法比对规则来保证）。
4. 持久性：一旦事务提交，它对数据库的改变就是永久的。持久性由redo log实现。

2.关于readview和可见性算法的原理解释

readview和可见性算法其实就是记录了sql查询那个时刻数据库里提交和未提交所有事务的状态。

• 要实现RR隔离级别，事务里每次执行查询操作readview都是使用第一次查询时生成的readview，也就是都是以第一次查询时当时数据库里所有事务提交状态来比对数据是否可见，当然可以实现每次查询的可重复读的效果了。
• 要实现RC隔离级别，事务里每次执行查询操作readview都会按照数据库当前状态重新生成readview，也就是每次查询都是跟数据库里当前所有事务提交状态来比对数据是否可见，当然实现的就是每次都能查到已提交的最新数据效果了。

3.事务隔离级别及存在问题

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

• 读未提交（Read uncommitted）：一个事务可以读到另一个事务还未提交的内容
• 读已提交（Read committed）：一个事务可以读到另一个事务已经提交的内容
• 可重复读（Repeatableread）：一旦事务开启，每次读取的内容都是相同的
• 串行化（Serializable）：通过加锁的方式，让操作数据库的事务按顺序执行

4.事务问题定位

-- 查看当前数据库的事务隔离级别: 
show variables like 'tx_isolation';
-- 设置事务隔离级别：
set tx_isolation='REPEATABLE-READ';
-- 查询执行时间超过1秒的事务
select *from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>1;
-- 强制结束事务
kill 事务对应的线程id(trx_mysql_thread_id字段)

二、数据库的锁

1.锁按性能分：

• 乐观锁：不会对操作的数据直接加锁，而是通过操作前比较版本和每次操作之后修改版本实现。适用于读多写少的场景，写数据较多时会造成cpu空转效率降低。
• 悲观锁：对数据操作前先获取锁避免其它线程操作干扰，读锁和写锁都是悲观锁。

-- 读锁
select * from T where id=1 lock in share mode
-- 写锁
select * from T where id=1 for update

• 意向锁：意向锁主要是mysql为了提高加表锁的效率自己加的。当需要给一个表加表锁时需要逐行判断有没有行锁，有了意向锁当有事务给表的数据增加了行锁或者排他锁时，会给表设置一个标识。这样再加表锁时就无需逐行判断。

2.锁按粒度分：

• 表锁：针对于整个表加锁。开销小，加锁快。一般用在整张表迁移的场景

-- 手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
-- 查看表上加过的锁
show open tables;
-- 删除表锁
unlock tables;

• 行锁：针对某一行数据加锁，开销大，加锁慢会出现死锁。

注意： 行锁是加在索引上，不是加在整行上。如果索引失效或者没有针对索引加锁，会造成行锁升级为表锁（RR级别会升级为表锁，RC级别不会升级）。
问题：为什么行锁升级表锁针对RR级别。因为RR级别为了保证可重复读，扫描过的数据就不允许其它事务修改或者间隙内被其它事务插入数据（幻读）从从而导致数据不一致，所以Mysql的做法是把所有扫描过的索引和间隙都加锁

-- 针对隔离级别为 RR
‐‐where条件里的name字段无索引 所以会升级为表锁
select * from account where name = 'lilei' for update; 

3.间隙锁、临键锁（为了解决RR级别下的幻读问题）

• 间隙锁：锁的是两个值之间的空隙。间隙锁是针对RR级别才生效
• 临键锁：除了锁主两个值的间隙，还会锁主两个值本身

4.锁问题分析

-- 查看系统上的行锁的争夺情况
show status like 'innodb_row_lock%';

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁，8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;  
-- 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  

-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

-- 查看锁等待详细信息
show engine innodb status; 

三、Mysql中的日志

1.redo日志

• 1.redo日志作用：如果事务提交成功，buffer pool 里的数据还没来得及写入磁盘，此时系统宕机了就可以用redo log来恢复磁盘文件ibd里的数据。

• 2.redo日志写过程：事务提交时先把日志写在redo log buffer中，调用操作系统函数 write写道文件系统的page cache,然后调用操作系统函数的fsync持久化到磁盘文件。

• 3.重要参数

-- 这个参数控制 redo log 的写入策略
innodb_flush_log_at_trx_commit：
	0：每次事务提交都只把redo log日志写在redo log buffer中
	1：每次事务提交把redolog日志直接持久化到磁盘
	2：每次事务提交把redolog日志写到操作系统的page cach中

# 查看innodb_flush_log_at_trx_commit参数值：
show variables like 'innodb_flush_log_at_trx_commit';
# 设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)：
set global innodb_flush_log_at_trx_commit=1;  

2.binlog日志

2.1binlog 作用

binlog是二进制日志，保存了所有执行过的修改操作语句。如果mysql数据库意外停止，可以用来恢复数据库数据。

# 查看binlog相关参数
show variables like '%log_bin%';

log_bin：binlog日志是否打开状态
log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

2.2开启binlog

打开binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重启数据库。在配置文件中的[mysqld]部分增加如下配置:

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=mysql-binlog
# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 其他配置
binlog_format = row # 日志文件格式，下面会详细解释
expire_logs_days = 15 # 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

2.3binlog写入磁盘机制

binlog写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0

• 0：每次提交事务都只写到page cach
• 1：每次提交都会写到磁盘
• N:每次提交事务只写道 page cach,积累N个后写到磁盘

2.4binlog日志格式

用参数 binlog_format 可以设置binlog日志的记录格式。

• STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小。但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样
• ROW：基于行的复制，日志中会记录成每一行数据被修改的形式。这种方式日志量较大，性能不如Statement
• MIXED:混合模式复制，实际就是前两种模式的结合。在Mixed模式下，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种

2.5查看binlog日志文件

# 查看bin-log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/user/mysql-5.7.25-winx64/data/mysql-binlog.000001 

# 查看bin-log二进制文件（带查询条件）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/user/mysql-5.7.25-winx64/data/mysql-binlog.000001 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 00:00:00" start-position="5000" stop-position="20000"

2.6使用binlog日志恢复被删除数据

# 1.先执行刷新日志的命令生成一个新的binlog文件mysql-binlog.000008，后面我们的修改操作日志都会记录在最新的这个文件里
flush logs;
# 2.执行两条插入语句
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('4', 'zhuge', '666');
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('5', 'zhuge1', '888');
# 3.假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了
delete from account where id > 3;
# 4.查看binlog日志文件
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000002
# 5.找到第一条sql BEGIN前面的文件位置标识 at 219(这是文件的位置标识)，再找到第二条sql COMMIT后面的文件位置标识 at 701
mysqlbinlog  --no-defaults --start-position=219 --stop-position=701 --database=test D:/user/mysql-5.7.25-winx64/data/mysql-binlog.000002 | mysql -uroot -p123456 -v test

以上步骤执行完被删除数据被恢复！

2.7dump备份

如果要全量数据同步或者备份，直接使用dump将全量数据保存下来。

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

mysql -u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test

3.undo日志

undo日志主要用来记录每一步修改操作前的数据，方便事务执行失败后回滚。

innodb_undo_directory：设置undo log文件所在的路径。该参数的默认值为"./"，即innodb数据文件存储位置，目录下ibdata1文件就是undo log存储的位置。
innodb_undo_logs: 设置undo log文件内部回滚段的个数，默认值为128。
innodb_undo_tablespaces: 设置undo log文件的数量，这样回滚段可以较为平均地分布在多个文件中。设置该参数后，会在路径innodb_undo_directory看到undo为前缀的文件。