- Q: 資料庫在 SELECT 資料的時後發生 Deadlocked，訊息如下
ERROR |
System.Data.SqlClient.SqlException (0x80131904):
Transaction (Process ID 121) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim.
Rerun the transaction.
- 情況為所 SELECT 資料表會 1 小時更新一次，排成的流程如下
- DELETE TABLE 不必要的資訊
- INSERT INTO TABLE 新資料
- 這樣的流程造成 SELECT 資料時會產生 deadlocked
- 如是提升SQL的查詢效能
- 建立索引(index)為第一優先
- 當 SQL Command 時在語法中加上 WITH (NOLOCK)，可以改善線上大量查詢的環境中資料集被 LOCK 的現象藉此改善查詢的效能
- SQL Server 會執行對應的鎖定一致性檢查
- 將 WITH (NOLOCK) 加在 SELECT 語法 TABLE 的後面
- SELECT JOIN 的語法也是可以使用，但 DELETE、INSERT、UPDATE 需要Transaction 的指令就不行使用 WITH (NOLOCK)
- SQL SELECT 使用 WITH (NOLOCK) 會造成 Dirty Read (Do not use WITH (NOLOCK) without fully understanding the ramifications of a dirty read)
SELECT first_name,
last_name,
FROM dbo.person p WITH (NOLOCK)
JOIN dbo.employee e WITH (NOLOCK) ON e.person_id = p.person_id
WHERE p.person_id = 1;
- WITH (NOLOCK)
- 相當於 READ UNCOMMITTED
- 通知 SQL Server 所使用 SELECT 指令無需考慮 SELECT TABLE 的 Transaction Lock 狀態，資料庫系統的 Lock 現象會有明顯的減少(包含Dead Lock)
- 因WITH (NOLOCK)不考慮 SELECT TABLE 的 Transaction Lock 狀態
- 當系統正在 處理多個交易 (如：跨多個 table 的 transaction 交易-提款系統)，WITH (NOLOCK) 會讓目前處理 (process) 交易資料被乎略
- 使用 WITH (NOLOCK) 時，它允許閱讀那些已經修改但是還沒有交易完成的資料
- 如果有需要考量 Transaction 交易資料的即時完整性時，使用 WITH (NOLOCK) 就要好好考慮一下
- Demo (Ref: SQL SERVER – Basic Explanation of Query Hint NOWAIT – How to Not Wait on Locked Query)
- 創建1個 TABLE 與 1筆資料 (First Create the following table in the database)
USE tempdb
GO
CREATE TABLE First (ID INT, Col1 VARCHAR(10))
GO
INSERT INTO First (ID,
Col1)
VALUES (1,
'First')
GO
- 同時開起兩個查尋表單 (Now open two different connections)
- Connection 1: Run following query
BEGIN TRAN
DELETE
FROM First
WHERE ID = 1
- Connection 2: Run following query
BEGIN TRAN
SELECT ID, Col1
FROM First WITH(NOWAIT)
WHERE ID = 1
- 發生 Deadlocked 的情況
- 如果是用 WITH (NOLOCK)
BEGIN TRAN
SELECT ID, Col1
FROM First WITH(NOLOCK)
WHERE ID = 1
- 解決 Deadlocked 的情況，但風險為SQL Server不在意資料準確性，而產生資料不正確性和不精確性
- 因此開發人員需知道如何時情況下使用 WITH (NOLOCK)
- A: 這次 SELECT 案例為查尋場次資訊，並不會有金流交易的問題，因此使用 WITH (NOLOCK)，來解決 Deadlocked
- Ref:
沒有留言:
張貼留言