最近博主所在的公司发生了一起很严重的生产事故,导致公司所有对外服务中断,原因的是数据库服务器宕机了,好歹运行部那帮平时懒散惯了的甩锅高手关键时刻还算有点用,经过一番折腾紧急修复了,给出的原因是公司所有的服务的数据库实例都在一个RAC上,共享物理设备,数据库负荷过大,在执行一些耗费IO较多的SQL时撑不住了,CEO发了火,运行部的老大挨了板子,这事儿也就过去,但是博主所负责的服务却遭了秧,出现了很多"半拉子工程",一条业务对应的数据库操作l,有的成功了,有的失败了,成功的也就成功了,失败的也就失败了,没有个整体的事物管理,博主目前所负责的系统也是几年前开发的,中间也是换了好几拨人维护,这方面处理的不是很完善,跟领导商量了一下,决定还是得补上这块窟窿,也就有了这篇文章。

研究一个东西首先要知道这个东西是什么,就像一个小品里,两个秀才争媳妇,答题取胜,其中一个说考啥都行,只要别考数学题就行,另一个来了句"什么是数学"引得满堂大笑。那我们就来看看什么是事务。

维基百科解释如下

A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database, and treated in a coherent and reliable way independent of other transactions.

A transaction generally represents any change in a database. Transactions in a database environment have two main purposes:

To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure, when execution stops (completely or partially) and many operations upon a

database remain uncompleted, with unclear status.

To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs' outcomes are possibly erroneous.

A database transaction, by definition, must be atomic, consistent, isolated and durable.[1] Database practitioners often refer to these properties of database transactions using the acronym ACID.

Transactions provide an "all-or-nothing" proposition, stating that each work-unit performed in a database must either complete in its entirety or have no effect whatsoever. Further,

the system must isolate each transaction from other transactions, results must conform to existing constraints in the database, and transactions that complete successfully must get written to durable storage.

大家可以自己翻译,

事务有几个特性, 简写就是ACID,即Atomic,Consistency,Isolation,Durability 其中最重要的就是原子性,一笔业务一般会涉及多个数据库操作,其整体做为一个事务,原子性的含义就是这些操作非常团结,干什么都是“一个都不能少”,好比一家人打车出去玩,不会因为座位不够而扔下一个人。对于一笔业务来说就是所有操作的结果保持一致,不会有的成功,有的失败。

Atomicity

Main article: Atomicity (database systems)

Atomicity requires that each transaction be "all or nothing": if one part of the transaction fails, then the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible ("atomic"), and an aborted transaction does not happen.

Consistency

Main article: Consistency (database systems)

The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This does not guarantee correctness of the transaction in all ways the application programmer might have wanted (that is the responsibility of application-level code), but merely that any programming errors cannot result in the violation of any defined rules.

Isolation

Main article: Isolation (database systems)

The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed sequentially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on the concurrency control method (i.e., if it uses strict – as opposed to relaxed – serializability), the effects of an incomplete transaction might not even be visible to another transaction.

Durability

Main article: Durability (database systems)

The durability property ensures that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

事务处理不好会出现脏读、不可重复读、和幻读,事务也有有不同的级别去应对以上情况。

As we know that, in order to maintain consistency in a database, it follows ACID properties. Among these four properties (Atomicity, Consistency, Isolation and Durability) Isolation determines how transaction integrity is visible to other users and systems. It means that a transaction should take place in a system in such a way that it is the only transaction that is accessing the resources in a database system.

Isolation levels defines the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system. A transaction isolation level are defined by the following phenomena –

Dirty Read – A Dirty read is the situation when a transaction reads a data that has not yet been commited.For example, Let’s say transaction 1 updates a row and leaves it uncommited, meanwhile Transaction 2 reads the updated row. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed.

Non Repeatable read – Non Repeatable read occurs when a transaction reads same row twice, and get a different value each time. For example, suppose transaction T1 reads a data. Due to concurrency, another transaction T2 updates the same data and commit, Now if transaction T1 rereads the same data, it will retrieve a different value.

Phantom Read – Phantom Read occurs when two same queries are executed, but the rows retrieved by the two, are different. For example, suppose transaction T1 retrieves a set of rows that satisfy some search criteria. Now, Transaction T2 generates some new rows that matches the search criteria for transaction T1. If transaction T1 reexecutes the statement that reads the rows, it gets a different set of rows this time.

Based on these phenomena, The SQL standard defines four isolation levels :

Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet commited changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.

Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction hold a read or write lock on the current row, and thus prevent other rows from reading, updating or deleting it.

Repeatable Read – This is the most restrictive isolation level. The transaction holds read locks on all rows it references and write locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non repeatable read.

Serializable – This is the Highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently ececuting transactions appears to be serially executing.