MySQL-第五章(下)-事务ACID特性

目录

7、事务的ACID特性

8、事务的生命周期（事务控制语句）

8.1 事务的开始

8.2 事务的结束

8.3 自动提交策略（autocommit）

8.4 隐式提交语句

8.5 隐式回滚

8.6 开始事务流程

8.7 事务回滚事务点

9. InnoDB 事务的ACID如何保证?

9.0 一些概念

 9.1 redo log

9.1.1 Redo是什么？

9.1.2 作用是什么？

9.1.3 redo日志位置

9.1.4 redo buffer

9.1.5 redo的刷新策略

9.1.6 MySQL CSR——前滚

10.undo 回滚日志

 10.1 undo是什么？

10.2 作用是什么？

10.3 undo表空间

10.4 undo表空间管理 （5.7版本）

10.5 配置undo表空间（5.7版本）

10.6 配置undo表空间（8.0版本）

11.概念性的东西:

11.1锁

11.2 隔离级别

---

7、事务的ACID特性

Atomic（原子性）

所有语句作为一个单元全部成功执行或全部取消。不能出现中间状态。

Consistent（一致性）

如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。

Isolated（隔离性）

事务之间不相互影响。

Durable（持久性）

事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。

8、事务的生命周期（事务控制语句）

Transaction （交易）。 伴随着交易类的业务出现的概念（工作模式）

8.1 事务的开始

begin：开始事务

说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。

8.2 事务的结束

commit：提交事务

完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。

rollback ：回滚事务

将内存中，已执行过的操作，回滚回去

8.3 自动提交策略（autocommit）

db01 [(none)]>select @@autocommit;

db01 [(none)]>set autocommit=0;

db01 [(none)]>set global autocommit=0;

注:自动提交是否打开，一般在有事务需求的MySQL中，将其关闭

不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能

(1)set autocommit=0;   

set global autocommit=0;

(2)vim /etc/my.cnf

autocommit=0     

8.4 隐式提交语句

1.用于隐式提交的 SQL 语句：

begin

a

b

begin

2.SET AUTOCOMMIT = 1

3.导致提交的非事务语句：

DDL语句： （ALTER、CREATE 和 DROP）

DCL语句： （GRANT、REVOKE 和 SET PASSWORD）

锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

4.导致隐式提交的语句示例：

TRUNCATE TABLE

LOAD DATA INFILE

SELECT FOR UPDATE

8.5 隐式回滚

1.会话窗口被关闭。

2.数据库关宕机

3.出现事务冲突（死锁）

4.事务语句执行失败

8.6 开始事务流程

1、检查autocommit是否为关闭状态
select @@autocommit;
或者：show variables like 'autocommit';
2、开启事务,并结束事务
begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
rollback;
--------------------------------------------------
begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
commit;

8.7 事务回滚事务点

下面演示将向表user中连续插入3条数据，在插入第2条数据的后面定义一个保存点，最后看看能否回滚到此保存点。

1、查看user表中的数据

mysql> select * from user;
+-----+----------+-----+------+
| mid | name | scx | word |
+-----+----------+-----+------+
| 1 | zhangsan | 0 | NULL |
| 2 | wangwu    | 1 | NULL |
+-----+----------+-----+------+
2 rows in set (0.05 sec)

2、mysql事务开始

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

3、向表user中插入2条数据

mysql> INSERT INTO user VALUES ('3','one','0','');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO user VALUES ('4,'two','0','');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+-----+----------+-----+------+
| mid | name | scx | word |
+-----+----------+-----+------+
| 1 | zhangsan | 0 | NULL |
| 2 | wangwu    | 1 | NULL |
| 3 | one            | 0 | |
| 4 | two             | 0 | |
+-----+----------+-----+------+
4 rows in set (0.00 sec)

4、指定保存点，保存点名为test

mysql> SAVEPOINT test;
Query OK, 0 rows affected (0.00 sec)

5、向表user中插入第3条数据

mysql> INSERT INTO user VALUES ('5','three','0','');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+-----+----------+-----+------+
| mid | name | scx | word |
+-----+----------+-----+------+
| 1 | zhangsan | 0 | NULL |
| 2 | wangwu | 1 | NULL |
| 3 | one | 0 | |
| 4 | two | 0 | |
| 5 | three | 0 | |
+-----+----------+-----+------+
5 rows in set (0.02 sec)

6、回滚到保存点test

mysql> ROLLBACK TO SAVEPOINT test;
Query OK, 0 rows affected (0.31 sec)
mysql> select * from user;
+-----+----------+-----+------+
| mid | name | scx | word |
+-----+----------+-----+------+
| 1 | zhangsan | 0 | NULL |
| 2 | wangwu    | 1 | NULL |
| 3 | one            | 0 | |
| 4 | two            | 0 | |
+-----+----------+-----+------+
4 rows in set (0.00 sec)

9. InnoDB 事务的ACID如何保证?

9.0 一些概念

redo log ---> 重做日志 ib_logfile0~1   50M   , 轮询使用

redo log buffer ---> redo内存区域

ibd     ----> 存储 数据行和索引

buffer pool --->缓冲区池,数据和索引的缓冲

LSN : 日志序列号

磁盘数据页,redo文件,buffer pool,redo buffer

MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动

WAL : write ahead log 日志优先写的方式实现持久化

脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.

CKPT: Checkpoint,检查点,就是将脏页刷写到磁盘的动作

TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.

 9.1 redo log

9.1.1 Redo是什么？

redo,顾名思义“重做日志”，是事务日志的一种。

9.1.2 作用是什么？

在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用

9.1.3 redo日志位置

redo的日志文件：iblogfile0 iblogfile1

9.1.4 redo buffer

redo的buffer:数据页的变化信息+数据页当时的LSN号

LSN：日志序列号  磁盘数据页、内存数据页、redo buffer、redolog

9.1.5 redo的刷新策略

commit;

刷新当前事务的redo buffer到磁盘

还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘

9.1.6 MySQL CSR——前滚

MySQL : 在启动时,必须保证redo日志文件和数据文件LSN必须一致, 如果不一致就会触发CSR,最终保证一致

情况一:

我们做了一个事务,begin;update;commit.

1.在begin ,会立即分配一个TXID=tx_01.

2.update时,会将需要修改的数据页(dp_01,LSN=101),加载到data buffer中

3.DBWR线程,会进行dp_01数据页修改更新,并更新LSN=102

4.LOGBWR日志写线程,会将dp_01数据页的变化+LSN+TXID存储到redobuffer

5. 执行commit时,LGWR日志写线程会将redobuffer信息写入redolog日志文件中,基于WAL原则,

在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)

6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失

7.MySQL再次重启时,必须要redolog和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102

MySQL此时无法正常启动,MySQL触发CSR.在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redolog LSN一值.这时MySQL正常启动

以上的工作过程,我们把它称之为基于REDO的"前滚操作"

10.undo 回滚日志

 10.1 undo是什么？

undo,顾名思义“回滚日志”

10.2 作用是什么？

在事务ACID过程中，实现的是“A” 原子性的作用

另外CI也依赖于Undo

在rollback时,将数据恢复到修改之前的状态

在CSR实现的是,将redo当中记录的未提交的时候进行回滚.

undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备

undo在生成过程中，也会记录redo信息

什么是一致性快照？

默认每个事务开始前，都会通过Undo生成一个一致性的快照

10.3 undo表空间

1. 作用： 用来作撤销工作。

2. 存储位置： 5.7版本，默认存储在共享表空间中（ibdataN）。8.0版本以后默认就是独立的（undo_001-undo_002）。

3. 生产建议： 5.7版本后，将undo手工进行独立。

----------------------------------------------------------------

UNDO内部由多个回滚段组成，即 Rollback segment，一共有128个，保存在ibdata系统表空间中，分别从resg slot0 - resg slot127，每一个resg slot，也就是每一个回滚段，内部由1024个undo segment 组成。

回滚段（rollback segment）分配如下：

slot 0 ，预留给系统表空间；

slot 1- 32，预留给临时表空间，每次数据库重启的时候，都会重建临时表空间；

slot33-127，如果有独立表空间，则预留给UNDO独立表空间；如果没有，则预留给系统表空间；

回滚段中除去32个提供给临时表事务使用，剩下的 128-32=96个回滚段，可执行 96*1024 个并发事务操作，每个事务占用一个 undo segment slot，注意，如果事务中有临时表事务，还会在临时表空间中的 undo segment slot 再占用一个 undo segment slot，即占用2个undo segment slot。

10.4 undo表空间管理 （5.7版本）

查看undo的配置参数
SELECT @@innodb_undo_tablespaces;  ---->3-5个    #打开独立undo模式，并设置undo的个数。
SELECT @@innodb_max_undo_log_size;               #undo日志的大小，默认1G。
SELECT @@innodb_undo_log_truncate;               #开启undo自动回收的机制（undo_purge）。
SELECT @@innodb_purge_rseg_truncate_frequency;   #触发自动回收的条件，单位是检测次数。

10.5 配置undo表空间（5.7版本）

1.配置undo表空间

#########官方文档说明############
Important
The number of undo tablespaces can only be configured 
when initializing a MySQL instance and is fixed for the life of the instance.
#################################

2.清空存数据目录

[root@db01 tmp]# pkill mysqld
[root@db01 tmp]# rm -rf /data/3357/data/*

3.编写配置文件

vim /etc/my.cnf
# 添加参数
innodb_undo_tablespaces=3           
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32

4. 重新初始化数据库生效

[root@db01 data]# /usr/local/mysql57/bin/mysqld --defaults-file=/data/3357/my.cnf  --initialize-insecure   --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/3357/data

5.启动数据库

[root@db01 data]# /etc/init.d/mysqld start
[root@db01 data]# ll /data/3306/data/undo00*
-rw-r----- 1 mysql mysql 10485760 May  7 15:39 /data/3306/data/undo001
-rw-r----- 1 mysql mysql 10485760 May  7 15:39 /data/3306/data/undo002
-rw-r----- 1 mysql mysql 10485760 May  7 15:39 /data/3306/data/undo003

#如果进行undo独立存储到其他文件系统

a. 关闭数据库

[root@db01 data]# systemctl start mysqld3357

b.设定路径参数

innodb_undo_directory=/data/3357/undologs 

c. 创建目录,并拷贝文件

[root@db01 data]# systemctl stop mysqld3357
mkdir -p  /data/3357/undologs 
chown -R mysql. /data/* 
cp -a /data/3357/data/undo* /data/3357/undologs

10.6 配置undo表空间（8.0版本）

参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

a.  innodb_undo_tablespaces参数不再使用,可以直接按需添加undo
mysql> CREATE UNDO TABLESPACE oldguo_undo_003 ADD DATAFILE 'undo_003.ibu';

b. 查询undo 
SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
WHERE FILE_TYPE LIKE 'UNDO LOG';

c. 删除 
ALTER UNDO TABLESPACE oldguo_undo_003 SET INACTIVE;
DROP UNDO TABLESPACE oldguo_undo_003;

11.概念性的东西:

redo怎么应用的

undo怎么应用的

CSR(自动故障恢复)过程

LSN :日志序列号

TXID:事务ID

CKPT(Checkpoint)

11.1锁

“锁”顾名思义就是锁定的意思。相当于厕所的门

“锁”的作用是什么？

保证事务之间的隔离性，保证资源不会被争用，锁是属于资源的，不是某个事务的特性

每次事务需要资源的时候，需要申请持有资源的锁

在事务ACID过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo也有参与).

悲观锁:行级锁定(行锁)

谁先操作某个数据行,就会持有<这行>的(X)锁.

乐观锁: 没有锁

锁的类型

资源：

内存锁：mutex（主要保护内存资源）   latch（闩锁）-----轻量级锁，保证内存数据页资源不被争用，不被置换

对象锁粒度：

        MDL(元数据锁)：修改元数据时，DDL-àalter,备份

        Table-Lock：表锁，备份（FTWRL全局表锁），DDL，lock tables t1 read/write(手工设定)也有可能升级为表锁

        Record(row) lock:行锁，索引锁，锁定聚簇索引

        GAP：间隙锁，RR级别，普通辅助所以间隙锁

        NEXT-LOCK：下一键锁，GAP+Record Lock  普通辅助索引的范围锁

功能：

        IS：意向共享锁，表级别

        S：共享锁，读锁，行级别

        IX：意向排他锁，表级别

        X：排他锁，写锁，行级别

11.2 隔离级别

影响到数据的读取,默认的级别是 RR模式.

transaction_isolation   隔离级别(参数)

负责的是,MVCC,读一致性问题

RU  : 读未提交,可脏读,一般部议叙出现

RC  : 读已提交,可能出现幻读,可以防止脏读.

RR  : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)

SR  : 可串行化,可以防止死锁,但是并发事务性能较差

补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.

设置隔离级别为：'READ-UNCOMMITTED'
mysql> set global transaction_isolation='READ-UNCOMMITTED';
mysql> set global autocommit=0;

重新开启两个session;
mysql> select @@transaction_isolation;
mysql> select @@autocommit;

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

事务并发度：RU>RC>RR>SE   ---------     互联网中常用的是RC