当前位置:网站首页>The isolation level of transaction and its problems
The isolation level of transaction and its problems
2020-11-06 21:19:48 【Irving the procedural ape】
One 、 The basic elements of a business (ACID)
1、 Atomicity (Atomicity): All operations after the transaction begins , Or all of them , Or not at all , It's impossible to stay in the middle . An error occurred during the execution of the transaction , It rolls back to the state it was in before the transaction started , All the operations are as if they didn't happen . That is to say, a business is an indivisible whole , It's like atoms in chemistry , Is the basic unit of material composition .
2、 Uniformity (Consistency): Before and after the transaction starts , The database integrity constraint is not broken . such as A towards B Transfer accounts , impossible A Buckle the money ,B Has received .
3、 Isolation, (Isolation): At the same time , Only one transaction is allowed to request the same data , There is no interference between the different transactions . such as A Withdrawing money from a bank card , stay A Before the withdrawal process is over ,B You can't transfer money to this card .
4、 persistence (Durability): After the transaction completes , All updates to the database by the transaction are saved to the database , Cannot be rolled back .
Two 、SQL Standard transaction isolation level (mysql The default is repeatable ,oracle The default is read committed )
- Read uncommitted (read uncommitted) Refer to , When a transaction has not yet been committed , The changes it makes can be seen by other things .
- Read submitted (read committed) Refer to , After a transaction is committed , The changes it makes will be seen by other things .
- Repeatable (repeatable read) Refer to , Data seen during the execution of a transaction , It is always consistent with the data seen when the transaction is started . Of course, at the level of repeatable read isolation , Uncommitted changes are also invisible to other transactions .
- Serialization (serializable ), As the name suggests, for the same line of records ,“ Write ” Will add “ Write lock ”,“ read ” Will add “ Read the lock ”. When there is a read/write lock conflict , Subsequent transactions must wait for the previous transaction to complete , To carry on That's ok .
Example
Business one | Business two |
---|---|
Start transaction A, Inquire about A=10 | Start transaction B |
Inquire about A=10, to update A=20 | |
The query value a1 | |
Commit transaction B | |
The query value a2 | |
Commit transaction A | |
The query value a3 |
If isolation level is “ Read uncommitted ”, be a1 The value is 20, although B Not submitted 2 But it turns out to be A I saw it
If isolation level is “ Read submitted ”, be a1 yes 10,a2 The value of is 2. Business B Can't be updated until it's submitted A notice . therefore , a3 The value of is also 2.
If isolation level is “ Repeatable ”, be a1、a2 yes 10,a3 yes 20. The reason a2 still 10, That's what we're following : The data that the transaction sees during execution must be consistent before and after .
If isolation level is “ Serialization ”, It's business B perform “ take A=10 Change to 20” When , Will be locked . Until transaction A After submission , Business B To continue . So from A From the perspective of , a1、a2 The value is 10,a3 The value of is 20
3、 ... and 、 How to set and view isolation level
- Read uncommitted :read uncommitted
- Read submitted :read committed
- Repeatable :repeatable read
- Serialization :serializable
Check the isolation level
show variables like 'transaction_isolation';
Set up innodb The transaction level method is :set Scope transaction isolation level Transaction isolation level , for example ~
SET [SESSION | GL.........
版权声明
本文为[Irving the procedural ape]所创,转载请带上原文链接,感谢
边栏推荐
- C++ 数字、string和char*的转换
- C++学习——centos7上部署C++开发环境
- C++学习——一步步学会写Makefile
- C++学习——临时对象的产生与优化
- C++学习——对象的引用的用法
- C++编程经验(6):使用C++风格的类型转换
- Won the CKA + CKS certificate with the highest gold content in kubernetes in 31 days!
- C + + number, string and char * conversion
- C + + Learning -- capacity() and resize() in C + +
- C + + Learning -- about code performance optimization
猜你喜欢
-
C + + programming experience (6): using C + + style type conversion
-
Latest party and government work report ppt - Park ppt
-
在线身份证号码提取生日工具
-
Online ID number extraction birthday tool
-
️野指针?悬空指针?️ 一文带你搞懂!
-
Field pointer? Dangling pointer? This article will help you understand!
-
HCNA Routing&Switching之GVRP
-
GVRP of hcna Routing & Switching
-
Seq2Seq实现闲聊机器人
-
【闲聊机器人】seq2seq模型的原理
随机推荐
- LeetCode 91. 解码方法
- Seq2seq implements chat robot
- [chat robot] principle of seq2seq model
- Leetcode 91. Decoding method
- HCNA Routing&Switching之GVRP
- GVRP of hcna Routing & Switching
- HDU7016 Random Walk 2
- [Code+#1]Yazid 的新生舞会
- CF1548C The Three Little Pigs
- HDU7033 Typing Contest
- HDU7016 Random Walk 2
- [code + 1] Yazid's freshman ball
- CF1548C The Three Little Pigs
- HDU7033 Typing Contest
- Qt Creator 自动补齐变慢的解决
- HALCON 20.11:如何处理标定助手品质问题
- HALCON 20.11:标定助手使用注意事项
- Solution of QT creator's automatic replenishment slowing down
- Halcon 20.11: how to deal with the quality problem of calibration assistant
- Halcon 20.11: precautions for use of calibration assistant
- “十大科学技术问题”揭晓!|青年科学家50²论坛
- "Top ten scientific and technological issues" announced| Young scientists 50 ² forum
- 求反转链表
- Reverse linked list
- js的数据类型
- JS data type
- 记一次文件读写遇到的bug
- Remember the bug encountered in reading and writing a file
- 单例模式
- Singleton mode
- 在这个 N 多编程语言争霸的世界,C++ 究竟还有没有未来?
- In this world of N programming languages, is there a future for C + +?
- es6模板字符
- js Promise
- js 数组方法 回顾
- ES6 template characters
- js Promise
- JS array method review
- 【Golang】️走进 Go 语言️ 第一课 Hello World
- [golang] go into go language lesson 1 Hello World