(4) 数据库之 深入理解 单机事务
ACID 1、原子性 一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成, 不会结束在中间某个环节 。 事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。 举例说明: bob 给smith转账100元,
ACID
1、原子性
一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
举例说明:
bob 给smith转账100元,事务分解为三个操作,分别对应v1,v2和v3。
两个状态 事务的初始状态对应v1. 事物的最终状态 执行成功则为v3,失败则只能回滚到v1状态。
原子性只保证记录回滚段。如果在v3步发现smith账户不存在或者在v3步 smith的100块也加上了但commit失败。
则会通过回滚段回滚至事务的初始状态。具体是先通过v3 执行v3的undo 将状态 回退至 v2状态。然后v2再通过v2
的undo回退至v1状态。
对于中间状态v2 原子性并不保证你看不到中间状态。在一致性(C)不保证的前提下。另如有别的线程在v2时 给smith增加了300元。但是该事务在v3时出错导致回滚值事务的最初状态 。 那么 smith的这300块 就平白无故的 消失了。
原子性的语意只保证记录回滚段(Undo日志)。通过回滚段可以回滚到之前的版本。其他的不做任何保证。
2、一致性
一般来说 原子性操作 都需要考虑一致性的问题。一致性的核心就是Can(happen before)。就是一个事务单元全部执行成功才可见。
事务单元与事务单元之间并发执行的时候可能在视点3 对Bob 或Smith进行更新操作。当事务回滚时会导致视点3的更新丢失。这个是严重的问题,如上面原子性讲到的smith 平白无故少了300元钱。 一致性保证happen beofre关系,就是在事务单元的开始和结束分别进行加锁和解锁 的操作。正式由于此强一致性 是的事务的视点3被迫 移到视点1的位置。这样做实际上就是对所有的事务进行排队的过程(实际过程很复杂)。
3、隔离性
如果一个事务结束,另一个事物才能进来 那么系统肯定是一致性的。但是完全一致性的情况下,定会导致系统的并发运行效率低。 于是不得不使用隔离性 提高系统的并发度。
隔离性 就是 以性能为理由,对一致性的破坏
数据库事务的隔离级别有4个,由高到低依次为Serializable、Repeatable read、Read committed、Read uncommitted。
(1)Serializable序列化读写 (队列 排它锁 )
将所有的请求排队,用排它锁把事务单元锁住。单位时间内只有一个‘人’能进来。全部是是串行,性能差导致系统不可用。
读写锁的隔离级别 Repeatable read 可重复读 和 Read committed 读已提交和 Read uncommitted 读未提交
(2)Repeatable read可重复读 (读锁不能被写锁升级,只能读读并行,并不能完美提升性能。)
(3) Read
committed读已提交(读锁可以被写锁升级 ,可以实现
读读并行、读写并行)
在Read committed读已提交时会出现 不可重复读 。同一事务的两次读到的结果可能不一致。因为在前一次读后,另一个写操作执行写提交。再读的话就会导致两次读结果不一致。而Repeatable read可重复读 是读读并行,不会出现 这种问题。
(4)Read uncommitted读未提交 只加写锁,不加读锁。可以做到读读并行、读写并行、写读并行
可以做到读读并行、读写并行、写读并行 。写写只能串行。
代价:
读到一些未提交的中间状态数据 ,因为读没加锁。所以一般不使用这种隔离级别。
4、持久性(Durability)
指的是只要事务成功结束,它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃,重新启动数据库系统后,数据库还能恢复到事务成功结束时的状态。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
http://blog.csdn.net/fg2006/article/details/6937413
4.多个事务并发时的并发问题:
?脏读:一个事务读到另一事务未提交的更新数据。
?不可重复读:一个事务读到另一事务已提交的更新数据。
?虚读:一个事务读到另一事务已提交的新插入的数据。
?第一类丢失更新:撤销一个事务时,把其他事务已提交的更新数据覆盖。
?第二类丢失更新:这是不可重复读中的特例,一个事务覆盖另一事务已提交的更新数据。
数据库事务的隔离级别有4个,由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable,
这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。
√: 可能出现 ×: 不会出现
脏读 | 不可重复读 | 幻读 | |
Read uncommitted | √ | √ | √ |
Read committed | × | √ | √ |
Repeatable read | × | × | √ |
Serializable | × | × | × |
注意:我们讨论隔离级别的场景,主要是在多个事务并发的情况下,因此,接下来的讲解都围绕事务并发。
Read uncommitted 读未提交 (读读 读写 写读并行)
公司发工资了,领导把5000元打到singo的账号上,但是该事务并未提交,而singo正好去查看账户,发现工资已经到账,是5000元整,非常高兴。可是不幸的是,领导发现发给singo的工资金额不对,是2000元,于是迅速回滚了事务,修改金额后,将事务提交,最后singo实际的工资只有2000元,singo空欢喜一场。
出现上述情况,即我们所说的脏读,两个并发的事务,“事务A:领导给singo发工资”、“事务B:singo查询工资账户”,事务B读取了事务A尚未提交的数据。
当隔离级别设置为Read uncommitted时,就可能出现脏读,如何避免脏读,请看下一个隔离级别。
Read committed 读提交 (读读 读写并行)
singo拿着工资卡去消费,系统读取到卡里确实有2000元,而此时她的老婆也正好在网上转账,把singo工资卡的2000元转到另一账户,并在singo之前提交了事务,当singo扣款时,系统检查到singo的工资卡已经没有钱,扣款失败,singo十分纳闷,明明卡里有钱,为何......
出现上述情况,即我们所说的不可重复读,两个并发的事务,“事务A:singo消费”、“事务B:singo的老婆网上转账”,事务A事先读取了数据,事务B紧接了更新了数据,并提交了事务,而事务A再次读取该数据时,数据已经发生了改变。
当隔离级别设置为Read committed时,避免了脏读,但是可能会造成不可重复读。
大多数数据库的默认级别就是Read committed,比如Sql Server , Oracle。如何解决不可重复读这一问题,请看下一个隔离级别。
Repeatable read 重复读 (读读并行)
当隔离级别设置为Repeatable read时,可以避免不可重复读。当singo拿着工资卡去消费时,一旦系统开始读取工资卡信息(即事务开始),singo的老婆就不可能对该记录进行修改,也就是singo的老婆不能在此时转账。
虽然Repeatable read避免了不可重复读,但还有可能出现幻读。
singo的老婆工作在银行部门,她时常通过银行内部系统查看singo的信用卡消费记录。有一天,她正在查询到singo当月信用卡的总消费金额(select sum(amount) from transaction where month = 本月)为80元,而singo此时正好在外面胡吃海塞后在收银台买单,消费1000元,即新增了一条1000元的消费记录(insert transaction ... ),并提交了事务,随后singo的老婆将singo当月信用卡消费的明细打印到A4纸上,却发现消费总额为1080元,singo的老婆很诧异,以为出现了幻觉,幻读就这样产生了。
注:Mysql的默认隔离级别就是Repeatable read。
Serializable 序列化
Serializable是最高的事务隔离级别,同时代价也花费最高,性能很低,一般很少使用,在该级别下,事务顺序执行,不仅可以避免脏读、不可重复读,还避免了幻像读
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Go language is an efficient, concise and easy-to-learn programming language. It is favored by developers because of its advantages in concurrent programming and network programming. In actual development, database operations are an indispensable part. This article will introduce how to use Go language to implement database addition, deletion, modification and query operations. In Go language, we usually use third-party libraries to operate databases, such as commonly used sql packages, gorm, etc. Here we take the sql package as an example to introduce how to implement the addition, deletion, modification and query operations of the database. Assume we are using a MySQL database.

How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())

Hibernate polymorphic mapping can map inherited classes to the database and provides the following mapping types: joined-subclass: Create a separate table for the subclass, including all columns of the parent class. table-per-class: Create a separate table for subclasses, containing only subclass-specific columns. union-subclass: similar to joined-subclass, but the parent class table unions all subclass columns.

Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps

Analysis of the basic principles of the MySQL database management system MySQL is a commonly used relational database management system that uses structured query language (SQL) for data storage and management. This article will introduce the basic principles of the MySQL database management system, including database creation, data table design, data addition, deletion, modification, and other operations, and provide specific code examples. 1. Database Creation In MySQL, you first need to create a database instance to store data. The following code can create a file named "my

HTML cannot read the database directly, but it can be achieved through JavaScript and AJAX. The steps include establishing a database connection, sending a query, processing the response, and updating the page. This article provides a practical example of using JavaScript, AJAX and PHP to read data from a MySQL database, showing how to dynamically display query results in an HTML page. This example uses XMLHttpRequest to establish a database connection, send a query and process the response, thereby filling data into page elements and realizing the function of HTML reading the database.

PHP is a back-end programming language widely used in website development. It has powerful database operation functions and is often used to interact with databases such as MySQL. However, due to the complexity of Chinese character encoding, problems often arise when dealing with Chinese garbled characters in the database. This article will introduce the skills and practices of PHP in handling Chinese garbled characters in databases, including common causes of garbled characters, solutions and specific code examples. Common reasons for garbled characters are incorrect database character set settings: the correct character set needs to be selected when creating the database, such as utf8 or u

To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.
