[MySQL - Transaction] Four Features and Isolation Levels

Lost a rabbit tooth lx 2022-08-06 13:14:23 阅读数:581


​1. 什么是事务

Transaction is born in order to wrap up a number of independent operation as a whole,Such a set of operations,要么全都执行,要么都不执行.事务是一个不可分割的数据库操作序列,也是数据库并发控制的基本单位.

2. 为什么用事务

In the case of the most typical bank transfer,A 给 B 转账,Then its execution of the process is:A 账户 - 500 B 账户 + 500.万一在这两个操作之间突然出现错误比如银行系统崩溃,导致 A 余额减少而 B 的余额没有增加,这样就不对了.事务就是保证这两个关键操作要么都成功,要么都要失败.

3. 事务的使用步骤

  • 开启事务(start transaction)
  • 执行多条 SQL
  • 提交/回滚事务(commit 全部成功/rollback 全部失败)


-- 开启事务
start transaction;
-- A 账户减少500
update account set money = money - 500 where name = 'A';
-- B 账户加上500
update account set money = money + 500 where name = 'B';
-- 提交事务

4. 事务的四大特性


4.1 原子性(Atomicity)

一个事务中的所有操作,要么全部执行成功,要么全部执行失败.Failed to perform but not does not perform,But through the inverse operation rollback data,消除前面的 SQL
带来的影响.当第二个 SQL 执行失败时,数据库就会还原之前操作.

比如 A 账户 -500元的逆操作就是 A 账户 +500元.

4.2 持久性(Durability)

事务执行完成之后,它所做的所有修改都是永久的.因为事务一旦提交,The data is written to disk,持久化的存储起来了.

4.3 一致性(Consistency)

A transaction in the data before and after the execution must maintain a reasonable and lawful status,The transaction is always from one consistent state to another consistent state.

比如,Two accounts to transfer,The two accounts for the total100,Into account the total is still as100,Can't make the total from100变为30,This is not reasonable,Do not conform to the consistency of the.

4.4 隔离性(重点)(Isolation)

多个事务并发访问时,事务之间是相互隔离的,一个事务不应该被其他事务干扰,多个并发事务之间要相互隔离.Isolation solution is when concurrent execution of multiple transaction,Especially the transaction in an attempt to modify or read the same data,There will be some problems.

Concurrent execution transaction possible problems such as:When a person is writing articles,Another person to read,Then he went away,But he didn't read write the final result,所以就会产生问题,这种就称为“脏读”问题.
第二种情况就是,Writing an article or a person in,But in the process of him to write to write this operation for the locking,Others will have to wait until after he submitted articles to read.This avoids the first kind of circumstance“脏读”问题.But when others to read,The author began to write,Finally finished,Will appear more read did not change the contents of the before,Read the content of the modified again.这就是“不可重复读”问题.
The third is to avoid the former two cases,Can't read rules changed,Read also cannot change.这样就解决了“脏读”问题和“不可重复读”问题.But will appear when you read again,He to write another article,Because the transaction, although at the time of submit isolation, want to undertake a series of lock,But it is not to lock all the table.So when you read you will find many an article the other,这就是“幻读”问题.

5. Concurrent execution of transaction three big problem

5.1 脏读问题

脏读问题:事务 A 在对某个数据进行修改,修改的同时,事务 B 读取了数据,此时事务 B 读取的就是一个“脏数据”,It is not the result of a final.

5.2 不可重复读问题

不可重复读问题:在一个事务中,包含了多次的读操作,The results of the multiple read operations out inconsistent.

5.3 幻读问题

幻读问题:一个事务执行过程中进行多次查询,多次查询的结果集不一样,This operation is also a kind of special not repeatable read question.

6. MySQL 事务的隔离级别

6.1 read uncommitted

Allows read uncommitted,该隔离级别的事务可以看到其他事务中未提交的数据.This isolation level the highest degree in concurrent,隔离级别最低.Because this level can be read uncommitted data in other transactions,而未提交的数据可能会发生回滚,So read this level to the data it called dirty data,Also is the dirty read will happen,There are not repeatable read and magic question.

6.2 read committed

读取已提交,This isolation level transaction can only be read committed transactions data,因此解决了脏读问题,But because in a transaction execution can be read to the results of other transaction commit,So the same in different time SQL Under the query will have different result,This is not repeatable read phenomenon,Read the questions and magic.在这个隔离级别中,并发程度降低了,But isolation level improved,解决了脏读问题.

6.3 repeatable read

可重复读,是 MySQL 的默认事务隔离级别,它能确保同一事务多次查询的结果一致,But the new problem is,Other transactions are read,Another transaction successful insert one data,But also less than the data query,Don't you insert again in,Read the questions that is unreal.This isolation level,并发程度又降低了,隔离性又提高了,解决了不可重复读问题.

6.4 serializable

串行化,它会强制事务排序,使之不会发生冲突,解决了幻读问题.它是 MySQL The highest level of isolation,But least concurrent,效率低,执行速度最慢,使用场景不多.

Through the modification of the above transaction isolation level may be my.ini This configuration file to set the current database isolation level,According to the actual demand to choose isolation level.


copyright:author[Lost a rabbit tooth lx],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/218/202208061303308031.html