今天我想談下SQL Server裡另一個非常有趣的話題:在SQL Server裡停用行和頁層級鎖。在SQL Server裡,每次你重建一個索引,你可以使用ALLOW_ROW_LOCKS 和ALLOW_PAGE_LOCKS選項來指定,SQLServer在用讀寫通路你的資料時,應該獲得行和頁鎖。我們從内部看下,當我們停用這些鎖時會發生什麼。
讓我們在一個聚集索引上運作一個簡單的REBUILD操作,這裡我們停用行層級鎖:
在這個事務期間,當你檢視鎖管理器時,你可以看到SQL Server隻在表層級獲得IS所,在頁層級獲得共享鎖,沒有行級别的鎖!
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcucjZ5gzYjFzY3AzNilDM5EWZzYWMmZDNwMWZiJTOxU2MfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
這些獲得的鎖現在沒有限制,因為通常SQL Server在頁層級獲得IS鎖,在行本身獲得共享鎖。當你通過一個事務修改你的資料,這個概念同樣适用。
在這個情況下,最後你還是在頁層級有排它鎖,而不是IX鎖。
接下來讓我們停用頁層級鎖:
首先我想向你展示下索引重組操作取決于頁層級鎖,是以這個重組操作會失敗:
The index “idx_ci” on table “Foo” cannot be reorganized because page level locking is disabled.
現在讓用重新運作我們的SELECT語句,但這次使用HOLDLOCK查詢提示:
當你再次檢視鎖管理器,你會看到在頁層級IS鎖消失了。我們隻有在表層級IS鎖,在行層級有共享鎖。
讓我們再來修改一條記錄:
和剛才一樣的事情發生了:SQL Server在表層級獲得IX鎖,在行上獲得排它鎖。在頁層級沒有鎖……
現在讓我們更進一步,對于我們的具體索引停用行和頁層級鎖:
現在當你讀取一些資料,SQL Server隻在表層級獲得共享鎖,你的整個表是隻讀的:
當你修改沒有獲得頁和行鎖的一條記錄時,SQL Server在整個表上獲得了排它鎖——偶滴神:
這篇文章的意義?為什麼你應該在SQL Server裡停用頁和行層級鎖,真的沒有一個很好的理由。就用SQL Server提供的預設的鎖政策即可,因為不然的話鎖會限制太多,進而傷及你的性能……
感謝關注!
<a href="https://www.sqlpassion.at/archive/2016/10/31/disabling-row-and-page-level-locks-in-sql-server/" target="_blank">https://www.sqlpassion.at/archive/2016/10/31/disabling-row-and-page-level-locks-in-sql-server/</a>