最近博主所在的公司發(fā)生了一起很嚴(yán)重的生產(chǎn)事故,導(dǎo)致公司所有對外服務(wù)中斷,原因的是數(shù)據(jù)庫服務(wù)器宕機(jī)了,好歹運(yùn)行部那幫平時(shí)懶散慣了的甩鍋高手關(guān)鍵時(shí)刻還算有點(diǎn)用,經(jīng)過一番折騰緊急修復(fù)了,給出的原因是公司所有的服務(wù)的數(shù)據(jù)庫實(shí)例都在一個RAC上,共享物理設(shè)備,數(shù)據(jù)庫負(fù)荷過大,在執(zhí)行一些耗費(fèi)IO較多的SQL時(shí)撐不住了,CEO發(fā)了火,運(yùn)行部的老大挨了板子,這事兒也就過去,但是博主所負(fù)責(zé)的服務(wù)卻遭了秧,出現(xiàn)了很多"半拉子工程",一條業(yè)務(wù)對應(yīng)的數(shù)據(jù)庫操作l,有的成功了,有的失敗了,成功的也就成功了,失敗的也就失敗了,沒有個整體的事物管理,博主目前所負(fù)責(zé)的系統(tǒng)也是幾年前開發(fā)的,中間也是換了好幾撥人維護(hù),這方面處理的不是很完善,跟領(lǐng)導(dǎo)商量了一下,決定還是得補(bǔ)上這塊窟窿,也就有了這篇文章平痰。

研究一個東西首先要知道這個東西是什么,就像一個小品里,兩個秀才爭媳婦,答題取勝,其中一個說考啥都行,只要別考數(shù)學(xué)題就行,另一個來了句"什么是數(shù)學(xué)"引得滿堂大笑汞舱。那我們就來看看什么是事務(wù)。

維基百科解釋如下

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.

大家可以自己翻譯,

事務(wù)有幾個特性,?簡寫就是ACID,即Atomic,Consistency,Isolation,Durability 其中最重要的就是原子性,一筆業(yè)務(wù)一般會涉及多個數(shù)據(jù)庫操作,其整體做為一個事務(wù),原子性的含義就是這些操作非常團(tuán)結(jié),干什么都是“一個都不能少”,好比一家人打車出去玩,不會因?yàn)樽徊粔蚨酉乱粋€人宗雇。對于一筆業(yè)務(wù)來說就是所有操作的結(jié)果保持一致,不會有的成功,有的失敗昂芜。

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.

事務(wù)處理不好會出現(xiàn)臟讀、不可重復(fù)讀赔蒲、和幻讀,事務(wù)也有有不同的級別去應(yīng)對以上情況泌神。

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.