原文: [翻譯]:SQL死鎖-阻塞

一般情況下死鎖不是一步到位的，它必須滿足特定的條件，然后形成資源的循環依賴才會產生死鎖，死鎖之前一定會出現阻塞，由阻塞升級才有可能出現死鎖，所以我們有必要了解系統中都有哪些已經被阻塞的鎖。

我在解決共享鎖產生的死鎖時，我測試團隊的一位同事的問題：既然所有的查詢都已經是read uncommitted模式了，為什么還會有死鎖呢？下面這篇會回答這個問題。

We already know what are the most important lock types and how transaction isolation levels affect locking behavior . Enough theory – today I’d like to show you simple example why blocking typically occurs in the system.

我們已經知道了最重要的幾種鎖的類型以及事務隔離級別是如何影響鎖行為的。今天我將給大家講一個例子，展示阻塞是如何發生的。

First, let’s create the table and populate it with the data.

首先，我們創建一個表格以及往這個表格中插入一定的測試數據。

As you can see, this table has 50,000 rows now and 1 clustered index on ID column. Now let’s start another session and run update statement that acquires the lock (update row with ID = Value = 40000). I’m using read committed isolation level but that behavior occurs in any pessimistic isolation level (read uncommitted, read committed, repeatable read and serializable).

這個表格已經有50,000行數據，在Id列上有一個聚集索引。我們另起一個會話用來更新數據。注意，這個更新的事務未提交。

Next, let’s take a look at the list of the locks in the system with sys.dm_tran_locks DMV. I don’t have any other activity in the system but in your case, you can filter results by request_session_id if needed.

下一步，我們從sys.dm_tran_locks中查詢所有的鎖信息。這個視圖由于統計了所有會話的鎖信息，如果你查詢的一個正在使用中的數據庫。，那么顯示的信息可能會比較多，你需要根據自己的會話Id過濾下數據結果，我本地因為沒有其它的會話，所以不需要過濾。

So we can see 3 active locks: exclusive lock on key (row) level and 2 intent-exclusive locks on the page and table levels.

我們看到了3個鎖信息，一個排它鎖在行記錄上，兩個意向排它鎖在頁級以及數據表對象上。

Now let’s open another session and run select with filter on ID column in the read committed isolation level (you’ll experience the same behavior in repeatable read and serializable isolation levels). This select executes just fine with clustered index seek in the plan.

現在我們打開另外一個會話，在Read comitted 模式下執行一條按Id過濾的查詢語句。這條查詢語句在聚集索引查找下很順利的執行成功。

Now let’s change select and replace filter on ID column with filter on Value column. This select should return the same 1 row but it you run it, it would be blocked.

現在，我們更換查詢條件，從Id轉換成非聚集索引列Value ，這條語句應該返回一行數據，但當你執行時，它將會被阻塞住。

If we query sys.dm_tran_locks again, we can see that the second session is waiting to acquire shared lock.

Let’s terminate the select and take a look at estimated execution plan.

讓我們來看一看實時的執行計劃

As you can see, the plan changes to clustered index scan. We know that this select returns only 1 row from the table but in order to process the request, SQL Server has to read every row from the table. When it tries to read updated row that held exclusive lock, the process would be blocked (S lock is incompatible with X/IX locks). That’s it – blocking occurs not because multiple sessions are trying to update the same data, but because of non-optimized query that needs to process/acquire lock on the data it does not really need.

就像你看到的，執行計劃已經變為聚集索引掃描了。我們知道這個查詢只應該返回一條數據，但SQL SERVER為了返回正確的行不得不讀取所有行記錄。當它嘗試讀取正在被更新的(已經被上了排它鎖)數據行時就會出現阻塞。所以阻塞的發生并不是因為同時有多個會議嘗試去更新相同的數據，而是因為沒有經過優化的查詢申請了鎖，但讀取到的數據往往是不必要的數據。

Now let’s try to run the same select in read uncommitted mode.

現在，我們在read uncommitted模式下執行相同的語句

As you can see – select runs just fine even with scan. As I already mentioned, in read uncommitted mode, readers don’t acquire shared locks. But let’s run update statement.

如圖顯示，查詢語句在uncommitted模式式正常返回。就像我已經提醒過的，在read uncommitted模式下，讀取數據不需要申請共享鎖，但我們來試試數據更新

It would be blocked. If you take a look at the lock list, you’ll see that there is the wait on update lock (SQL Server acquires update locks when searches for the data for update)

阻塞出現，我們再看一下鎖列表，將會發現一個更新鎖，當前的狀態為等待。

And this is the typical source of confusions – read uncommitted mode does not eliminate blocking – shared locks are not acquired, but update and exclusive locks are still in the game. So if you downgraded transaction isolation level to read uncommitted, you would not completely solve the blocking issues. In addition to that, you would introduce the bunch of consistency issues. The right way to achieve the goal is to illuminate the source of the problem – non-optimized queries.

這是典型的容易引起混淆的原因所在。read uncommitted模式并不會消除阻塞。共享鎖雖然不需要申請了，但更新鎖以及排它鎖仍然存在。如果你將事務隔離級別降低到read uncommitted，你并不能完全解決阻塞的問題。額外說一下，這樣會產生數據不致的問題。正確解決阻塞的方法是說明問題的根源：未經優化的查詢。

Next time we will talk how to detect such queries.

下一次我們將討論如何發現這些未經優化的查詢。

[翻譯]:SQL死鎖-阻塞