事务第02讲:数据库事务[MySQL]
部分资料
引用自各路大神,具体见“99.参考资料”。
1. 什么是事务?
数据库事务:是数据库管理系统执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成。
2. 事务基本要素
【ACID】
原子性(atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位。
一致性(consistency):事务开始前和结束后,数据库的完整性约束没有被破坏。比如A向B转账,不可能A扣了钱,B却没收到。
隔离性(isolation):同一时间,只允许一个事务请求同一数据,
不同事务之间没有任何干扰
;这主要是针对并发事务来讲,并发事务所做的数据修改必须与其他并发事务隔离开进行
;事务查看数据时数据所处的状态,要么是另一个并发事务修改她之前的状态,要么是另一个事务修改她之后的状态,事务不会查看中间状态的数据
。比如A正从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。
持久性(durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。
3. MySQL事务隔离级别
【温馨提示】
:3.1和3.2交叉看,交叉看,交叉看。
隔离性:指多个用户并发操作数据库时,数据库为每一个用户开启不同的事务,这些事务之间互不干扰,相互隔离。
事务的隔离级别越高,事务的执行效率越低。
3.1 事务隔离级别概念
3.1.1 读未提交
概念:在该隔离级别下,一个事务可以读取到另一个事务【未提交】的数据,会产生脏读问题。在项目中几乎不用,安全性太差;
解决的问题:解决了写-写问题中“第一类丢失更新(回滚丢失)”,但是会出现脏读、不可重复读、第二类丢失更新和幻读。
3.1.2 读已提交
概念:一个事务可以读取到另一个事务【已提交】的数据。这是大多数数据库默认的隔离级别,但不是MySQL的默认隔离级别。
解决的问题:因为一个事务只能看到别的事务已提交的数据,所以可以避免脏读问题,但是会出现不可重复读、第二类丢失更新和幻读问题。
3.1.3 可重复读
概念:一个事务中多个实例在并发读取数据时会读取到一样的数据。
解决的问题:解决第一类丢失更新、脏读、不可重复读、第二类丢失更新,但是会出现幻读问题。简单说,幻读指用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影”行。MySQL默认的事务隔离级别,InnoDB存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了幻读问题。
3.1.5 可串行化
概念:事务的最高级别,通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,她是
在每个读取的数据行上加了共享锁
。在这个级别,可能导致大量的超时现象和锁竞争,一般为了提升程序的吞吐量不会采用这种隔离级别。
解决的问题:解决所有并发问题。
【小结】
- 读未提交:别人改数据的事务尚未提交,我在我的事务中也能读到;
- 读已提交:别人改数据的事务已经提交,我在我的事务中才能读到;
- 可重复读:别人改数据的事务已经提交,我在我的事务中也不去读;
- 串行化:我的事务尚未提交,别人就别想去改数据。
3.2 事务并发问题
当多个【用户/进程/线程】同时对数据库进行操作时,会出现3中冲突情形:
- 读-读,不存在任何问题
- 读-写,有隔离性问题,可能遇到脏读、不可重复读和幻读等
- 写-写,可能丢失更新
事务隔离级别可能引起的并发事务问题
。第一列是事务隔离级别,第二列至第四列是各个事务隔离级别对应的并发事务问题。ISO/ANSI SQL92定义了4个事务隔离级别,每个级别的隔离程度不同,允许出现的副作用也不同,应用可以根据自己的业务逻辑要求,通过选择不同的隔离级别来平衡 “隔离”与“并发”的矛盾
。
事务隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
---|---|---|---|---|
读未提交(read-uncommitted) | 是 | 是 | 是 | 哪个并发问题都解决不了 |
不可重复读(read-committed) | 否 | 是 | 是 | 也可称作读已提交,可解决脏读(Oracle默认值) |
可重复读(repeatable-read) | 否 | 否 | 是 | 可解决脏读、不可重复读(MySQL默认值) |
串行化(serializable) | 否 | 否 | 否 | 可解决脏读、不可重复读和幻读,相当于锁表 |
3.2.1 读-写问题
3.2.1.1 脏读
概念:事务A读取了事务B更新的数据(此时事务B并没有提交事务),然后B执行回滚操作,那么A读取到的数据就是脏数据。
栗子: 粑粑给你转账,在转账数据过去后粑粑并没有提交数据,介个时候粑粑告诉你钱还了,你查查撒,你看钱到账了,告诉我收到了,这个时候我再执行刚刚的事务回滚,那个钱就又回到我这里了。
3.2.1.2 不可重复读
概念:在
一个事务中两次查询的数据不一致
(侧重于数据的修改)。这么说吧,不一致本身并没有什么危害,危害的是我们不知道她不一致了,就会导致错误的数据处理。(事务A多次读取同一数据,事务B在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致)
栗子:试想一下,你在一个事务中用到了两次A这个数据,第一次用完后,另外一个事务对数据进行了修改,等到第二次用的时候数据就已经变了,跟你预期要操作的那个A数据不一致了,这个时候你接下来的逻辑代码就是在操作一个你不知道的数据,可怕不?就问你可怕不?
【注意】:脏读是一个事务读取了另一个事务[未提交]的数据,不可重复读是读取了另一个事务[提交]的数据。
解决不可重复读方案:只要锁住满足条件的行。
3.2.1.3 幻读
概念:在
一个事务中查询的行数不一样
(侧重于数据的新增和删除)
栗子:再来think一下下,如果事务A对表中的所有name这一列的内容全部改成“诸葛孔明”,执行完后,事务B新增了一个数据的name内容为“庞士元”,等你在事务A中再查看数据库会发现,哎吆我去,咋还有一行没改过来呢?
解决幻读方案:锁表(对于MySQL来说,MVCC也可解决幻读)
【注意】
:不可重复读和幻读很容易混淆。不可重复读侧重于修改,幻读侧重于新增or删除。
解决不可重复读的问题只要锁住满足条件的行,解决幻读需要锁表(对于MySQL来说,MVCC[参见《事务第01讲:MySQL锁》]也可解决幻读)
3.2.2 写-写问题
3.2.2.1 第一类丢失更新
第一类丢失更新,也称作回滚丢失(lostupdate)。A事务撤销时,把已提交的B事务的更新数据覆盖了。
这种错误可能造成很严重的问题,通过账户取款来分析一下。
时间 | 取款事务A | 转账事务B |
---|---|---|
T1 | 开始事务 | |
T2 | 开始事务 | |
T3 | 查询账户余额为1000元 | |
T4 | 查询账户余额为1000元 | |
T5 | 汇入100元,把余额改为1100元 | |
T6 | 提交事务 | |
T7 | 取出100元,把余额改为900元 | |
T8 | 撤销事务 | |
T9 | 余额恢复为1000元(丢失更新) |
A在事务撤销时,“不小心”将B事务已经转入账户的金额抹去了。
【注】:标准定义的所有隔离级别都不允许第一类丢失更新发生
。
3.2.2.2 第二类丢失更新
第二类丢失更新,也称作覆盖丢失/两次更新(Second lost update)
A事务覆盖B事务已经提交的数据,造成B事务所做操作丢失。
时间 | 转账事务A | 取款事务B |
---|---|---|
T1 | 开始事务 | |
T2 | 开始事务 | |
T3 | 查询账户余额为1000元 | |
T4 | 查询账户余额为100元 | |
T5 | 取出100元,把余额改为900元 | |
T6 | 提交事务 | |
T7 | 汇入100元,把余额改为1100元 | |
T8 | 提交事务 | |
T9 | 余额改为1100元(丢失更新) |
上面的栗子中由于转账事务覆盖了取款事务对存款余额所做的更新,导致银行最后损失了200元,相反如果转账事务先提交,则用户将损失200元。
第二类丢失更新,实质上和不可重复读是同一问题。
实际案例:
财务系统加工资,若公司本次调薪决定给员工彤彤加薪5k人民币,财务部两名操作人员A和B,过程如下:
1). A操作员在系统的页面上查询出彤彤的薪水信息,然后选择薪水记录进行修改,打开修改页面,但A突然有事离开了,页面放在那里没有作任何提交;
2).同时B操作员同样在应用中查询出彤彤的薪水信息,然后选择薪水记录进行修改,录入增加薪水5k,然后提交了;
3).此时,A操作员回来了,在自己之前打开的薪水修改页面也录入增加薪水5k,提交了。
该栗子操作员A和B只要一前一后作提交,问题就来了。这个过程走下来的结果是:彤彤涨了1w,操作员A和B都懵逼了。
3.2.2.3 解决方案
防止丢失更新的解决方案
两种思路:一种是
悲观锁
,一种是乐观锁
。
- 一种假设这样的问题是高概率,最好一开始就锁住,免得更新老是失败;
- 另一种假设这样的问题是小概率,最后一步做更新的时候再锁住,免得锁住时间太长影响其他人做有关操作。
3.2.2.3.1 解决方案一(悲观锁)
A.传统的悲观锁(不推荐)
以上面的栗子来看,
在弹出修改工资页面初始化时
(这种情况下一般会从数据库查询出来),在这个初始化查询中使用select ... for update nowait
,通过 for update nowait 语句,将这条记录锁住
,避免其他用户更新,从而保证后续的更新是在正确的状态下更新的。然后在保持这个链接的状态下,再做更新操作。当然这个有个前提就是保持链接,就是要对链接占用较长时间,这个在现在的web系统高并发频率下显然是不现实的。
B.现在的悲观锁(推荐优先使用
)
在修改工资这个页面
做提交时先查询下
,当然这个查询必须也要加锁
(select … for update nowait),保证在这条记录没有变化的基础上做更新,若有变化则提示告知用户。
页面做提交时先加锁查询下,不要一开始就加锁,增加效率
。
3.2.2.3.2 解决方案二(乐观锁)
A.旧值条件(前镜像)法
就是在sql更新时使用旧的状态值做条件,sql大致如下:update table set col1 = newcol1value, col2 = newcol2value … where col1 = oldcol1value and col2 = oldcol2value…,在上面的栗子中我们就可以把当前工资作为条件进行更新,如果这条记录已经被其他会话更新过,则本次更新了0行,这里我们应用系统一般会做个提示告知用户重新查询更新。这个取哪些旧值作为条件更新视具体系统实际情况而定(这种方式有可能发生阻塞,如果应用其他地方使用悲观锁法长时间锁定了这条记录,则本次会话就需要等待,
所以使用这种方式时最好统一使用乐观锁法
)。
B.版本列法(推荐优先使用
)
其实这种方式是一个特殊化的前镜像法,就是不需要使用多个旧值做条件,只需要
在表上加一个版本列
,这一列可以是 number 或者 date/timestamp 列,加这列的作用就是用来记录这条数据的版本(在表设计时,一般我们都会给每个表增加一些 number 型和 date 型的冗余字段,以便扩展使用,这些冗余字段完全可以作为版本列用),在应用程序中我们每次操作对版本列做维护即可。在更新时我们把上次版本作为条件进行更新
。
5. 栗子
举栗子说明各个隔离级别的情况。
【注意】
:验证“读已提交”的前提是MySQL关闭自动提交
,使用命令 show variables like ‘%commit%’; 进行查询,使用命令 set session autocommit = 0; 关闭会话级的自动提交(或者直接在配置文件关闭自动提交)。
5.1 读未提交
1). 打开一个客户端A,并设置当前事务隔离级别为read uncommited(未提交读),查询表tb_book的初始值,开启事务。从图中可以看出当前session的事务隔离级别是未提交读。
2). 在客户端A的事务提交之前,打开另一个客户端B,更新表tb_book,更新后s_age为80。
【注意】
:需要将mysql的binlog日志参数binlog_format设置为mixed,否则会报下面的错
impossible to write to binary logsince BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
即,innodb的事务隔离级别是读未提交或者读已提交模式时,binlog不可以使用statement模式记录日志。
3). 此时,虽然客户端B的事务还没有提交,但是客户端A就可以查询到B已经更新的数据,可以看到再次查询时s_age已经是B修改后的数据80
4). 一旦客户端B的事务因为某种原因回滚了,所有的操作都将会被撤销,那客户端A查询到的数据其实就是脏数据。可以看到回滚后又变回了100。
5). 在客户端A执行更新语句 update tb_student set s_age=s_age-20 where s_id=1; 诸葛孔明的 s_age 没有变成 80-20=60,居然是80,很奇怪。由于客户端B回滚了,所有客户端A再次update的时候其实是100-20=80。
解决方法:提升事务隔离级别为读已提交。
5.2 读已提交
1). 打开一个客户端A,并设置当前事务模式为read committed(读已提交),查询表tb_book的所有记录,开启事务;
2). 在客户端A的事务提交之前,打开另一个客户端B,更新表tb_student;
3). 此时,客户端B的事务还没提交,客户端A不能查询到B已经更新了的数据,解决了脏读的问题;
4). 客户端B提交事务
5). 客户端A再次执行查询,结果与上一步不一致,可以看到客户端A看到了客户端B已提交的数据,此时产生了不可重复读问题。
5.3 可重复读
1). 打开一个客户端A,并设置当前事务模式为repeatable read,查询表account的所有记录
2). 在客户端A的事务提交之前,打开另一个客户端B,更新表tb_student表并提交;
3). 在客户端A查询表tb_book的所有记录,与步骤1)查询结果一直,没有出现不可重复读的问题
4). 在客户端A,接着执行 update tb_student set s_age=s_age-10 where s_id=1; s_age 没有变成 60-10=50,s_age 的值用的是步骤2)中的50来算的,所以结果是50-10=40,数据的一致性没有被破坏
。
可重复读的隔离级别下MySQL使用了MVCC(多版本并发控制,Multiversion Concurrency Control)机制,select操作是不会更新版本号的,是快照读(历史版本);insert、update和delete会更新版本号,是当前读(当前版本)
。
5). 重新打开客户端B,插入一条新数据后提交
6). 在客户端A查询表tb_student的所有记录,没有查询出新增的数据,所有没有出现幻读
5.5 串行化
1). 打开一个客户端A,并设置当前事务模式为serializable,查询表tb_book的初始值;
2). 打开一个客户端B,并设置当前事务模式为serializable,插入一条记录,表被锁住了插入失败,mysql中事务隔离级别为serializable时会锁表,因此不会出现幻读的情况,这种隔离级别并发性极低,开发中很少会用到。
6. 补充说明
- 事务隔离级别为读提交时,写数据只会锁住相应的行;
- 事务隔离级别为可重复读时,如果检索条件有索引(包括主键索引)的时候,默认加锁方式是next-key锁;如果检索条件没有索引,更新数据时会锁住整张表。一个间隙被事务加了锁,其他事务是不能再这个间隙插入记录的,这样可以防止幻读;
- 事务隔离级别为串行化时,读写数据都会锁住整张表;
- 隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。
99.参考资料
该睡觉了,未完待续,明天再写吧,晚安了各位,么么哒(*  ̄3)(ε ̄ *)