首页 > 数据库 >MySQL事务

MySQL事务

时间：2025-01-14 12:57:26浏览次数：3  
标签：事务 buffer t1 MySQL 磁盘 日志 select

事务的ACID特性

Atomic（原子性）

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

Consistent（一致性）

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

Isolated（隔离性）

事务之间不相互影响。

Durable（持久性）

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

事务的生命周期（标准的事务控制语句）

1)如何开启事务

begin;

2)标准的事务语句

DML :

insert

update

delete

3)事务的结束

提交：

commit;

回滚：

rollback;

自动提交机制(autocommit)

select @@autocommit;

在线修改参数：

1)会话级别

创建所需数据库和表并写入内容：

create database lkk default charset utf8mb4 collate utf8mb4_bin;

use lkk;

create table t1 (id int,name varchar(20));

insert into t1(id,name) values(1,'张三');

select * from t1;

临时关闭自动提交

set autocommit=0;

及时生效，只影响当前登录会话

select @@autocommit;

修改表中内容：

update t1 set name='李四' where id=1;

select * from t1;

复制当前会话：

use lkk;

select * from t1;

在第一个会话提交：

commit;

在另一个会话再次查看：

select * from t1;

回滚

修改表中内容：

update t1 set name='王五' where id=1;

select * from t1;

数据回滚：

rollback;

select * from t1;

2)全局级别

set global autocommit=0;

断开窗口重连后生效，影响到所有新开的会话

(3)永久修改(重启生效)

修改配置文件

vim /etc/my.cnf

添加：

autocommit=0

隐式提交的情况

触发隐式提交的语句：

begin

a

b

create database

导致提交的非事务语句：

DDL语句：（ALTER、CREATE 和 DROP）

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

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

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

TRUNCATE TABLE

LOAD DATA INFILE

SELECT FOR UPDATE

事务的ACID如何保证?

  • 概念名词

redo log：重做日志

ib_logfile0~1：默认50M，轮询使用

redo log buffer：redo内存区域

ibd：存储数据行和索引

data buffer pool：缓冲区池，数据和索引的缓冲

LSN：日志序列号

ibd、redolog、data buffer pool、redo buffer

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

WAL (持久化)：

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

日志是优先于数据写入磁盘的

脏页：

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

CKPT：

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

TXID：

事务号，InnoDB会为每一个事务生成一个事务号，伴随着整个事务

事务日志-- redo 重做日志

作用：

主要功能保证D，A、C也有一定得作用

记录了内存数据页的变化

提供快速的持久化功能(WAL)

CSR过程中实现前滚的操作（磁盘数据页和redo日志LSN一致）

redo日志位置

cd /usr/local/mysql/data/

redo的日志文件：

redo buffer

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

redo的刷写策略

commit;

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

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

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的"前滚操作"

  • undo（回滚日志）

作用：

在 ACID特性中，主要保证A的特性，同时对CI也有一定功效

  1. 记录了数据修改之前的状态
  2. rollback 将内存的数据修改恢复到修改之前
  3. 在CSR中实现未提交数据的回滚操作
  4. 实现一致性快照,配合隔离级别保证MVCC，读和写的操作不会互相阻塞
  • 锁（为了防止出现脏读、幻读、不可重复读）

读锁（共享锁）：我读的时候，其他人不能改

写锁（排他锁）：我写的时候，其他不能改，也不能读

写锁又分为:

row-level lock（行锁）：锁行

gap lock（间隙锁）：锁区域、锁范围

next-key lock（临键锁）：行锁+间隙锁

  • 隔离级别

RU（读未提交）：可脏读、幻读，一般不用

RC（读已提交）：可能出现幻读，可以防止脏读(高并发网站为了快速读写，会使用RC级别)

RR（可重复读）：防止"幻读"现象，利用的是undo的快照技术+GAP(间隙锁)+NextLock(临键锁) （默认级别）

SR（可串行化）：可以防止死锁，但是并发事务性能较差 （独占进程，排队进行各项事务）

transaction_isolation=read-uncommitted#隔离级别最低，并发性能高

transaction_isolation=read-committed#锁定正在读取的行

transaction_isolation=REPEATABLE-READ#锁定所读取的所有行

MVCC ---> undo快照

MVCC(Multiversion Concurrency Control)多版本并发控制。即通过数据行的多个版本管理来实现数据库的并发控制，使得在InnoDB事务隔离级别下执行一致性读操作有了保障。

RU会出现脏读

RC会出现不可重复读，也会出现幻读

RR通过MVCC基础解决了不可重复读，但是有可能会出现幻读现象

在RR模式下，GAP和Next-lock进行避免幻读现象，必须索引支持

查看隔离级别：

select @@tx_isolation;

脏读、幻读、不可重复读的区别：

  1. 脏读：是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问这个数据，然后使用了这个数据
  2. 不可重复读：是指在一个事务内多次读同一数据，在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读
  3. 幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。 同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样

不可重复读的重点是修改：

同一事务，两次读取到的数据不一样。

幻读的重点在于新增或者删除：

同样的条件，第 1 次和第 2 次读出来的记录数不一样

脏读：

强调的是第二个事务读到的不够新

验证隔离级别

创建库和表并写入数据

create database world;

use world;

create table t1 (id int primary key not null,name varchar(20));

insert into t1 values (1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'maliu'),(5,'zhuqi'),(6,'shenba'),(7,'gengjiu');

1)修改隔离级别为RU，验证脏读

vim /etc/my.cnf

添加：

transaction_isolation=read-uncommitted

autocommit=0

重启MySQL

systemctl restart mysqld

查看当前隔离级别：

select @@tx_isolation;

use world;

select * from t1;

复制当前会话

第一个会话：

update t1 set name='hehe' where id=1;

select * from t1;

第二个会话：

use world;

select * from t1;

能看到未提交（commit）之前的数据就是脏读（未提交就可以读到修改后的数据）

2)修改隔离级别为RC， （避免脏读）验证不可重复读

vim /etc/my.cnf

修改：

transaction_isolation=read-committed

重启MySQL

systemctl restart mysqld

第一个会话：

use world;

select * from t1;

update t1 set name='haha' where id>3;

第二个会话：

insert into t1 values (8,'zhangsan'),(9,'lisi'),(10,'wangwu');

commit;

select * from t1;

第一个会话：

commit;

select * from t1;

第二个会话先提交，发现最终查看结果不一样，幻读

3)修改隔离级别为RR，避免脏读、不可重复读、可能会导致幻读

vim /etc/my.cnf

修改：

transaction_isolation=repeatable-read

重启MySQL

systemctl restart mysqld

第一个会话：

use world;

select * from t1;

update t1 set name='lkk' where id=1;

第二个会话：

insert into world.t1 values(14,'a'),(15,'b'),(16,'c');

发现数据无法写入，然后报错提示锁等待超时。

InnoDB核心参数的介绍

  • 存储引擎默认设置

default_storage_engine=innodb

  • 启用独立表空间

innodb_file_per_table=1

  • 共享表空间文件个数和大小

innodb_data_file_path=ibdata1:512M:autoextend

Innodb_flush_method=(O_DIRECT, fsync)

作用: 控制的是 Redo buffer  和 buffer pool

fsync    :

O_DIRECT :

O_DSYNC  :

  • 最高安全模式

innodb_flush_log_at_trx_commit=1

innodb_flush_method=O_DIRECT

常见的innodb_flush_method取值有以下几种：

fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。

O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成

O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲

  • 最高性能

innodb_flush_log_at_trx_commit=0

innodb_flush_method=fsync

redo日志设置有关的

show variables like "innodb_log_buffer_size";  ——查看当前日志缓冲区大小

vim /etc/my.cnf

添加：

innodb_log_buffer_size=16777216    ——内存日志缓冲区大小

innodb_log_file_size=50331648   ——磁盘日志文件大小（重做日志）

innodb_log_files_in_group = 2  ——磁盘日志文件文件个数（重做日志）

innodb_buffer_pool_size    ——设置缓冲池的大小

innodb_buffer_pool_instances   ——设置缓冲池的数量

脏页刷写策略

innodb_max_dirty_pages_pct=25  （25%）

还有哪些机制会触发写磁盘?（从内存写入磁盘）

CSR （比LSN,前滚）

redo满了

标签：事务,buffer,t1,MySQL,磁盘,日志,select
From： https://blog.csdn.net/2402_88627342/article/details/145055757

相关文章

  • pbootcms数据库sqlite转mysql操作流程
    操作并不复杂，如果没有基础，操作之前一定要备份，避免数据损坏第一步：用SQLiteStudio打开db数据库，并导出sql文件第二步：使用专用工具，将导出的sql文件进行处理。第三步：将文件通过数据库管理工具导入第四步：修改数据库配置文件，根据自己IDE数据库信息调整就行。第五步：登录网站......
  • mysql使用count()执行select报错：ERROR 1140 (42000) In aggregated query without GRO
    1原因mysql的sql_mode默认开启了only_full_group_by模式2解决办法2.1命令解决（临时生效）查看sql_modeshowvariableslike'%sql_mode';showsessionvariableslike'%sql_mode';showglobalvariableslike'%sql_mode';修改sql_modesetglobalsql_......
  • 详细分析Mysql中的SQL_MODE基本知识
     一、基础知识sql_mode是MySQL中用于设置sql语法和行为的系统变量；控制MySQL的sql解析和执行的方式，使其与sql标准或其他数据库系统的行为一致，通过设置sql_mode，可以改变MySQL处理待定sql操作的方式。MySQL5.7默认sql_mode包括（7个）：1）ONLY_FULL_GROUP_BY；......
  • MySQL之DDL语言
    目录一、数据库的基本操作1、创建数据库语法：示例：2、修改数据库语法：示例：3、删除数据库语法：示例：4、查询数据库语法：5、使用数据库语法：二、数据表的基本操作1、创建数据表语法：示例：2、重命名数据表语法：示例：3、删除数据表语法：示例：4、查询数据表语法：三、表......
  • 2025毕设springboot 高校班级事务管理系统论文+源码
    系统程序文件列表开题报告内容研究背景随着信息技术的迅猛发展和教育信息化的不断推进，高校班级事务管理正逐步从传统的人工模式向数字化、智能化方向转型。在传统管理模式下，班级信息的传递、文件的存储与分享、任务的分配与跟踪等往往依赖于纸质文档和口头通知，这不仅效率低......
  • MySQL（高级特性篇） 03 章——用户与权限管理
    一、用户管理MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限。普通用户只拥有被授予的各种权限MySQL提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、......
  • 管理实践-基于vika+钉钉机器人辅助进行事务管理
    一、先看最终实现的效果通过维格表（vika）将相关信息同步发送至钉钉项目群。二、正文本次的实践，是将维格表做为消息分发的主体，通过维格的【机器人】功能模块基于WebHook进行主动推送信息的能力，如下图：三、针对的痛点（为什么要做这件事？）1.首先是我们日常都比较重视的问......
  • java项目之教师工作量管理系统(ssm+mybatis+mysql)
    风定落花生，歌声逐流水，大家好我是风歌，混迹在java圈的码农一枚。今天要和大家聊的是一款基于ssm的教师工作量管理系统。项目源码以及部署相关请联系风歌，文末附上联系信息。项目简介：教师工作量管理系统的主要使用者管理员可以管理教师，可以对教师信息修改删除以及查询操作；可以查......
  • mysql、oracle、sqlserver的区别
    一、保存数据的持久性：        MySQL：是在数据库更新或者重启，则会丢失数据。                Oracle：把提交的sql操作线写入了在线联机日志文件中，保持到了磁盘上，可以随时恢复。                SqlServer：2014之后，也拥有了完全持久和延......
  • MySQL中的四种表联结
    目录1、联结、关系表（1）关系表（2）为什么使用联结2、如何创建联结（1）笛卡尔积（叉联结）--用逗号分隔（2）where子句的重要性（3）内联结--INNERJOIN （4）联结多个表3、使用表别名（对比列别名）4、自联结、自然联结、外联结（1）自联结：self-join（2）自然联结：naturaljoin（3）外联结：leftjoin、righ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99