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

薛定諤家的貓發表於2018-08-19
SQL

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成為分割槽）   鎖到此語句結束了下次講  如何優雅的排除併發導致的阻塞資料處理。

 

相關文章