T-SQL:事務鎖下的併發處理（十五）

1.事務在sql servce 中事務是一個工作單元可能包含查詢和修改數據以及修改數據定義等多個活動也可以顯示或隱式定義事務邊界顯示定義事務 BEGIN TRAN 開始如果要提交事務使用 COMMIT TRAN 撤銷事務（回滾） ROLLBACK TRAN 如下示例一個簡單的顯示事務 ...

1.事務

在sql servce 中事務是一個工作單元可能包含查詢和修改數據以及修改數據定義等多個活動也可以顯示或隱式定義事務邊界

顯示定義事務 BEGIN TRAN 開始如果要提交事務使用 COMMIT TRAN 撤銷事務（回滾） ROLLBACK TRAN 如下示例

BEGIN TRAN;
INSERT INTO Sales.Orders
      (custid, empid, orderdate, requireddate, shippeddate, 
       shipperid, freight, shipname, shipaddress, shipcity,
       shippostalcode, shipcountry)
    VALUES
      (85, 5, '20090212', '20090301', '20090216',
       3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
       N'10345', N'France');
commit tran

一個簡單的顯示事務也可以是隱式事務

GO
 INSERT INTO Sales.Orders
      (custid, empid, orderdate, requireddate, shippeddate, 
       shipperid, freight, shipname, shipaddress, shipcity,
       shippostalcode, shipcountry)
    VALUES
      (85, 5, '20090212', '20090301', '20090216',
       3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
       N'10345', N'France');
GO

通過GO 在當前批執行完成時自動提交事務當然預設情況下SQL Server 將每個單獨的語句作為一個事務每個語句結束後SQL Server 會自動提交事務也可通過設置會話來改變預設設置

SET IMPLICIT_TRANSACTIONS ON

設置為ON 後不需要指定BEGIN TRAN 語句開始事務但必須以COMMIT TRAN或 TOLLBACK TRAN 標記結束

事務有4個屬性-原子性，一致性，隔離性，持續性首字母縮寫為ACID

原子性（Atomicity）: 事務是一個原子工作單元，事務中的所有修改要麼提交，要麼撤銷。在事務提交指令記錄到事務日誌之前如果系統出現了故障，重新啟動時，SQL Server 會撤銷所做的修改。如果事務中出現錯誤預設會自動回滾也可以通過 @@TRANCOUNT 檢測事務事務完成或者說是當前環境是否在事務中如果在就返回 1 沒有就是0

SELECT    @@TRANCOUNT

一致性（Consistency）：指數據狀態，在隔離級別中每個級別都要是一致性級別只有事務保持一致性級別才能訪問。在約束中也指主外鍵書屋會轉換資料庫的一致性狀態到另一個一致性狀態保持一致性

隔離性（Isolation）：隔離是一種控制訪問數據的機制，確保事務所範圍數據是在其所期望的一致性級別中的數據。在SQL Server 中支持兩種不同的模式來處理隔離：基於鎖的傳統模式和行版本控制(新模式) 但是預設的是鎖模式而且當前加鎖也是共用鎖，如果數據狀態不一致讀取數據就會被阻止直到狀態一致。而改成行版本控制模式讀取就不需要等待也不會加共用鎖在不需要及時顯示的數據時這種模式是提高併發的處理方式。具體實現要看使用的隔離級別。

持續性（Durability）：數據修改在寫入到資料庫磁碟之前，總是先寫入資料庫的事務日誌磁碟。提交後，指令記錄在事務日誌磁碟上，在尚未修改磁碟數據之前，事務是持續的，回滾也只是刪除事務日誌的所有修改記錄。

下麵是一個完整的事務示例：

BEGIN TRAN;

  DECLARE @neworderid AS INT;


BEGIN TRY
  INSERT INTO Sales.Orders
      (custid, empid, orderdate, requireddate, shippeddate, 
       shipperid, freight, shipname, shipaddress, shipcity,
       shippostalcode, shipcountry)
    VALUES
      (85, 5, '20090212', '20090301', '20090216',
       3, 32.38, N'Ship to 85-B', N'6789 rue de l''Abbaye', N'Reims',
       N'10345', N'France');

  SET @neworderid = SCOPE_IDENTITY();

  SELECT @neworderid AS neworderid;

  INSERT INTO Sales.OrderDetails(orderid, productid, unitprice, qty, discount)
    VALUES(@neworderid, 11, 14.00, 12, 0.000),
          (@neworderid, 42, 9.80, 10, 0.000),
          (@neworderid, 72, 34.80, 5, 0.000);

COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH

2.鎖

鎖是事務保護數據資源而獲得的控制資源，防止其他事務的衝突或不相容訪問。

鎖主要有兩種鎖模式排他鎖和共用鎖

當你試圖修改數據時事務會請求數據資源的一個排他鎖，它會一直到事務結束才會解除期間任何其他事務請求都會被阻塞。對於單條語句事務只要這條語句結束鎖就會自動解除。對於多條語句事務就只有當他完成所有語句執行並通過 COMMIT TRAN 或 ROLLBACK TRAN 命令時事務才會解除鎖

排他鎖：如果一個事務在修改行，直到事務完成，其他事務都不能修改相同的行。但是能不能讀取相同行取決於它的隔離級別。

共用鎖：在讀取事務加鎖是預設加的是共用鎖 SQL讀取的預設隔離級別 READ COMMITTED 因為此隔離級別會讓事務請求讀取資源時預設加上共用鎖多個事務可以同時擁有相同數據資源共用鎖。此模式下也會因為併發造成幻讀雖然在修改數據時，無法修改鎖和持續時間，但可以通過改變隔離級別在讀取數據時控制鎖定的處理方式。

其實鎖的本質就是數據的隔離級別在通過控制隔離級別也能達到加鎖的效果，而且效果更好。

在SQL 中預設的隔離級別 READ COMMITTED SNAPSHOT 這種隔離依靠行版本控制，而不是鎖，在此模式下讀取者不需要共用鎖，因為不需要等待，依賴行版本控制技術提供隔離。如果一個事務在READ COMMITTED隔離級別下修改數據行直到事務完成，另一個事務都不能讀取相同行。這種併發控制稱 “悲觀式併發” 如果一個事務在READ COMMITTED SNAPSHOT隔離級別下修改數據行此時另一個事務讀取相同行會獲得最後一次提交的可用狀態。這種併發控制稱 “樂觀式併發” 在樂觀併發中可以很好的解決修改和展示併發問題。

這種事務之間的併發處理已稱為 鎖相容性

請求鎖模式	請求排他鎖	請求共用鎖
請求排他鎖	否	否
請求共用鎖	否	是

交叉的否代表不相容請求鎖模式會被拒絕交叉是表示相容請求鎖模式會被接收

那麼鎖都可以鎖些什麼資源呢？

鎖定的資源包括RID ，鍵，行，頁，對象，表，資料庫，範圍，分配單元，堆，B樹。

鎖請求流程規則是什麼呢？

例如：獲取一個行上的排他鎖事務必須首先獲取一個行所在頁的意向排他鎖和一個擁有該頁對象的意向排他鎖，同樣的共用鎖也是此步驟。

為什麼要申請意向鎖？

為了在更高級別有效的檢測是否有鎖不相容請求，並防止授予這些鎖請求，這就是為什麼在排他鎖請求相同數據行時被阻斷的原因。

例如：一個事務持有行上鎖，而另一個事務在該行所在的頁或者表請求不相容鎖比如排他鎖有第一個事務的行上鎖有一個表意向鎖這個時候請求就被拒絕了

但意向鎖不會拒絕更低級別對象的鎖請求

例如：一個頁上的意向鎖不會阻斷在該頁的其他事務的排他鎖我們可以通過一個表來細緻的瞭解這些鎖的相容性請求。

請求鎖模式	請求排他鎖	請求共用鎖	請求意向排他鎖	請求意向共用鎖
請求排它鎖	否	否	否	否
請求共用鎖	否	是	否	是
請求意向排他鎖	否	否	是	是
請求意向共用鎖	否	是	是	是

通過這些鎖得到我們最理想的併發處理鎖定所需要的內容，即受影響行數。鎖是需要記憶體資源和內部管理開銷，當需要鎖時要考慮當前系統資源情況。

有一個情況不得不說在行鎖中超過5000個時會自動升級鎖到表鎖然後每加1250個鎖都會觸發預設的鎖升級

可以通過ALTER TABLE語句設置LOCK_ESCALATION 的表選項控制鎖升級。也可以禁用。也可以更改升級方式比如分區級別( 把一個表物理的組織成小單元c成為分區）鎖到此語句結束了下次講如何優雅的排除併發導致的阻塞數據處理。