原文: [翻譯]:SQL死鎖-鎖與事務(wù)級(jí)別

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

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鎖類(lèi)型:共享鎖(S),排它鎖(X),以及更新鎖(U)。今天我們來(lái)講事務(wù)級(jí)別是如何影響鎖的行為的。但在這之前，我們需要從一個(gè)問(wèn)題開(kāi)始：“什么是事務(wù)”？

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.

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

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

這里有一個(gè)非常有用的編寫(xiě)-ACID,它將描述事務(wù)的需求：

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

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

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

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

• (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ù)隔離，其它的會(huì)話(huà)看不到事務(wù)未提交的數(shù)據(jù)。這句并不總是正確的，有時(shí)依賴(lài)于系統(tǒng)的實(shí)現(xiàn)，我們后續(xù)會(huì)講到。

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

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

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

下面是一些公共的關(guān)于事務(wù)的錯(cuò)誤觀(guān)點(diǎn)，例如：

• 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.

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

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

??????? 當(dāng)執(zhí)行select語(yǔ)句時(shí)沒(méi)有事務(wù)。這是不正確的，SQL SERVER會(huì)使用輕量級(jí)的事務(wù)。

• 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.

??????? 當(dāng)們們?cè)趕elect語(yǔ)句后面加了nolock后，就沒(méi)有事務(wù)了。這也是不正確的。nolock只是降低了事務(wù)必有隔離級(jí)別為read uncommitted而已并不是沒(méi)有事務(wù)。

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.

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

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

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:

排它鎖不管事務(wù)級(jí)別，它總是占用鎖到整個(gè)事務(wù)結(jié)束：

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.

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

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.

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

注：這塊還沒(méi)理解到位，后續(xù)有時(shí)間再補(bǔ)充下。

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:

在你的事務(wù)中或者是表級(jí)間的查詢(xún)你可以通過(guò)設(shè)置事務(wù)隔離級(jí)別來(lái)控制鎖行為，就像下面的查詢(xún)語(yǔ)句：

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

這條語(yǔ)句的作用就是你可以對(duì)Table1讀取其它事務(wù)未提交的數(shù)據(jù)，以serializable隔離級(jí)別讀取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.

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

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

下一次我們將會(huì)講到為什么我們的系統(tǒng)中會(huì)存在阻塞以及我們?nèi)绾巫霾拍軠p少阻塞的發(fā)生

[翻譯]:SQL死鎖-鎖與事務(wù)級(jí)別