SQL SERVER鎖升級的investigation是怎樣的

這篇文章將為大家詳細講解有關(guān)SQL SERVER鎖升級的investigation是怎樣的，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。

前些日子在分析SQL SERVER 死鎖的過程中，檢查有一些莫名其妙的死鎖，兩個根本不搭噶的事務(wù)，鎖在了一起，WHY，其實SQL SERVER 在數(shù)據(jù)庫界，算是一朵奇葩，獨有的鎖升級的技術(shù)，我想你應(yīng)該不曾聽到 MYSQL ,ORACLE , PG  ，MONGODB 這些數(shù)據(jù)庫提及到的鎖升級的問題。

而牽扯到鎖升級到額問題，就需要提及，到底為什么鎖升級，鎖從哪里升級到哪里的問題。所以就有了這篇文字，關(guān)于鎖升級的東西。

首先我們在談?wù)撴i的時候，的先站在一個討論的起跑線，就是鎖是發(fā)生在內(nèi)存級別的，并且鎖的開始和結(jié)束都是伴隨著“事務(wù)”的開始和結(jié)束。在達成如上的共識后我們就開始下面的一些討論和研究。

當事務(wù)管理器接收到提交請求時，它向事務(wù)中涉及的所有資源管理器發(fā)送一個prepare命令。然后，每個資源管理器執(zhí)行使事務(wù)持久所需的所有操作，并將保存事務(wù)日志映像的所有緩沖區(qū)刷新到磁盤。當每個資源管理器完成準備階段時，它將準備的成功或失敗返回給事務(wù)管理器。

如果事務(wù)管理器從所有資源管理器接收到成功的準備，它將向每個資源管理器發(fā)送提交命令。然后，資源管理器可以完成提交。如果所有資源管理器都報告提交成功，那么事務(wù)管理器將向應(yīng)用程序發(fā)送成功通知。如果任何資源管理器報告準備失敗，事務(wù)管理器將向每個資源管理器發(fā)送回滾命令，并指示向應(yīng)用程序提交失敗。

SQL SERVER 在什么時候會選擇什么樣的鎖的因素可能有哪些 

下面是部分SQL SERVER 中可以進行鎖的資源樣本

問題：為什么要這么多鎖的類型，MYSQL innodb 不僅僅有 row lock嗎？

作為一個商業(yè)數(shù)據(jù)庫，在設(shè)計之初SQL SERVER 考慮了下面一個圖（假設(shè)），使用低級鎖(如行鎖)可以降低兩個事務(wù)同時請求同一數(shù)據(jù)塊上的鎖的概率，從而提高并發(fā)性，但使用低級鎖還會增加鎖的數(shù)量和管理鎖所需的資源反之使用表或頁鎖可以降低開銷，但代價是降低并發(fā)性。

SQL Server數(shù)據(jù)庫引擎使用動態(tài)鎖定策略來確定最經(jīng)濟有效的鎖。數(shù)據(jù)庫引擎會根據(jù)模式和查詢的特性自動確定在執(zhí)行查詢時哪些鎖是最合適的。例如，為了減少鎖定的開銷，優(yōu)化器可以在執(zhí)行索引掃描時選擇索引中的頁級鎖定。這樣做的好處也是顯而易見，如果我有多行在一個PAGE中，并且都需要更改，系統(tǒng)會根據(jù)需要索引的資源來鎖定這個PAGE，而不是一個頁面里面的每個行，因為要考慮每個鎖的管理，申請，釋放，都是需要相關(guān)CPU 資源，內(nèi)存資源的，如果能在不影響并發(fā)度的情況下，鎖的粒度有效控制是有助于系統(tǒng)的信息的訪問和修改的。

并且SQL SERVER 也是可以在表的創(chuàng)建，或使用中進行鎖釋放可以自動進行升級的設(shè)置的，你可以打開表的鎖升級，或禁止掉他。

說到這里不得不說說SQL SERVER 鎖的歷史 SQL SERVER 7.0 之前的時候，（應(yīng)該不是我出生的時候，在很久很久很久久以前久以前），SQL SERVER 是不支持 ROW 鎖的，而僅僅支持 PAGE LOCK，并且一個頁面是 2KB ，在 SQL SERVER 7.0，他們將SQL SERVER 變?yōu)榱?8KB 的PAGE ，并且開始支持了 ROW LOCK。那到底為什么 SQL SERVER 不能做成和MYSQL 一樣，僅僅支持行鎖就好的數(shù)據(jù)庫，為什么單庫的商業(yè)數(shù)據(jù)庫還是有優(yōu)勢的（注意這是問句）

下面是一個行鎖的結(jié)構(gòu)

     鎖是一個64或128字節(jié)的內(nèi)存結(jié)構(gòu)(分別用于32位或64位機器)，每個持有或請求鎖的進程都有另外32或64字節(jié)。如果您需要對每一行都使用鎖，并且掃描一百萬行，那么您需要超過64MB的RAM來保存該進程的鎖。

一個語句在一個對象上持有的鎖的數(shù)量超過了一個閾值。舉例目前這個閾值是5000個鎖，超過就會觸發(fā)esclation，如果鎖分布在同一語句中的多個對象上，則不會發(fā)生鎖升級——例如，一個索引中的3000個鎖和另一個索引中的3000個鎖，另一方面鎖資源占用的內(nèi)存超過啟用內(nèi)存的40%，那么鎖會將發(fā)生升級。

那鎖升級到底是好不好，回答是  呵呵， 我想你明白我的意思。為什么

當觸發(fā)鎖升級時，如果存在沖突鎖，則會先增加更多的X鎖（我想你應(yīng)該是懂這個過程的），并且不同進程持有的同一表或分區(qū)上有并發(fā)的X鎖，則鎖升級嘗試將失敗。每次事務(wù)在同一對象上獲得另外更多個鎖時，SQL Server都會繼續(xù)嘗試升級鎖，成功后會將SQL Server索引或堆表上的所有行鎖進行釋放。

可以想想這個鎖升級從上到下的描述中，觸發(fā)他的伴隨的是大事務(wù)，占用更多的內(nèi)存，系統(tǒng)陷入了可能繁忙的狀態(tài)，那此時進行鎖升級，可能會成功，可能會失敗，同時成功后，鎖的級別將從ROW 變成 PAGE 或者更寬泛的鎖，系統(tǒng)將由 SHARE 變?yōu)?nbsp; ONLY You  use it . 其他的事務(wù)如果訪問你的資源，你是不是就不在管他們死活。

這還不是最糟糕的，鎖升級會導(dǎo)致更多的死鎖的出現(xiàn)，并且是莫名其妙的，看似兩個根本就無關(guān)的事務(wù)，也會鎖在一起，我想這時候如果你的領(lǐng)導(dǎo)來詢問你的時候為什么這么多莫名其妙的死鎖時，你是不是已經(jīng)可以，有理有據(jù)來和他 make  clear 一下了。

關(guān)于SQL SERVER鎖升級的investigation是怎樣的就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。