頁併發訪問的保護：閂鎖 在多線程併發情況下，需要防止讀線程讀到寫線程正在寫的資源，在編程中，通過使用互斥器(Mutexes), 信號量(Semaphore), 臨界區(Critical Section)和事件(Event Object)來保護資源，而這些機制在SQL Server中被統一表示為 閂鎖 ...
頁併發訪問的保護：閂鎖
在多線程併發情況下，需要防止讀線程讀到寫線程正在寫的資源，在編程中，通過使用互斥器(Mutexes), 信號量(Semaphore), 臨界區(Critical Section)和事件(Event Object)來保護資源，而這些機制在SQL Server中被統一表示為閂鎖。
閂鎖本身是一種數據結構，用於保護併發訪問中的資源。閂鎖有多種模式，不同模式的相容性不一。模式和相容矩陣如下：
- KP – Keep Latch 保證引用的結構不能被破壞
- SH – Shared Latch, 讀數據頁的時候需要
- UP – Update Latch 更改數據頁的時候需要
- EX – Exclusive Latch 獨占模式，主要用於寫數據頁的時候需要
- DT – Destroy Latch 在破壞引用的數據結構時所需要
-- KP SH UP EX EX DT KP Y Y Y Y Y N SH Y Y Y Y N N UP Y Y N N N N EX Y N N N N N DT N N N N N N 關於閂鎖，更多內容：關於閂鎖
鎖
閂鎖是線程併發時保護物理頁的機制，鎖(Lock)是事務併發時對邏輯頁的保護機制。
邏輯 VS. 物理
鎖是一個6位元組長的字元串，它本身並不是被鎖定的對象，只是描述了哪個對象被鎖定了。閂鎖是在記憶體中被鎖住的一個實際對象。獲取閂鎖需要獲取實際被鎖住的對象，獲取鎖只需要組織相應的6位元組字元串，然後向鎖管理器請求某個鎖。線程 VS. 事務
閂鎖由進程中的線程獲取和釋放，而鎖由事務獲取和釋放。而一個事務可能使用多個線程，如並行查詢，查詢中的子查詢被不同的工作線程執行等。
鎖模式有很多種，只通過其名稱，有時很難明白其含義。下麵簡單解釋一下各種鎖模式。
架構穩定鎖
當查詢計劃執行時需要確保它所引用的各種對象(e.g 表、索引)的結構不會被其它查詢修改，於是在查詢開始執行時，需要獲取所有被引用對象的”架構穩定鎖“，實際是SCH-S鎖。對於DDL語句，需要獲取對象上SCH-M鎖，它幾乎與所有的鎖模式都不相容。SCH-S它通常是在查詢編譯時獲取，編譯完成後，通常只是看到IS鎖。SCH-M在對象修改的整個過程都被持有，在這期間任何別的查詢都無法獲取這個對象上的鎖。BY Joe .TJ
共用鎖、更新鎖和排它鎖
基本的鎖模式包括共用鎖(S),排它鎖(X)和更新鎖(U)。S和X鎖，顧名思義。更新數據時，需要先找到將被更新的數據行，這些被找到的數據行就被加上U鎖，然後將U鎖升級成X鎖，再更新行或者刪除後插入行。U鎖與數據讀取類型的鎖模式是相容，即不阻塞讀操作，同時減少了更新死鎖的發生。鎖的層次結構和意向鎖
鎖模式名稱前有“I”的都是意向鎖，理解意向鎖前要先明白鎖的層次結構。以掃描一個包含數百萬行的表為例。如果每一行獲取一個S鎖，則需要數百萬個S鎖，顯然開銷會非常大。所以查詢決定不獲取行鎖，而獲取表上的S鎖。這時，如果有一個併發的查詢，需要刪除表中的某一行，於是需要獲取一個X行鎖。好了，問題來了：因為有了表級的S鎖，鎖管理器如何才能知道不能分配X行鎖給這個刪除操作？鎖只是一個描述什麼對象被 鎖住了的字元串，鎖管理器沒有辦法能過刪除操作發來的X行鎖字元串判斷出：表上已經有S表鎖，不能分配請求的X行鎖。為瞭解決這種問題，於是有了意向鎖。刪除操作知道要刪除的行屬於哪一個表，在獲取X行鎖前，需要獲取行的上層對象的IX鎖，即表的IX鎖。而IX鎖和S鎖是相容，於是兩個事務中的其一必須等待另一個完成才能開始。這樣就解決了這個問題。
同樣還有一類轉換鎖，如SIX,UIX等。比如SIX表示，用S鎖模式鎖定了表，並且可能鎖定下級的某個行。關於轉換鎖的更多資料，分析SIX鎖和鎖分區導致的死鎖
鍵範圍鎖
鎖模式名稱前有“R”的都是鍵範圍鎖。鍵範圍鎖需要在可序列化隔離級別下，它隱式地保護當前事務中已經讀取的行集範圍不實被其它事務修改。其它也是可序列化的要求，事務中讀取的數據，從頭到尾都要一致，不允許幻讀和幻插入。還有一點要註意，既然是”鍵“，那就是針對索引，即必需通過索引確定鍵範圍，才能使用鍵範圍鎖。
在預設的已提交讀隔離級別下，有種情況會使用到鍵範圍鎖：主外鍵關係的表中，如果設置了外鍵的級聯刪除，則刪除主鍵表時，被級聯刪除的外鍵表的行會使用鍵範圍鎖。BY Joe .TJ
數據寫入
數據修改操作方式和數據查詢操作類似，包括插入，更新和刪除等。數據修改操作符調用next()時，先定位到要修改的數據的位置(對於插入，則是定位到插入數據的新位置)，然後做相應的修改操作。然後再調用next()修改下一行，如此往複。刪除和更新操作通常被讀操作所驅動，當讀取操作符定位到要被刪除或者更新的行，然後將行的書簽傳給刪除或者更新操作符執行操作。SQL Server 使用日誌預寫( Write Ahead Logging )機制保證，每個數據修改操作完前要先寫事務日誌。數據修改的簡單流程如下：
操作符先定位到將要修改的數據所在的頁。同樣，頁必需要在緩存池中。
操作符需要獲取頁上的排它閂鎖，避免其它操作符讀取到些頁。
操作符生成修改操作的事務日誌，然後寫入到日誌文件(實際是寫入到日誌緩存區，還沒有寫入到磁碟上的日誌文件)。
操作符修改緩存池中的數據頁。
- 在開始數據修改前，將前面日誌的LSN寫入到數據頁的頁頭作為”最後修改操作的LSN(Last Modified LSN)“。
- 釋放頁上的排它閂鎖，其它的操作符可以讀取這一頁了。註意，直到這一步，所有操作都是發生在記憶體中，沒有任何磁碟操作發生。
- 在數據修改事務提交前，必鬚生成一條日誌用於表示此事務已經提交了。然後將這些日誌寫到磁碟上的日誌文件，這樣完成了事務的持久化。如果這時發生系統崩潰，在恢復時，恢復進程會REDO這些已經提交的事務。
SQL Server 通過Checkpoint周期性將緩存池中的臟頁寫到數據文件。
有一種數據寫入的流程與上面描述的有一些差別：最小化日誌化(minimally logged write)。只有插入新數據的操作能夠被最小日誌化，例如：INSERT，使用UPDATE中的 .WRITE()向blob類型的列追加數據。最小化日誌操作還必須滿足一些其它的條件，才能發生。它在寫數據的簡單流程如下：
- 分配新的頁給正在執行執行最小化日誌操作(BULK操作符)。
- 操作符從緩存池定位到分配給它的新頁，然後獲取它的排它閂鎖。
- 生成一條日誌記錄此頁已經用於最小化日誌的大容量插入操作。然後這條日誌被追加到日誌中(在日誌緩衝區)，再將這條日誌的LSN寫入到頁頭，作為最後修改操作的LSN.
- 頁被添加到事務中的最小化日誌頁列表中。
- 現在操作符就開始向頁中添加更多的行。這個過程中，不需要為每一行插入生成一條日誌。只是寫入緩存池中的頁，現在還沒有寫入到磁碟。
- 當一頁被寫滿，就重覆前面的流程分配新頁然後再寫入數據。
- 在最小化日誌操作事務提交前，必須將所有被修改的數據頁先寫入到磁碟。當數據頁寫入磁碟完成後，會生成一樣用於表示事務已提交的日誌，然後將這條日誌追加到日誌文件中(日誌緩衝區)。再將所有相關的日誌寫入到磁碟上的日誌文件。
- 為了避免“驚群效應”，即所有被修改的數據頁在事務提交前同時寫入數據文件。為了避免之種情況，SQL Server 使用一個叫積極寫(Eager Write)的進程將臟頁定入磁碟。積極寫進程可以在事務提交前交臟頁寫到磁碟。
最小日誌操作也是事務性的，滿足事務的ACID屬性。
關於最小化日誌操作的更多資料：導數中的最小化日誌記錄：背景和理論
DDL
並不是所有的T-SQL語句都使用在執行計劃樹中迭代操作符的方式執行。最典型的就是DDL語句，例如CREATE TABLE。SQL Server 將資料庫中所有對象的元數據存儲在內部系統表中，所以任何資料庫對象級的操作(如創建表，新增行，刪除表等等)都是要通過操作這些元數據來實現。大約有80個內部系統表，它們涵蓋了objects, procedures, functions, schemas, users, logins, certificates, views, partitions, permissions, databases, files等所有資料庫對象的元數據。雖然DDL不直接使用操作符，但是它直接使用實現操作符的代碼來高效地訪問內部系統表數據。例如，DDL不調用Seek操作符的next(),而是使用實現Seek操作符中next()的代碼去定位數據。DDL通過更新、刪除和插入內部系統表的數據來完成自身的操作。有一些DDL還去完成一些系統表外的操作，如在磁碟上創建文件，與Windows集群API協作配置AG等。還有一些DDL需要維護用戶表內的數據，如載入新添加的列的預設值，檢查現有的數據數據是否符合新加的約束等。
BACKUP, RESTORE 和 DBCC
概括的說，BACKUP和RESTORE是複製一個文件到一個文件。BACKUP是把數據文件和日誌文件複製到備份文件，RESTORE是把備份文件複製到數據文件和日誌文件，當做它們還會處理一些內部系統表的工作。而DBCC 每個命令的行為都不太一樣，推薦閱讀 CHECKDB from every angle 來瞭解更多信息。
我知道這些有什麼用？
資料庫開發者主要面對兩種問題：性能調優和解決數據丟失問題。知道這些對後者沒有什麼多大的用處，但對前者有幫助。一旦你明白，伺服器會給每一個客戶端發來的請求創建一個任務(Task)線程，性能問題就會被簡化為：任何時刻，任務要麼在執行，要麼在等待。而每一次等待的信息都會被SQL Server 內部會收集起來。推薦一個白皮書，其中有一個非常好的方法論指導如何使用等待信息排查性能瓶頸問題：Performance Tuning Waits Queues
