原文: [翻譯]:SQL死鎖-鎖與事務級別

其實這一篇呢與解決我項目中遇到的問題也是必不可少的。上一篇講到了各種鎖之間的兼容性，里面有一項就是共享鎖會引起死鎖，如何避免呢，將我們的查詢都設置中read uncommitted是否可行呢？其結果顯示，當我們當所有的查詢都設置成read uncommitted后，后面共享鎖死鎖基本消除了，看來還是管用的。好了下面接著翻譯：

Last time?we discussed a few major lock types that SQL Server uses. Shared(S), Exclusive(X) and Update(U). Today I’d like to talk about transaction isolation levels and how they affect locking behavior. But first, let’s start with the question: “What is transaction?”

上一次我們討論了一些主要的SQL SERVER鎖類型:共享鎖(S),排它鎖(X),以及更新鎖(U)。今天我們來講事務級別是如何影響鎖的行為的。但在這之前，我們需要從一個問題開始：“什么是事務”？

Transaction is complete unit of work. Assuming you transfer money from checking account to saving, system should deduct money from the checking and add it to the saving accounts at once. Even if those are 2 independent operations, you don’t want it to “stop at the middle”, well at least in the case if bank deducts it from the checking first If you don’t want to take that risk, you want them to work as one single action.

事務是一個完整的單元工作模式。假如你從支票帳戶中將錢轉移到儲蓄卡中，銀行系統(tǒng)首先會從你的支票帳戶中扣錢，然后再往你的儲蓄卡中存錢。即使這是兩個相互獨立的操作，你也不想讓其在中間的某一步停止，至少不能停止在銀行將你的錢從支票帳戶中扣除之后 。如果你不冒這個風險，那么你希望這兩步操作最好是一步操作來完成。

There is useful acronym – ACID – that describes requirements to transaction:

這里有一個非常有用的編寫-ACID,它將描述事務的需求：

• (A) – Atomicity or “all or nothing”. Either all changes are saved or nothing changed.

????????(A)-它代表所有或者是無，要么全部保存要么不保存任何數(shù)據(jù)

• (C) – Consistency. Data remains in consistent stage all the time

????????(C)-數(shù)據(jù)每時每刻要保持一致性

• (I) – Isolation. Other sessions don’t see the changes until transaction is completed. Well, this is not always true and depend on the implementation. We will talk about it in a few minutes

??????? (I)-數(shù)據(jù)隔離，其它的會話看不到事務未提交的數(shù)據(jù)。這句并不總是正確的，有時依賴于系統(tǒng)的實現(xiàn)，我們后續(xù)會講到。

• (D) – Durability. Transaction should survive and recover from the system failures

??????? (D)-數(shù)據(jù)可以回滾，當事務執(zhí)行出現(xiàn)異常的情況下

There are a few common myths about transactions in SQL Server. Such as:

下面是一些公共的關于事務的錯誤觀點，例如：

• There are no transactions if you call insert/update/delete statements without begin tran/commit statements. Not true. In such case SQL Server starts implicit transaction for every statement. It’s not only violate consistency rules in a lot of cases, it’s also extremely expensive. Try to run 1,000,000 insert statements within explicit transaction and without it and notice the difference in execution time and log file size.

?????? 當我們直接寫insert/update/delete這句語句時，如果沒有顯示的寫begin tran/commit 這類語句就不存在事務。這是不正確的，實際上SQL SERVER 會隱式的為每次SQL操作都加了事務。這不光違反了數(shù)據(jù)一致性規(guī)則且往往造成的后果是非常昂貴的?？梢匀L試往一個表中插入1000000條數(shù)據(jù)，第一種顯示的加上事務語句，第二種不加事務語句，執(zhí)行之后對比下執(zhí)行的時間以及日志大小的不同。

• There is no transactions for select statements. Not true. SQL Server uses (lighter) transactions with select statements.

??????? 當執(zhí)行select語句時沒有事務。這是不正確的，SQL SERVER會使用輕量級的事務。

• There is no transactions when you have (NOLOCK) hint. Not true. (NOLOCK) hint downgrades the reader to read uncommitted isolation level but transactions are still in play.

??????? 當們們在select語句后面加了nolock后，就沒有事務了。這也是不正確的。nolock只是降低了事務必有隔離級別為read uncommitted而已并不是沒有事務。

Each transaction starts in specific transaction isolation level. There are 4 “pessimistic” isolation levels: Read uncommitted, read committed, repeatable read and serializable and 2 “optimisitic” isolation levels: Snapshot and read committed snapshot. With pessimistic isolation levels writers always block writers and typically block readers (with exception of read uncommitted isolation level). With optimistic isolation level writers don’t block readers and in snapshot isolation level does not block writers (there will be the conflict if 2 sessions are updating the same row). We will talk about optimistic isolation levels later.

每個事務都在指定的事務級別中，這里有四種悲觀事務必有隔離級別：Read uncommitted (允許臟讀)，read committed(不允許臟讀)，repeatable(可重復讀)，serialzable以及兩種經(jīng)過優(yōu)化后的事務級別：Snapshot 以及read committed snapshot。

???? 注：這里事務隔離級別比較多，我理解不也太多，就省略掉了。我們比較常見的就是前面的兩種，允許臟讀以及不允許臟讀的情況。至于后面的有關鏡像相關的內容這里我不做多的翻譯。

Regardless of isolation level, exclusive lock (data modification) always held till end of transaction. The difference in behavior is how SQL Server handles shared locks. See the table below:

排它鎖不管事務級別，它總是占用鎖到整個事務結束：

So, as you can see, in read uncommitted mode, shared locks are not acquired – as result, readers (select) statement can read data modified by other uncommitted transactions even when those rows held (X) locks. As result any side effects possible. Obviously it affects (S) lock behavior only. Writers still block each other.

所以，就像你看到的，如果在允許臟讀的模式下，是不需要申請共享鎖的，可以讀取到其實事務還未完全提交的數(shù)據(jù)，即使這些數(shù)據(jù)已經(jīng)被加上了排它鎖。但這只影響共享鎖，對于寫的會話仍然會存在相互阻塞甚至死鎖的情況。

In any other isolation level (S) locks are acquired and session is blocked when it tries to read uncommitted row with (X) lock. In read committed mode (S) locks are acquired and released immediately. In Repeatable read mode, (S) locks are acquired and held till end of transaction. So it prevents other session to modify data once read. Serializable isolation level works similarly to repeatable read with exception that locks are acquired on the range of the rows. It prevents other session to insert other data in-between once data is read.

共享鎖可以任意事務隔離級別中發(fā)生，當它嘗試去讀取其它事務未提交的數(shù)據(jù)(行上加了排它鎖)時就是會阻塞。在Read committed 模式下，共享鎖的申請以及釋放都是非常迅速的。在Repeatable read模式下，共享鎖被申請后一直占用到事務結束，它保證其它會話不編輯其已經(jīng)讀取到的數(shù)據(jù)。Serializable 模式的工作方式和Repeatable非常相似，但它會鎖定一定范圍的數(shù)據(jù)，訪問其它會話插入數(shù)據(jù)。

注：這塊還沒理解到位，后續(xù)有時間再補充下。

You can control that locking behavior with “set transaction isolation level” statement – if you want to do it in transaction/statement scope or on the table level with table hints. So it’s possible to have the statement like that:

在你的事務中或者是表級間的查詢你可以通過設置事務隔離級別來控制鎖行為，就像下面的查詢語句：

So you access Table1 in read uncommitted isolation level and Table2 in serializable isolation level.

這條語句的作用就是你可以對Table1讀取其它事務未提交的數(shù)據(jù)，以serializable隔離級別讀取Table2的數(shù)據(jù)。

It’s extremely easy to understand the difference between transaction isolation levels behavior and side effects when you keep locking in mind. Just remember (S) locks behavior and you’re all set.

這將非常容易理解事務隔離級別行為之間的差別以及它們的副作用，你只需要記住共享鎖以及你所有的設置。

Next time we will talk why do we have blocking in the system and what should we do to reduce it.

下一次我們將會講到為什么我們的系統(tǒng)中會存在阻塞以及我們如何做才能減少阻塞的發(fā)生

[翻譯]:SQL死鎖-鎖與事務級別