MySQL性能优化(四)事务

MySQL性能优化(四)事务

1.MySQL事务

1.1 事务的简介

1.1.1 为什么需要事务

现在的很多软件都是多用户,多程序,多线程的,对同一个表可能同时有很多人在用,为保持数据的一致性,所以提出了事务的概念。

A给B转账,A的余额-1000元, B的余额就要+1000元,这两个update语句必须作为一个整体来执行,不然如果出现A账户扣钱了但是B没有加钱这种情况很难处理。

1.1.2 什么存储引擎支持事务

  1. 查看数据库下面的存储引擎是否支持事务?
show engines;

image.png 只有Innodb存储引擎是支持事务的

  1. 查看mysql当前默认的存储引擎?
  SHOW VARIABLES LIKE '%storage_engine%'

image.png

  1. 查看某张表的存储引擎?
  show create table 表名 ;
  1. 修改表的存储引擎?
Create table .... type=InnoDB; //创建表的时候指明存储引擎
Alter table table_name type=InnoDB; //表创建后 修改存储引擎

1.2 事务的特性

  • 事务应该具有4个属性:原子性一致性隔离性持久性。这四个属性通常称为ACID特性。

  • 原子性(atomicity)。一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。

  • 一致性(consistency)。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

  • 隔离性(isolation)。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。

  • 持久性(durability)。持久性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

1.2.1 原子性(atomicity)

一个事务必须被视为一个不可分割的最小单元,整个事务中的所有操作要么全部提交成功,要么全部失败,对于一个事务来说,不可能只执行其中的一部分操作

比如: 小明使用支付宝给amos转账,可以简单理解为两个步骤:

  1. 小明支付宝扣除500元
  2. amos支付宝增加500元

不能出现小明支付宝已经扣除了500元,但amos的支付宝余额没有增加500元的情况。也就是说转账的这两个步骤要么全部成功,要么全部失败,不能存在第三种状态

image.png

1.2.2 一致性(consistency)

一致性是指事务将数据库从一种一致性转换到另外一种一致性状态,在事务开始之前和事务结束之后数据库中数据的完整性没有被破坏

假设我们10个人,每人有一个账号,里面有钱,可以转来转去,这组成了一个小型的数据系统,那么什么叫数据一致性?这是由你自己来定义的,比较通用的就是:这10个人的账号金额总数不变——满足这一条件,就叫数据一致,不满足,就叫数据不一致,或者在分布式的环境下,有一个数据在几个地方都保存了,那么任何时候,这几个地方的数据都必须相同,这也叫一致性。

一致性关注数据的可见性,中间状态的数据对外部不可见,只有最初状态和最终状态的数据对外可见

例子:

  1. 小明向我转账500元,但是犹豫网络卡顿迟迟未到账
  2. 此时系统重新发了一次请求,又向我转账500元
  3. 最后我账号上增加了1000块,而小明账户上只减少了500块,这样转账前后的总金额就不一致了

image.png

1.2.3 持久性(durability)

  • 一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,已经提交的修改数据也不会丢失
  • 持久性指事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚。 通俗的说:比如我将事务做完之后,这个结果是能持久下去的并能一直存下去。不管断电还是其他情况。

image.png

1.2.4 隔离性(isolation)

  • 隔离性要求一个事务对数据库中数据的修改,在未提交完成前对于其他事务是不可见的
  • 隔离性指多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行效果。 通俗的说:多个用户并发访问操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。

1.3 事务的隔离级别

MySQL默认的事务隔离级别为repeatable-read 可重复读

查看MySQL事务默认的隔离级别

show variables like 'transaction_isolation'

image.png

可以看出事物隔离级别是repeatable-read;事物的read-only是被关闭的,这个tx_read_onlySpring的注解上可以通过@Transactional来修改。

事务的并发问题

image.png

1.3.1 未提交读(READ UNCOMMITED)脏读

设置当前会话的隔离级别为未提交读

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SESSION ASESSION B
T1SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T2START TRANSACTION;
T3 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
T4 START TRANSACTION;
T5SELECT * FROM t1; -- 结果为空
T6 INSERT INTO t1 VALUES (1, 100);
T7SELECT * FROM t1; -- 结果不为空,查询到了T6时刻添加的数据
T8 ROLLBACK;
T9SELECT * FROM; --结果为空

问题所在SESSION AT7 时刻,发生了脏读,读取到了SESSION B 未提交的数据。

1.3.2 已提交读 (READ COMMITED)

  • 只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
  • 该事务的隔离级别会造成不可重复读(同一事务内两次读的数据不一致)的问题
SESSION ASESSION B
T1SET SESSION TRANSACTION ISOLATION LEVEL READ COMMIT; //设置隔离级别为已提交读
T2BEGIN; //开启事务
T3UPDATE account SET balance = balance -5000 WHERE id = 1
T4 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;
T5 START TRANSACTION;
T6 SELECT * FROM account WHERE id = 1
T7COMMIT;
T8 SELECT * FROM account WHERE id = 1
  • T6:查询到amos账户余额的初始值为20000
  • T8:查询到amos账户余额的为15000
  • 这样两次查询的结果不一致,就导致了不可重复读的问题

1.3.3 可重复读(REPEATABLE READ)

  • 可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读

  • 可重复读中,该sql第一次读取到数据后,就将这些数据加锁(悲观锁),其它事务无法修改这些数据,就可以实现可重复读了。但这种方法却无法锁住insert的数据,所以当事务A先前读取了数据,或者修改了全部数据,事务B还是可以insert数据提交,这时事务A就会发现莫名其妙多了一条之前没有的数据,这就是幻读,不能通过行锁来避免。需要Serializable隔离级别 ,读用读锁,写用写锁,读锁和写锁互斥,这么做可以有效的避免幻读、不可重复读、脏读等问题,但会极大的降低数据库的并发能力。

  • 但是MySQLORACLEPostgreSQL等成熟的数据库,出于性能考虑,都是使用了以乐观锁为理论基础的MVCC(多版本并发控制)来实现。

SESSION ASESSION B
T1SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
T2BEGIN; //开启事务
T3UPDATE account SET balance = balance -5000 WHERE id = 1
T4 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;
T5 START TRANSACTION;
T6 SELECT * FROM account WHERE id = 1
T7COMMIT;
T8 SELECT * FROM account WHERE id = 1
  • T6 读取到amos的余额为15000
  • T8 读取到amos的余额为15000
  • 两次结果一致,可重复读解决了不可重复读的问题

1.3.4 可串行化(SERIALIZABLE)

  • 事务的最高级别,完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞
  • 在每个读的数据行上,加上锁,使之不可能相互冲突,因此,会导致大量的超时现象
  • 如果一个事务,使用了SERIALIZABLE——可串行化隔离级别时,在这个事务没有被提交之前 其他的线程,只能等到当前操作完成之后,才能进行操作,这样会非常耗时,而且,影响数据库的性能,通常情况下,不会使用这种隔离级别
SESSION ASESSION B
T1SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
T2BEGIN; //开启事务
T3SELECT * FROM account
T4 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
T5 START TRANSACTION;
T6 INSERT INTO account VALUES(3, 'amos2', 50000) //处于等待状态
T7COMMIT;
T8 // 当A提交事务后,T6才执行成功
  • T3 查询结果为两条记录
  • T6 执行时,因为T3还没有提交,所以T6一直处于等待状态
  • 当A提交事务后,T6才执行成功

总结

隔离级别脏读(Dirty Read)不可重复读(NonRepeatable Read)幻读(Phantom Read)
未提交读(Read uncommitted)可能可能可能
已提交读(Read committed)不可能可能可能
可重复读(Repeatable read)不可不可可能
可串行化(Serializable )不可能不可能不可能

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×