浅谈MySQL事务

最新推荐文章于 2024-09-02 20:24:59 发布

oldba.cn

最新推荐文章于 2024-09-02 20:24:59 发布

阅读量127

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_45320660/article/details/115325685

版权

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMITWORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

事务测试

 mysql> use RUNOOB;Database changedmysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
 Query OK, 0 rows affected (0.04 sec)
 
 mysql> select * from runoob_transaction_test;
 Empty set (0.01 sec)
 
 mysql> begin;  # 开始事务Query OK, 0 rows affected (0.00 sec)
 mysql> insert into runoob_transaction_test value(5);
 Query OK, 1 rows affected (0.01 sec)
 
 mysql> insert into runoob_transaction_test value(6)
 mysql> commit; # 提交事务
 Query OK, 0 rows affected (0.01 sec)

 mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

 mysql> begin
 mysql> insert into runoob_transaction_test values(7);
 Query OK, 1 rows affected (0.00 sec)
 mysql> rollback;   # 回滚
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
 +------+
 | id   |
 +------+
 | 5    |
 | 6    |
 +------+
 2 rows in set (0.01 sec)

在介绍事务工作方式之前，先介绍几个名词解释

重做日志（redo log）
ib_logfile0~1 50M 轮询使用
日志缓存区（redo log buffer）
redo内存区域
表空间数据文件（ibd）
存储数据行和索引
数据缓存区（innodb buffer pool）
缓冲区池,数据和索引的缓冲 16KB在内存的区域
日志序列号（LSN）log sequnces num
记录redo日志号码变化类似版本序列号
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致，数据库才能正常启动
WAL : write ahead log
redo日志优先于数据页写入磁盘,从而实现持久化功能
脏页（dirty page）
内存脏页:内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页
CKPT（Checkpoint）
检查点：检查事务有没有提交，未提交的回滚
实际上将脏页刷写到磁盘的动作。
DB_TRX_ID 6字节事务ID号
InnoDB会为每一个事务生成一个事务号,伴随着整个事务。
DB_ROLL_PTR 7字节回滚指针
rollback时，会使用undo日志回滚已修改的数据. DB_ROLL_PTR指向了此次事务的回滚位置点，用来找到undo日志信息。

事务工作流程原理
在这里插入图片描述

事务举例演示说明：

begin;
update t1 set name="lisi" where id=1;
commit;

redo log 重做日志如何应用？

事务正常发生期间的工作流程：
在这里插入图片描述

事务处理原理：
执行DML语言事务语句，磁盘上查找到相应的数据id=1的数据页，生成事务ID，一起调到内存buffer_pool中进行缓存，

内存中更新数据时（形成脏页），在log_buffer中生成redo日志(记录数据页发生的变化)，以及生成LSN。

执行commit时，基于WAL机制，将redo日志存放在磁盘的ib_logfireN中,commit完成。

ib_logfireN中记录了一条日志(page10数据页变化+LSN=1002) 采用异步同步机制。

此时如果宕机，IBP和ILB数据全丢。

启动后，IBD将磁盘的数据加载到内存IBP中和Ib_logfile0将磁盘的数据加载到内存ILB。ILB进行redo操作IBP，自动处理故障恢复。

但是因为commit,CKPT检查直接落盘。

总结：宕机时，已经提交的事务不会丢。 commit ----> redo log落盘

情景：宕机时，redo落地，数据页没有落地。
宕机恢复后，mysql CR（自动故障恢复）工作模式，自动检查redo的LSN号码和数据页的LSN号码

会比较磁盘数据页和redolog的LSN（lsn号码记录数据页字节发生量，记录日志字节发生量）,必须要求两者LSN一致数据库才能正常启动，如果发现，redo LSN > 数据页的LSN,将redo log加载到log_buffer，将原始的数据页重新加载到buffer_pool，在缓冲区实现LSN重新同步。使用redo重新构造脏页(前滚) redo落盘数据不一定落盘。

此次事务已经提交（commit标签），立即触发CKPT动作，将脏页刷写到磁盘上。检查磁盘的LSN才会更新，都保持2000，数据库启动。

commit执行成功，唯一能保证的是redo落盘

补充：为了减少IO次数
MySQL有一种机制，批量刷写redo的机制。会在A事务commit时，顺便将redo buffer中的未提交的redo日志也一并刷到磁盘。
为了区分不同状态的redo，日志记录时，会标记是否COMMIT（如果提交，找到只改标签，不会发生io）。

redo保证了ACID那些特性？
主要是D的特性，另外A、C也有保证

undo log 回滚日志如何应用？
undo按段存储，共128段一个段1024个槽位（slot）一个事务一个槽位

事务回滚期间的工作流程：

事务处理流程：
事务发生数据页修改之前，先会申请一个undo事务操作，保存事务回滚日志（逆向操作的逻辑日志）。

undo落地之后，事务修改数据页头部（会记录DB_TRX_ID事务ID和DB_ROLL_PTR指针号），在进行redo操作，会记录DB_TRX_ID事务ID和DB_ROLL_PTR指针号在redo中

情景1：
当执行rollback时，根据数据页的会记录DB_TRX_ID事务ID和DB_ROLL_PTR指针号，找到undo日志，进行回滚。

情景2：

begin; 
update t1 set A=2 where A=1;
宕机。

假设：undo 有， redo没有
启动数据库时，检查redo和数据页的LSN号码。发现是一致的。

所以不需要进行redo的前滚，此时也不需要回滚。undo信息直接被标记为可覆盖状态。

假设：undo 有，redo也有此时没有commit,搭了个便车。提前写到磁盘。

MySQL CR（自动故障恢复）工作模式，启动数据库时，自动检查redo的LSN和数据页LSN。

如果发现redoLSN>数据页的LSN ，加载原始数据页+变化redo指定内存。使用redo重构脏页（前滚）。

如果确认此次事务没有commit标记，立即触发回滚操作，根据DB_TRX_ID+DB_ROLL_PTR信息，找到und回滚日志，实现回滚。

以上流程被称之为InnoDB的核心特性：自动故障恢复（Crash Recovery）。先前滚再回滚（先应用redo再应用undo）

undo保证了ACID那些特性？
主要保证事务的A的特性，同时C和I的特性也有关系。

3.事务中的C特性怎么保证?
InnoDB crash recovery：数据库意外宕机时刻，通过redo前滚+undo回滚（未提交的进行回滚）保证完整数据的最终一致。

InnoDB doublewrite buffer：一段连续的存储空间，默认存储在ibdataN中。解决数据页写入不完整，保证原始数据页也是完整的。脏页写到一半时，发生问题,用到了dw buffer

MySQL官网说明
https://dev.mysql.com/doc/refman/5.7/en/innodb-doublewrite-buffer.html

mysqld process crash in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery

从内存innnodb_buffer_pool中写入到磁盘的DWB时，每次1M，分两次。（DWB共2M连续的数据空间）
—>相当于磁盘备份了内存中的数据页

正常情况：
只有将（2M）完整数据写入doublewriter buffer后，innnodb_buffer_pool中的数据才会往ibd文件中物理存储。物理存储完毕后，DWB才会被buffer pool的下个存储的数据访问到。

极端情况：
如果在数据页写盘时发生操作系统、存储系统、或者myql进程中断，Innodb可以从doublewriter buffer存储中找回丢失的数据页备份。

2M不够用？
一个数据页 16K 单位数据页如果落盘不完整，才算原数据损坏脏页完整数据量没完全落盘不算损坏所以DW大小2M完全可以保证。

事务中I的特性怎么保证？
隔离级别：读隔离性
RU：脏读（内存中未提交的数据）、不可重复读、幻读
RC: 不可重复读、幻读。
RR: 有可能会出现幻读。
SR: 事务串行执行。 ---->不需要隔离

具体演示可以参考：简单说几个MySQL高频面试题（6.讲下 MySQL 事务的特性及隔离级别）

锁机制：写隔离性 innodb—>行级锁
作用：保护并发访问资源。不能两个一起操作

保护的资源分类：
latch（闩锁）: rwlock、 mutex, 主要保护内存资源
MDL（Metadata_lock）：元数据锁 DDL操作
FTWRL：innodb表在mysqldump、PXB备份时，备份frm文件时产生的全局锁
table_lock: 保护表级别

lock table t1 read;
lock table t1 write;

        row_lock:	innodb默认的锁粒度，加锁方式都是在索引上加锁的；

record lock： 记录锁，在聚簇索引上锁定      RC级别只有record lock
gap lock：    间隙锁，在辅助索引间隙加锁    RR级别存在，防止幻读
next lock：   下一键锁，gap+record锁       RR级别存在，防止幻读
MVCC:	多版本并发控制

    乐观锁：乐观。
    悲观锁：悲观。

每个事务操作都要经历两个阶段：
读: 乐观锁。
MVCC利用乐观锁机制，实现非锁定读取。
read view：RV(版本号集合)

RC级别下不可重读现象演示：

准备环境:
vim /etc/my.cnf
#添加隔离级别参数：
transaction_isolation=READ-COMMITTED

/etc/init.d/mysqld restart

打开两个会话窗口：sessionA    sessionB

第一步：确认隔离级别是否生效    sessionA    sessionB
select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

第三步：建库建表，添加数据    sessionA
create database test charset utf8mb4;
use test;
create table t1 (id int primary key auto_increment,num int not null , name varchar(20) not null);
insert into t1(num,name) values(1,'a'),(3,'c'),(6,'d'),(7,'x');
insert into t1(num,name) values(11,'a'),(23,'c'),(36,'d'),(37,'x');
commit;
select * from t1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | a    |
|  2 |   3 | c    |
|  3 |   6 | d    |
|  4 |   7 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
+----+-----+------+
8 rows in set (0.00 sec)

第四步：sessionB查看t1表数据一致
use test;
select * from test.t1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | a    |
|  2 |   3 | c    |
|  3 |   6 | d    |
|  4 |   7 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
+----+-----+------+
8 rows in set (0.00 sec)

第五步：sessionA
begin;

第六步：sessionB
begin;
select * from test.t1 where id=1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | a    |
+----+-----+------+

第七步：sessionA
update t1 set name='aa' where id=1;
commit;

第八步：sessionB
select * from t1 where id=1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | aa   |
+----+-----+------+

RC级别下幻读现象演示：

准备工作：
sessionA  sessionB  :
use test;
show tables;

第一步：sessionA  sessionB  两边此时一模一样
begin;
select * from t1;
mysql> select * from t1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   1 | aa   |
|  2 |   3 | c    |
|  3 |   6 | d    |
|  4 |   7 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
+----+-----+------+
8 rows in set (0.00 sec)

第二步：sessionA 把num小于10的全部update10
update t1 set num=10 where num<10;        # RC级别此时说明只对四行数据进行加锁，中间的范围并没有加锁。
select * from t1;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |  10 | aa   |
|  2 |  10 | c    |
|  3 |  10 | d    |
|  4 |  10 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
+----+-----+------+
8 rows in set (0.00 sec)

第三步：sessionB 此时插入了一条数据
insert into t1(num,name) values(5,'aaa');
commit;

第四步：sessionA 进行提交
commit;
select * from t1;    查询到了幻行。
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |  10 | aa   |
|  2 |  10 | c    |
|  3 |  10 | d    |
|  4 |  10 | x    |
|  5 |  11 | a    |
|  6 |  23 | c    |
|  7 |  36 | d    |
|  8 |  37 | x    |
|  9 |   5 | aaa  |
+----+-----+------+
9 rows in set (0.00 sec)

RR级别如何防止幻读：

trx1 : 第一个查询时， 生成global consitence snapshot  RV-CS1(10:00),一直伴随着事务生命周期结束。
trx2 : 第一个查询时， 生成global consitence snapshot  RV-CS2(10:01),一直伴随着事务生命周期结束。

快照技术由undo log来提供。

总结MVCC的不同级别下的读和写：

MVCC采用乐观锁机制，实现非锁定读取。
在RC级别下，事务中可以立即读取到其他事务commit过的readview
在RR级别下，事务中从第一次查询开始，生成一个一致性readview，直到事务结束。

更多精彩内容欢迎关注微信公众号
在这里插入图片描述

oldba.cn

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
浅谈MySQL事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 insert,update,delete 语句一般来说，事务是必须满足4个条件（ACID）：原子性（Atomic
复制链接

扫一扫