MySQL事务

MySQL事务

MySQL事务机制主要用于处理操作量大、复杂度高的数据

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

  • 事务处理可以用来维护数据的完整性,保证多条SQL语句要么全部执行,要么全部不执行

  • 事务用于管理insert、update和delete之类的DML语句,[select语句],其它类型的SQL语句没有事务的概念

概述事务

事务必须满足ACID4个条件:A原子性、C一致性、I隔离性、D持久性

  • 原子性:一个事务中的所有操作要么全部完成、要不一个都不做,不会结束在中间某个环节

  • 一致性:事务执行结束后数据库的完整性没有破坏

  • 隔离性:数据库允许多个并发事务对数据库中的数据进行读写操作,隔离性可以防止多个事务并发执行时导致的数据不一致性。事务根据隔离等级可以分为4级:读未提交、读已提交、可重复读和串行化

  • 持久性:事务执行完成后对数据的修改就是永久的

在MySQL命令行的默认设置下,事务都是自动提交的。如果需要使用事务则需要显式的开启事务start transaction或者begin,或者执行命令set autocommit=0,用来禁止使用当前会话的自动提交

事务与数据库底层数据

事务的进行过程中,在未结束之前,DML语句并不会直接更改底层数据,只是将历史操作记录一下,在内存中完成记录。只有在事务结束时,而且应该是成功结束时,才会修改底层硬盘文件中的数据

  • 事务的原子性是通过undo log来实现

  • 事务的持久性是通过redo log来实现

  • 事务的隔离性是通过【读写锁+MVCC多版本并发控制】来实现的

  • 事务的一致性是通过原子性、持久性和隔离性来实现的

事务控制语句

  • begin或者 start transaction可以显式的开启一个事务,结束事务有提交和回滚2种方式

  • commit提交事务,并使已执行的对数据库的所有修改成为永久性修改

  • rollback回滚结束事务,撤销已经执行的未提交的修改操作

  • savepoint 标识名称 用于在事务过程种创建一个保存点,从而支持部分回滚。一个事务中可以添加多个保存点

  • release savepoint 标识名 用于删除一个事务的保存点,如果对应名称的保存时不存在则抛出异常

  • rollback to 标识名 将事务回滚到指定的保存点,执行名称的保存点到当前位置的所有操作撤销,但是保存点之前的操作仍旧保留,等待事务结束

  • set transaction isolation level用于设置事务的隔离性,innodb存储引擎提供的隔离性有读未提交read uncommitted、读已提交read committed、可重复读repeatable read和serializable串行化,系统默认隔离等级为可重复读

事务处理

begin开启事务 rollback事务回滚 commit事务提交

还可以使用set改变MySQL的自动提交模式

  • set autocommit=0 禁止自动提交

  • set autocommit=1 开启自动提交

基本测试

在navicat或者命令行中开启两个窗口模拟两个并发修改数据库的进程

1、创建数据库 create database test1;

2、切换当前库 use test1;

3、创建测试使用的表 create table t1(id int primary key,name varchar(32))

engine=innodb;

MySQL8默认数据库的存储引擎就是innodb

4、开启事务: begin;

5、插入操作:insert into t1 values(1,'zhangsan');,在另外一个窗口中执行查询,则看不到插入的数据,因为事务的默认隔离等级为可重复读

6、提交事务 commit; 修改生效,另外一个窗口中则能够查询到数据

7、如果没有提交还可以使用rollback撤消修改操作

多点回滚

begin;

update t1 set name='modify1' where id=1;
select * from t1; 

savepoint s1; 

delete from t1; 

rollback to s1; 
select * from t1;

commit;

相关日志问题

  • 事务的原子性是通过undo log来实现

  • 事务的持久性是通过redo log来实现

redo log

如果每次读写数据都需要磁盘的IO,效率会很低。innodb提供了缓存buffer pool作为访问数据库的缓存,读取和修改操作都会涉及到缓存的操作,缓存会定期刷新到磁盘中,但是写入缓存的数据在系统宕机时会丢失,事务的持久性则无法保证。每次读写硬盘数据的IO成本太高,为了解决这个问题,引入了redo log来提升更新数据的执行效率。

当事务提交时,先将redo log buffer写入redo log文件进行持久化,待事务commit操作完成时才算完成。这种作为成为预先日志持久化write-ahead log。在持久化一个数据页之前,先将内存中相应的日志页持久化。当有一条数据需要更新时,innodb会将记录写入到redo log中,并更新内存,这时更新就算完成。innodb会在适当的时候,例如系统空闲时,才真正将操作记录更新到磁盘。如果在数据落盘之前系统宕机,数据库重启后,可以通过日志来保证数据的完整性.

undo log

undo log提供了两个作用:提供回滚和多版本控制MVCC

在数据修改时不仅记录redo,还记录了相对应的undo。undo log主要记录的是数据的逻辑变化,为了在发生错误时回滚之前的所有操作。

undo日志用于将数据库逻辑的恢复到原来的样子,所以实际上记录的时相反的工作。例如insert对应的是delete。undo日志用于事务的回滚操作,进而保证了事务的原子性

隔离级别

数据库重要的功能就是实现数据共享,对于同时运行的多个事务,当多个事务同时访问数据库中相同的数据时,如果没有采取必要的隔离机制,则会导致出现各种并发问题。

问题的本质就是共享数据的线程安全问题

常见问题

1、第一类丢失更新:A事务回滚时把已经提交的B事务更新的数据覆盖了。解决方案是锁机制

2、脏读:A事务读取到B事务更新但是还没有提供的数据,如果B回滚撤销,则A读取的数据就是临时而且无效的数据。

3、不可重复读:A事务读取到了一个字段值,但是B更新并提交了该字段的修改,A再次读取同一个字段值,但是两次读取到的内容不一致

4、幻读:A事务从一个表中读取了多行数据,但是B事务插入或者删除了一些新的行,如果A再次读取,则发现数据会有多出来或者少掉的行

5、第二类丢失更新:A事务修改记录,同时B事务修改记录,B提交数据后使用B的修改结果覆盖了事务A的修改结果

事务隔离性

数据库系统必须具有隔离并发各个事务的能力,使其相互不会影响,避免各种并发问题。一个事务和其它事务隔离的程度就成为隔离等级。数据库中规定了多种事务隔离级别。不同的隔离级别对应不同的干扰程度,隔离级别越到,数据的一致性就越号,但是并发性越差

MySQL数据库支持4种隔离级别,默认可重复读

隔离级别的范围

隔离级别的作用范围可以分为全局级和会话级两种。全局级对所有的会话有效,会话级只对当前会话有效

设置全局隔离等级 set global transaction isolation level read committed;

设置会话级隔离等级 set session transaction isolation level read uncommitted;

总结隔离等级
隔离级别描述
Read-Uncommitted允许事务读取其它事务没有提交的数据,脏读、不可重复读和幻读问题都会出现
Read-Committed只允许事务读取其它事务已经提交的数据,可以避免脏读,但是不可重复读和幻读问题都会出现
Repeatable-Read可以保证多次从一个字段中读取相同的数据,可以认为事务开启时会自动对现有数据进行快照,其它事务修改不管是否提交,当前事务读取的时快照数据,可以避免脏读和不可重复读,但是幻读问题会出现。快照是MVCC多版本并发控制
Serializable可以确保事务是串行执行,可以避免所有的并发问题,但是由于性能低下,一般不使用

在具体应用开发中,一般建议使用数据库管理系统默认的隔离等级,同时在编程中引入乐观锁

样例:两个事务同时操作tb_users表中的age值

读未提交

MySQL数据库中事务的隔离实际上是依靠锁机制来实现的,但是加锁会带来性能的损失。读未提交隔离等级是不加锁的,所以性能最好,但是由于基本没有什么限制,所以脏读问题都无法解决。

读已提交

解决脏读问题的方法就是只允许读取别的事务已经提交的数据,其它事务未提交数据当前事务不能读取。例如oracle默认的事务隔离级别就是读已提交。由于只能读取已经提交的数据,所以可能出现两次读取的数据不一致

可重复读

针对不可重复读的问题提出了可重复读的隔离等级,针对查询采用了MVCC多版本并发控制引入快照机制,每个事务都有自己的数据快照,即使其它事务提交数据,也不影响当前事务相关行的数据快照。幻读仍旧会出现

为了解决不可重复读的问题,MySQL采用了MVCC多版本并发控制的方式。数据库中的一行记录实际上有多个版本,每个版本除了有数据之外,还有一个标识版本的字段row trx_id,这个字段就是产生的对应事务的id,在事务开始的时候向事务系统申请,按照时间先后顺序递增

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NnIcz5mu-1654399757230)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20220605111216702.png)]

一行记录现在有3个不同的版本,每个版本中都记录了使其产生的事务id,每个数据存储的备份就是快照【一致性视图】,可重读读就是在事务开始的时候生成一个当前事务的全局性的快照,但是读提交则是每次执行语句时都会重新生成一次快照。

读取快照数据的规则:

  • 版本未提交不能读取

  • 版本已经提交,但是却是在快照创建后提交的,不能读取

  • 版本已经提交,且是在快照创建前提交的,则可以读取

可重复读和读已提交两者主要的区别在于快照的创建上,可重复读仅仅在事务开始时创建一次,而读已提交每次执行sql语句时都要创建一次

串行化

隔离等级最高,隔离效果最好,可以解决脏读、不可重复度和幻读问题,当时并发性最差。将事务的并发执行转换为顺序执行,后一个事务的执行必须等待前一个事务结束

并发写问题

事务A执行update操作,update时要对所修改的数据行进行加锁,这个行所在事务提交后才能释放,而在事务A提交之前,如果事务B也希望修改这行数据,必须先进行行锁的申请,但是由于A已经占用了行锁,所以B申请不到,此时B一直会处于等待状态,直到A提交释放锁后,B才能执行

update tb_users set age=10 where id=1;

id就是主键PK,是有索引的,那么MySQL在索引树种查找到这行数据,然后加上行锁

update tb_users set age=20 where age=10;

假设表种并没有针对age设置索引,所以MySQL无法直接定位这行数据。MySQL会给这个表种的所有行

加锁,但是添加行锁后,MySQL会再次执行一次过滤,发现不满足条件的行就释放锁,最后只留下符合

条件的行。但是一次锁定一次解锁的过程对性能影响比较大,如果是大表的化,还是建议合理设计索引

幻读问题

解决并发问题的方案就是行锁,解决幻读也是依赖于锁机制实现,使用间隙锁。MySQL把行锁和间隙锁合并在一起,就可以解决并发写和缓读问题,这个锁叫做next-key锁

例如: select * from tb_student 可以获取age=10age=30的数据,针对索引数据库会创建维护一个B+树,树可以用来快速定位行记录

针对具体的行数据,例如age=10age=30的数据,添加一个行锁,根据age=10age=30可以将整个区间划分为3部分,(负无穷大,10]、(10,30)和[30,正无穷大)三个部分,在这3个区间上可以添加间隙锁

事务A事务B
beginbegin
select * from tb_users
update tb_users set name=‘zhangsan’ where age=10
insert into tb_users values(null,‘lisi’,10);
select * from tb_users where age=10解决幻读问题
commitcommit

在事务A提交之前,事务B的插入操作只能等待,这实际上就是间隙锁生效。

  • 如果表中有索引,实际上直接可以使用行锁,如果不是索引列,那么数据库会为整个表加上间隙锁。

  • MySQL的innodb引擎才能支持事务,其中可重复读是MySQL默认的隔离级别

  • 读未提交和串行化基本上不需要考虑隔离级别,因为读未提交不加锁限制;串行化相当于单线程执行,效率太差

  • 读已提交解决了脏读问题,行锁解决了并发更新问题,并且MySQL在可重复读时引入行锁+间隙锁的组合可以实现可重复读

悲观锁和乐观锁

悲观锁:获取数据时都会直接加锁,共享资源每次只给一个线程使用,其它线程阻塞等待。在数据库中提供了行锁、表锁等,操作数据时先加锁后使用。例如售票系统 select * from ticket where id=100 for update

乐观锁:不是数据库系统自带的,需要开发实现。乐观锁是只操作数据时并不进行任何特殊处理,也就是不加锁,在进行更新时才进行冲突判断

在数据表中添加一个额外列:version数据版本号或者使用时间戳timestamp,每次修改数据则版号加1

  • update tb_users set age=80,version=2 where id=5 and version=1 第一次读取数据的版本号为1,修改时条件为version=1的那条数据。如果中间有事务已经修改了数据,则版本号绝对不是1,所以该语句执行返回结果为0

  • 如果执行结果为0,则重新读取数据,重新执行修改操作

JDBC事务实现

使用JDBC连接mysql默认每一个连接是自动提交事务的。如果需要使用JDBC执行多条语句,并要求组成一个事务一起执行的话

1、在执行之前关闭自动提交,设置手动提交事务Connection的对象.setAutoCommit(false)

2、如果执行成功,手动提交事务Connection的对象.commit();

3、如果执行过程中出现异常,则手动回滚撤销操作Connection的对象.rollback();

4、补充说明:希望养成习惯,在关闭Connection的对象之前,把连接对象设置回自动提交,Connection的对象.setAutoCommit(true)

因为实际开发中,每次获取的连接,不一定是新的连接,而是从连接池中获取的旧的连接,而且关闭也不是真关闭,而是还给连接池,供别人接着用。以防别人拿到后,以为是自动提交的,而没有commit,最终数据没有成功。

一般涉及到事务处理的话,那么业务逻辑都会比较复杂。例如购物车结算时,1)在订单表中添加一条记录。2)在订单明细表中添加多条订单明细的记录,表示该订单买了什么东西。3)修改商品表的销量和库存量。

用两条修改语句来模拟组成一个简单的事务。

  • update t_department set description = ‘xx’ where did = 2;

  • update t_department set description = ‘yy’ where did = 3;

希望这两个语句要么一起成功,要么一起回滚。为了制造失败,可以故意把第二条语句写错 update t_department set description = ‘yy’ (少了where) did = 3;

Connection conn = null; 
try {
    Class.forName("com.mysql.cj.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql:///test? serverTimezone=UTC", "root", "123456"); 
    // 默认情况下单语句单事务,如果需要手动定义事务范围,则需要关系自动提交 
    conn.setAutoCommit(false);
    PreparedStatement ps = conn.prepareStatement("insert into t1 values(?,?)");
    ps.setInt(1, 125);
    ps.setString(2, "xxx5"); 
    int len = ps.executeUpdate();
    ps.setObject(1, "tttt");//数据类型错误 
    ps.setString(2, "66666"); 
    ps.executeUpdate();
    conn.commit();// 提交事务
} catch (Exception e) { 
    if (conn != null) 
        conn.rollback();//回滚撤销事务
    System.out.println(e.getMessage()); 
} finally {
    //如果使用直连方式,conn是否恢复原来的提交方式都没有关系;如果使用连接池则必须恢复原来的自 动提交方式 
    if (conn != null)
        conn.close(); 
}

分区处理

一般情况下创建的表对应一组存储文件,当数据量较大时MySQL的性能就开始下降

解决方案:如果数据表中的数据具有特定业务含义数据的特性,可以将表中数据分散到多个存储文件中,以保证单个文件的执行效率。

最常见的分文件的方法是按照id值进行分区,不同的分区对应不同的存储问题。采用id的hash值进行分区,实际上就是对10进行取模,可以将数据均匀的分散到10个文件中

create table tb_article( 
    id int primary key,
    title varchar(32), 
    content mediumtext 
) partition by hash(id) partitions 10;-- 按照id的hash值进行分区,总共分为10个区

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GUXLI2R7-1654399757232)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20220605112310220.png)]

服务器端的表分区对客户端都是透明的,客户端还是照常插入数据,但是服务器端会按照设定的分区算法分散存储数据

PreparedStatement ps = conn.prepareStatement("insert into tb_article values(?,?,?)"); 
for (int i = 0; i < 10000; i++) { 
    ps.setInt(1, i+1); 
    ps.setString(2, i + "_name");
    ps.setString(3, i+"_content");
    ps.executeUpdate();
}

表分区的用途

  • 逻辑数据分割

  • 提高单一的写入或者读取的应用速度

  • 提高分区范围查询的速度

  • 分割数据能够有多个不同的物理文件路径

  • 高效的保存历史数据

分区算法

MySQL支持的常见分区类型有Range、List、Hash、key分区,其中range最为常见

  • Range范围:允许将数据划分到不同的范围,例如可以将一个表通过年份划分成若干个分区

  • List预定义列表:允许系统通过预先定义的列表的值将数据进行分割

  • hash哈希:允许通过对表中的一个或者多个列的hash key进行计算,最后通过这个hash码将数据对应到不同的分区

  • key键值:是hash分区的而一种扩展,这里的hash key是由mysql系统产生的

  • 复合模式:多种模式的组合使用,例如对已经进行了range分区的表上,对其中的分区再次进行hash分区

指定分区中的列名称时需要使用主键列或者主键中的一部分,否则设置失败

hash哈希分区

一般用于不按照业务规则进行数据文件的均匀拆分,输出的结果和输入是否有规律无关,仅适用于整形字段

create table tb_emp( 
    id int primary key auto_increment, 
    name varchar(32) not null, 
    hiredate date default '1989-2-3'
)partition by hash(id) partitions 4;

一般要求hash中的值最好有一定的线性关系,否则分区数据将不能均匀分布。

关键字分区key

key用于处理字符串,比hash()多一步从字符串中计算出一个整数,然后再进行取模计算

create table tb_article(
    id int auto_increment, 
    title varchar(64) comment '文章标题', 
    content text, 
    primary key(id,title) 
)partition by key(title) partitions 10;

范围分区****range

range是按照一种指定数据的大小范围进行分区,例如按照文章的发布时间将数据分区存放

获取时间戳 select unix_timestamp('2022-4-30 23:59:59 ') 1651334399

select unix_timestamp('2022-3-31 23:59:59 ') 1648742399

create table tb_article(
    id int auto_increment,
    title varchar(32) not null,
    pub_date int, primary key(id,pub_date) 
) partition by range(pub_date)(
    -- 2022年3月和以前的数据 
    partition p202203 values less than (1648742399),
    -- 2022年4月的数据 
    partition p202204 values less than (1651334399),
    -- 2022年4月以后的数据
    partition p202205 values less than maxvalue 
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YpOEYCD0-1654399757232)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20220605112745213.png)]

其中maxvalue表示最大值。MySQL允许在分区键中使用null,分区键允许是一个字段,也可以是一个表达式。一般MySQL的分区会把null当作0或者最小值进行处理。需要注意:range中null当作最小值;list中null必须出现在枚举列表中,否则不作处理;hash或者key分区中null被当作0值处理

条件运算符只能使用less than,所以要求小值在前

列表分区

列表分区也是一种条件分区,使用列表值进行分区。列表值应该是离散值,而范围分区是连续值

create table tb_artitle(
    id int auto_increment, 
    title varchar(32) not null 
    status tinyint(1), -- 用于表示文章的状态,例如0草稿、1完成未发布、2已发布、3下架 
    primary key(id,status)
)partition by list(status)( 
    partition writing values in (1,0), -- 表示正在写的文章
    partition publishing values in(2,3) -- 表示已经完成的文章 
);
  • 2
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值