事務處理

• 事務的概念來自於兩個獨立的需求：併發資料庫訪問，系統錯誤恢復。

• 一個事務是可以被看作一個單元的一系列SQL語句的集合。

事務的特性（ACID）

• atomacity 原子性 ：事務必須是原子工作單元；對於其資料修改，要麼全都執行，要麼全都不執行。通常，與某個事務關聯的操作具有共同的目標，並且是相互依賴的。如果系統只執行這些操作的一個子集，則可能會破壞事務的總體目標。原子性消除了系統處理操作子集的可能性。

• consistency 一致性：事務將資料庫從一種一致狀態轉變為下一種一致狀態。也就是說，事務在完成時，必須使所有的資料都保持一致狀態（各種 constraint 不被破壞）。

• isolation 隔離性：由併發事務所作的修改必須與任何其它併發事務所作的修改隔離。事務檢視資料時資料所處的狀態，要麼是另一併發事務修改它之前的狀態，要麼是另一事務修改它之後的狀態，事務不會檢視中間狀態的資料。換句話說，一個事務的影響在該事務提交前對其他事務都不可見。

• durability 永續性：事務完成之後，它對於系統的影響是永久性的。該修改即使出現致命的系統故障也將一直保持。

事務的隔離級別

如果不對資料庫進行併發控制，可能會產生異常情況：

髒讀(Dirty Read)

• 當一個事務讀取另一個事務尚未提交的修改時，產生髒讀。

• 一個事務開始讀取了某行資料，但是另外一個事務已經更新了此資料但沒有能夠及時提交。這是相當危險的，因為很可能所有的操作都被回滾，也就是說讀取出的資料其實是錯誤的。

非重複讀(Nonrepeatable Read)

• 一個事務對同一行資料重複讀取兩次，但是卻得到了不同的結果。同一查詢在同一事務中多次進行，由於其他提交事務所做的修改或刪除，每次返回不同的結果集，此時發生非重複讀。

幻像讀(Phantom Reads)

• 事務在操作過程中進行兩次查詢，第二次查詢的結果包含了第一次查詢中未出現的資料（這裡並不要求兩次查詢的SQL語句相同）。這是因為在兩次查詢過程中有另外一個事務插入資料造成的。

• 當對某行執行插入或刪除操作，而該行屬於某個事務正在讀取的行的範圍時，會發生幻像讀問題。

丟失修改(Lost Update)

• 第一類：當兩個事務更新相同的資料來源，如果第一個事務被提交，第二個卻被撤銷，那麼連同第一個事務做的更新也被撤銷。

• 第二類：有兩個併發事務同時讀取同一行資料，然後其中一個對它進行修改提交，而另一個也進行了修改提交。這就會造成第一次寫操作失效。

為了兼顧併發效率和異常控制，在標準SQL規範中，定義了4個事務隔離級別，（ Oracle 和 SQL Server 對標準隔離級別有不同的實現 ）

未提交讀(Read Uncommitted)

• 直譯就是讀未提交，意思就是即使一個更新語句沒有提交，但是別的事務可以讀到這個改變。

• Read Uncommitted允許髒讀。

已提交讀(Read Committed)

• 直譯就是讀提交，意思就是語句提交以後，即執行了 Commit以後別的事務就能讀到這個改變，只能讀取到已經提交的資料。Oracle等多數資料庫預設都是該級別。

• Read Commited 不允許髒讀，但會出現非重複讀。

可重複讀(Repeatable Read)：

• 直譯就是可以重複讀，這是說在同一個事務裡面先後執行同一個查詢語句的時候，得到的結果是一樣的。

• Repeatable Read 不允許髒讀，不允許非重複讀，但是會出現幻象讀。

序列讀(Serializable)

• 直譯就是序列化，意思是說這個事務執行的時候不允許別的事務併發執行。完全序列化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞。

• Serializable 不允許不一致現象的出現。

事務隔離的實現——鎖

共享鎖(S鎖)

• 用於只讀操作(SELECT)，鎖定共享的資源。共享鎖不會阻止其他使用者讀，但是阻止其他的使用者寫和修改。

更新鎖(U鎖)

• 用於可更新的資源中。防止當多個會話在讀取、鎖定以及隨後可能進行的資源更新時發生常見形式的死鎖。

獨佔鎖(X鎖，也叫排他鎖)

• 一次只能有一個獨佔鎖用在一個資源上，並且阻止其他所有的鎖包括共享縮。寫是獨佔鎖，可以有效的防止“髒讀”。

Read Uncommited 如果一個事務已經開始寫資料，則另外一個資料則不允許同時進行寫操作，但允許其他事務讀此行資料。該隔離級別可以通過“排他寫鎖”實現。

Read Committed 讀取資料的事務允許其他事務繼續訪問該行資料，但是未提交的寫事務將會禁止其他事務訪問該行。可以通過“瞬間共享讀鎖”和“排他寫鎖”實現。

Repeatable Read 讀取資料的事務將會禁止寫事務（但允許讀事務），寫事務則禁止任何其他事務。可以通過“共享讀鎖”和“排他寫鎖”實現。

Serializable 讀加共享鎖，寫加排他鎖，讀寫互斥。

索引

資料庫建立索引能夠大大提高系統的效能。

• 通過建立唯一性的索引，可以保證資料庫表中每一行資料的唯一性。

• 可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。

• 可以加速表和表之間的連線，特別是在實現資料的參考完整性方面特別有意義。

• 在使用分組和排序子句進行資料檢索時，同樣可以顯著的減少查詢中分組和排序的時間。

• 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的效能。

增加索引也有許多不利的方面。

• 建立索引和維護索引需要消耗時間，這種時間隨著數量的增加而增加。

• 索引需要佔物理空間，除了資料表佔據資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要額空間就會更大。

• 當對錶中的資料進行增加，刪除和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度。

應該對如下的列建立索引

• 在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構。

• 在經常用在連線的列上，這些列主要是一些外來鍵，可以加快連線的速度。

• 在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的。

• 在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間。

• 在經常使用在where子句中的列上面建立索引，加快條件的判斷速度。

有些列不應該建立索引

• 在查詢中很少使用或者作為參考的列不應該建立索引。

• 對於那些只有很少資料值的列也不應該增加索引（比如性別，結果集的資料行佔了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度）。

• 對於那些定義為text，image和bit資料型別的列不應該增加索引。這是因為，這些列的資料量要麼相當大，要麼取值很少。

• 當修改效能遠遠大於檢索效能時，不應該建立索引，因為修改效能和檢索效能是矛盾的。

建立索引的方法：直接建立和間接建立（在表中定義主鍵約束或者唯一性約束時，同時也建立了索引）。

索引的特徵：

• 唯一性索引保證在索引列中的全部資料是唯一的，不會包含冗餘資料。

• 複合索引就是一個索引建立在兩個列或者多個列上。可以減少在一個表中所建立的索引數量。