鎖是數(shù)據(jù)庫中的一個(gè)非常重要的概念，它主要用于多用戶環(huán)境下保證數(shù)據(jù)庫完整性和一致性。各種大型數(shù)據(jù)庫所采用的鎖的基本理論是一致的，但在具體實(shí)現(xiàn)上各有差別。SQL Server更強(qiáng)調(diào)由系統(tǒng)來管理鎖。在用戶有SQL請求時(shí)，系統(tǒng)分析請求，自動(dòng)在滿足鎖定條件和系統(tǒng)性能之間為數(shù)據(jù)庫加上適當(dāng)?shù)逆i，同時(shí)系統(tǒng)在運(yùn)行期間常常自動(dòng)進(jìn)行優(yōu)化處理，實(shí)行動(dòng)態(tài)加鎖。對(duì)于一般的用戶而言，通過系統(tǒng)的自動(dòng)鎖定管理機(jī)制基本可以滿足使用要求，但如果對(duì)數(shù)據(jù)安全、數(shù)據(jù)庫完整性和一致性有特殊要求，就需要了解SQL Server的鎖機(jī)制，掌握數(shù)據(jù)庫鎖定方法。
我們知道，多個(gè)用戶能夠同時(shí)操縱同一個(gè)數(shù)據(jù)庫中的數(shù)據(jù)，會(huì)發(fā)生數(shù)據(jù)不一致現(xiàn)象。即如果沒有鎖定且多個(gè)用戶同時(shí)訪問一個(gè)數(shù)據(jù)庫，則當(dāng)他們的事務(wù)同時(shí)使用相同的數(shù)據(jù)時(shí)可能會(huì)發(fā)生問題。這就是并發(fā)訪問：同一時(shí)間有多個(gè)用戶訪問同一資源，并發(fā)用戶中如果有用戶對(duì)資源做了修改，此時(shí)就會(huì)對(duì)其它用戶產(chǎn)生某些不利的影響，例如：
1：臟讀：一個(gè)用戶對(duì)一個(gè)資源做了修改，此時(shí)另外一個(gè)用戶正好讀取了這條被修改的記錄，然后，第一個(gè)用戶放棄修改，數(shù)據(jù)回到修改之前，這兩個(gè)不同的結(jié)果就是臟讀。
2：不可重復(fù)讀：一個(gè)用戶的一個(gè)操作是一個(gè)事務(wù)，這個(gè)事務(wù)分兩次讀取同一條記錄，如果第一次讀取后，有另外用戶修改了這個(gè)數(shù)據(jù)，然后第二次讀取的數(shù)據(jù)正好是其它用戶修改的數(shù)據(jù)，這樣造成兩次讀取的記錄不同，如果事務(wù)中鎖定這條記錄就可以避免。
3：幻讀：指用戶讀取一批記錄的情況，用戶兩次查詢同一條件的一批記錄，第一次查詢后，有其它用戶對(duì)這批數(shù)據(jù)做了修改，方法可能是修改，刪除，新增，第二次查詢時(shí)，會(huì)發(fā)現(xiàn)第一次查詢的記錄條目有的不在第二次查詢結(jié)果中，或者是第二次查詢的條目不在第一次查詢的內(nèi)容中。
所以，處理多用戶并發(fā)訪問的方法是加鎖。鎖是防止其他事務(wù)訪問指定的資源，控制、實(shí)現(xiàn)并發(fā)控制的一種主要手段。當(dāng)一個(gè)用戶鎖住數(shù)據(jù)庫中的某個(gè)對(duì)象時(shí)，其他用戶就不能再訪問該對(duì)象。加鎖對(duì)并發(fā)訪問的影響體現(xiàn)在鎖的粒度上。
為了控制鎖定的資源，應(yīng)該首先了解數(shù)據(jù)系統(tǒng)的空間管理。在SQL Server 2000系統(tǒng)中，最小的空間管理單位是頁，一個(gè)頁有8K。所有的數(shù)據(jù)、日志、索引都存放在頁上。另外，使用頁有一個(gè)限制，這就是表中的一行數(shù)據(jù)必須在同一個(gè)頁上，不能跨頁。頁上面的空間管理單位是盤區(qū)，一個(gè)盤區(qū)是8個(gè)連續(xù)的頁。表和索引的最小占用單位是盤區(qū)。數(shù)據(jù)庫是由一個(gè)或者多個(gè)表或者索引組成，即是由多個(gè)盤區(qū)組成。放在一個(gè)表上的鎖限制對(duì)整個(gè)表的并發(fā)訪問；放在盤區(qū)上的鎖限制了對(duì)整個(gè)盤區(qū)的訪問；放在數(shù)據(jù)頁上的鎖限制了對(duì)整個(gè)數(shù)據(jù)頁的訪問；放在行上的鎖只限制對(duì)該行的并發(fā)訪問。
SQL Server 2000 具有多粒度鎖定，允許一個(gè)事務(wù)鎖定不同類型的的資源。為了使鎖定的成本減至最少，SQL Server 自動(dòng)將資源鎖定在適合任務(wù)的級(jí)別。SQL Server 可以鎖定行、頁、擴(kuò)展盤區(qū)、表、庫等資源。
主要目的：根據(jù)操作的數(shù)據(jù)判斷鎖定級(jí)別，平衡數(shù)據(jù)資源和系統(tǒng)資源，級(jí)別是動(dòng)態(tài)的。
1.行是可以鎖定的最小空間, 行級(jí)鎖占用的數(shù)據(jù)資源最少，所以在事務(wù)的處理過程中，允許其他事務(wù)繼續(xù)操縱同一個(gè)表或者同一個(gè)頁的其他數(shù)據(jù)，大大降低了其他事務(wù)等待處理的時(shí)間，提高了系統(tǒng)的并發(fā)性。
2. 頁級(jí)鎖是指在事務(wù)的操縱過程中，無論事務(wù)處理數(shù)據(jù)的多少，每一次都鎖定一頁，在這個(gè)頁上的數(shù)據(jù)不能被其他事務(wù)操縱。在SQL Server 7.0以前，使用的是頁級(jí)鎖。頁級(jí)鎖鎖定的資源比行級(jí)鎖鎖定的數(shù)據(jù)資源多。在頁級(jí)鎖中，即使是一個(gè)事務(wù)只操縱頁上的一行數(shù)據(jù)，那么該頁上的其他數(shù)據(jù)行也不能被其他事務(wù)使用。因此，當(dāng)使用頁級(jí)鎖時(shí)，會(huì)出現(xiàn)數(shù)據(jù)的浪費(fèi)現(xiàn)象，也就是說，在同一個(gè)頁上會(huì)出現(xiàn)數(shù)據(jù)被占用卻沒有使用的現(xiàn)象。在這種現(xiàn)象中，數(shù)據(jù)的浪費(fèi)最多不超過一個(gè)頁上的數(shù)據(jù)行。
2. 表級(jí)鎖也是一個(gè)非常重要的鎖。表級(jí)鎖是指事務(wù)在操縱某一個(gè)表的數(shù)據(jù)時(shí)，鎖定了這個(gè)數(shù)據(jù)所在的整個(gè)表，其他事務(wù)不能訪問該表中的其他數(shù)據(jù)。當(dāng)事務(wù)處理的數(shù)據(jù)量比較大時(shí)，一般使用表級(jí)鎖。表級(jí)鎖的特點(diǎn)是使用比較少的系統(tǒng)資源，但是卻占用比較多的數(shù)據(jù)資源。與行級(jí)鎖和頁級(jí)鎖相比，表級(jí)鎖占用的系統(tǒng)資源例如內(nèi)存比較少，但是占用的數(shù)據(jù)資源卻是最大。在表級(jí)鎖時(shí)，有可能出現(xiàn)數(shù)據(jù)的大量浪費(fèi)現(xiàn)象，因?yàn)楸砑?jí)鎖鎖定整個(gè)表，那么其他的事務(wù)都不能操縱表中的其他數(shù)據(jù)。
3. 盤區(qū)鎖是一種特殊類型的鎖，只能用在一些特殊的情況下。簇級(jí)鎖就是指事務(wù)占用一個(gè)盤區(qū)，這個(gè)盤區(qū)不能同時(shí)被其他事務(wù)占用。例如在創(chuàng)建數(shù)據(jù)庫和創(chuàng)建表時(shí)，系統(tǒng)分配物理空間時(shí)使用這種類型的鎖。系統(tǒng)是按照盤區(qū)分配空間的。當(dāng)系統(tǒng)分配空間時(shí)，使用盤區(qū)鎖，防止其他事務(wù)同時(shí)使用同一個(gè)盤區(qū)。當(dāng)系統(tǒng)完成分配空間之后，就不再使用這種類型的盤區(qū)鎖。特別是，當(dāng)涉及到對(duì)數(shù)據(jù)操作的事務(wù)時(shí)，不使用盤區(qū)鎖。
4. 數(shù)據(jù)庫級(jí)鎖是指鎖定整個(gè)數(shù)據(jù)庫，防止任何用戶或者事務(wù)對(duì)鎖定的數(shù)據(jù)庫進(jìn)行訪問。數(shù)據(jù)庫級(jí)鎖是一種非常特殊的鎖，它只是用于數(shù)據(jù)庫的恢復(fù)操作過程中。這種等級(jí)的鎖是一種最高等級(jí)的鎖，因?yàn)樗刂普麄€(gè)數(shù)據(jù)庫的操作。只要對(duì)數(shù)據(jù)庫進(jìn)行恢復(fù)操作，那么就需要設(shè)置數(shù)據(jù)庫為單用戶模式，這樣系統(tǒng)就能防止其他用戶對(duì)該數(shù)據(jù)庫進(jìn)行各種操作。
行級(jí)鎖是一種最優(yōu)鎖，因?yàn)樾屑?jí)鎖不可能出現(xiàn)數(shù)據(jù)既被占用又沒有使用的浪費(fèi)現(xiàn)象。但是，如果用戶事務(wù)中頻繁對(duì)某個(gè)表中的多條記錄操作，將導(dǎo)致對(duì)該表的許多記錄行都加上了行級(jí)鎖，數(shù)據(jù)庫系統(tǒng)中鎖的數(shù)目會(huì)急劇增加，這樣就加重了系統(tǒng)負(fù)荷，影響系統(tǒng)性能。因此，在SQL Server中，還支持鎖升級(jí)(lock escalation)。所謂鎖升級(jí)是指調(diào)整鎖的粒度，將多個(gè)低粒度的鎖替換成少數(shù)的更高粒度的鎖，以此來降低系統(tǒng)負(fù)荷。在SQL Server中當(dāng)一個(gè)事務(wù)中的鎖較多，達(dá)到鎖升級(jí)門限時(shí)，系統(tǒng)自動(dòng)將行級(jí)鎖和頁面鎖升級(jí)為表級(jí)鎖。特別值得注意的是，在SQL Server中，鎖的升級(jí)門限以及鎖升級(jí)是由系統(tǒng)自動(dòng)來確定的，不需要用戶設(shè)置。鎖定在較小的粒度（例如行）可以增加并發(fā)但需要較大的開銷，因?yàn)槿绻i定了許多行，則需要控制更多的鎖。鎖定在較大的粒度（例如表）就并發(fā)而言是相當(dāng)昂貴的，因?yàn)殒i定整個(gè)表限制了其它事務(wù)對(duì)表中任意部分進(jìn)行訪問，但要求的開銷較低，因?yàn)樾枰S護(hù)的鎖較少。鎖定在較小的粒度（例如行）可以增加并發(fā)但需要較大的開銷，因?yàn)槿绻i定了許多行，則需要控制更多的鎖。鎖定在較大的粒度（例如表）就并發(fā)而言是相當(dāng)昂貴的，因?yàn)殒i定整個(gè)表限制了其它事務(wù)對(duì)表中任意部分進(jìn)行訪問，但要求的開銷較低，因?yàn)樾枰S護(hù)的鎖較少。
在SQL Server數(shù)據(jù)庫中加鎖時(shí)，除了可以對(duì)不同的資源加鎖，還可以使用不同程度的加鎖方式，即鎖有多種模式，SQL Server中鎖模式包括：
1．共享鎖 SQL Server中，共享鎖用于所有的只讀數(shù)據(jù)操作。共享鎖是非獨(dú)占的，允許多個(gè)并發(fā)事務(wù)讀取其鎖定的資源。默認(rèn)情況下，數(shù)據(jù)被讀取后，SQL Server立即釋放共享鎖。例如，執(zhí)行查詢“SELECT * FROM AUTHORS”時(shí)，首先鎖定第一頁，讀取之后，釋放對(duì)第一頁的鎖定，然后鎖定第二頁。這樣，就允許在讀操作過程中，修改未被鎖定的第一頁。但是，事務(wù)隔離級(jí)別連接選項(xiàng)設(shè)置和SELECT語句中的鎖定設(shè)置都可以改變SQL Server的這種默認(rèn)設(shè)置。例如，“ SELECT * FROM AUTHORS HOLDLOCK”就要求在整個(gè)查詢過程中，保持對(duì)表的鎖定，直到查詢完成才釋放鎖定。
2．更新鎖 更新鎖在修改操作的初始化階段用來鎖定可能要被修改的資源，這樣可以避免使用共享鎖造成的死鎖現(xiàn)象。因?yàn)槭褂霉蚕礞i時(shí)，修改數(shù)據(jù)的操作分為兩步，首先獲得一個(gè)共享鎖，讀取數(shù)據(jù)，然后將共享鎖升級(jí)為排它鎖，然后再執(zhí)行修改操作。這樣如果同時(shí)有兩個(gè)或多個(gè)事務(wù)同時(shí)對(duì)一個(gè)事務(wù)申請了共享鎖，在修改數(shù)據(jù)的時(shí)候，這些事務(wù)都要將共享鎖升級(jí)為排它鎖。這時(shí)，這些事務(wù)都不會(huì)釋放共享鎖而是一直等待對(duì)方釋放，這樣就造成了死鎖。如果一個(gè)數(shù)據(jù)在修改前直接申請更新鎖，在數(shù)據(jù)修改的時(shí)候再升級(jí)為排它鎖，就可以避免死鎖。
3．排它鎖 排它鎖是為修改數(shù)據(jù)而保留的。它所鎖定的資源，其他事務(wù)不能讀取也不能修改。
4．結(jié)構(gòu)鎖 執(zhí)行表的數(shù)據(jù)定義語言 (DDL) 操作（例如添加列或除去表）時(shí)使用架構(gòu)修改 (Sch-M) 鎖。當(dāng)編譯查詢時(shí)，使用架構(gòu)穩(wěn)定性 (Sch-S) 鎖。架構(gòu)穩(wěn)定性 (Sch-S) 鎖不阻塞任何事務(wù)鎖，包括排它鎖。因此在編譯查詢時(shí)，其它事務(wù)（包括在表上有排它鎖的事務(wù)）都能繼續(xù)運(yùn)行。但不能在表上執(zhí)行 DDL 操作。
5．意向鎖 意向鎖說明SQL Server有在資源的低層獲得共享鎖或排它鎖的意向。例如，表級(jí)的共享意向鎖說明事務(wù)意圖將排它鎖釋放到表中的頁或者行。意向鎖又可以分為共享意向鎖、獨(dú)占意向鎖和共享式獨(dú)占意向鎖。共享意向鎖說明事務(wù)意圖在共享意向鎖所鎖定的低層資源上放置共享鎖來讀取數(shù)據(jù)。獨(dú)占意向鎖說明事務(wù)意圖在共享意向鎖所鎖定的低層資源上放置排它鎖來修改數(shù)據(jù)。共享式排它鎖說明事務(wù)允許其他事務(wù)使用共享鎖來讀取頂層資源，并意圖在該資源低層上放置排它鎖。
6．大容量更新鎖 當(dāng)將數(shù)據(jù)大容量復(fù)制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 設(shè)置了 table lock on bulk 表選項(xiàng)時(shí)，將使用大容量更新 鎖。大容量更新鎖允許進(jìn)程將數(shù)據(jù)并發(fā)地大容量復(fù)制到同一表，同時(shí)防止其它不進(jìn)行大容量復(fù)制數(shù)據(jù)的進(jìn)程訪問該表。
從程序員的角度看：分為樂觀鎖和悲觀鎖。
1. 樂觀鎖：完全依靠數(shù)據(jù)庫來管理鎖的工作。
2. 悲觀鎖：程序員自己管理數(shù)據(jù)或?qū)ο笊系逆i處理。
程序員不用去設(shè)置、控制這些鎖，SQLServer通過設(shè)置事務(wù)的隔離級(jí)別自動(dòng)管理鎖的設(shè)置和控制。鎖管理器通過查詢分析器分析待執(zhí)行的sql語句，來判斷語句將會(huì)訪問哪些資源，進(jìn)行什么操作，然后結(jié)合設(shè)定的隔離級(jí)別自動(dòng)分配管理需要用到的鎖。 而且在鎖的數(shù)目太多時(shí)，系統(tǒng)會(huì)自動(dòng)進(jìn)行鎖升級(jí)。如前所述，升級(jí)的門限由系統(tǒng)自動(dòng)配置，并不需要用戶配置。 在實(shí)際應(yīng)用中，有時(shí)為了應(yīng)用程序正確運(yùn)行和保持?jǐn)?shù)據(jù)的一致性，必須人為地給數(shù)據(jù)庫的某個(gè)表加鎖。比如，在某應(yīng)用程序的一個(gè)事務(wù)操作中，需要根據(jù)一編號(hào)對(duì)幾個(gè)數(shù)據(jù)表做統(tǒng)計(jì)操作，為保證統(tǒng)計(jì)數(shù)據(jù)時(shí)間的一致性和正確性，從統(tǒng)計(jì)第一個(gè)表開始到全部表結(jié)束，其他應(yīng)用程序或事務(wù)不能再對(duì)這幾個(gè)表寫入數(shù)據(jù)，這個(gè)時(shí)候，該應(yīng)用程序希望在從統(tǒng)計(jì)第一個(gè)數(shù)據(jù)表開始或在整個(gè)事務(wù)開始時(shí)能夠由程序人為地（顯式地）鎖定這幾個(gè)表，這就需要用到手工加鎖（也稱顯式加鎖）技術(shù)?？梢允褂?span lang="EN-US"> SELECT、INSERT、UPDATE 和 DELETE 語句指定表級(jí)鎖定提示的范圍，以引導(dǎo) Microsoft SQL Server使用所需的鎖類型。當(dāng)需要對(duì)對(duì)象所獲得鎖類型進(jìn)行更精細(xì)控制時(shí)，使用表級(jí)鎖定提示更改默認(rèn)的鎖定行為。
所指定的表級(jí)鎖定提示有如下幾種：
1． HOLDLOCK: 在該表上保持共享鎖，直到整個(gè)事務(wù)結(jié)束，而不是在語句執(zhí)行完立即釋放所添加的鎖。持有共享鎖，直到整個(gè)事務(wù)完成，應(yīng)該在被鎖對(duì)象不需要時(shí)立即釋放，等于SERIALIZABLE事務(wù)隔離級(jí)別
2． NOLOCK：不添加共享鎖和排它鎖，當(dāng)這個(gè)選項(xiàng)生效后，可能讀到未提交讀的數(shù)據(jù)或“臟數(shù)據(jù)”，這個(gè)選項(xiàng)僅僅應(yīng)用于SELECT語句。允許臟讀，等于 READ UNCOMMITTED事務(wù)隔離級(jí)別
3．PAGLOCK：指定添加頁鎖（否則通?？赡芴砑颖礞i）。在使用一個(gè)表鎖的地方用多個(gè)頁鎖。
4．READCOMMITTED用與運(yùn)行在提交讀隔離級(jí)別的事務(wù)相同的鎖語義執(zhí)行掃描。默認(rèn)情況下，SQL Server 2000 在此隔離級(jí)別上操作。
5．READPAST: 跳過已經(jīng)加鎖的數(shù)據(jù)行，這個(gè)選項(xiàng)將使事務(wù)讀取數(shù)據(jù)時(shí)跳過那些已經(jīng)被其他事務(wù)鎖定的數(shù)據(jù)行，而不是阻塞直到其他事務(wù)釋放鎖，READPAST僅僅應(yīng)用于READ COMMITTED隔離性級(jí)別下事務(wù)操作中的SELECT語句操作。
6．READUNCOMMITTED：等同于NOLOCK。
7．REPEATABLEREAD：設(shè)置事務(wù)為可重復(fù)讀隔離性級(jí)別。
8．ROWLOCK：使用行級(jí)鎖，而不使用粒度更粗的頁級(jí)鎖和表級(jí)鎖。
9．SERIALIZABLE：用與運(yùn)行在可串行讀隔離級(jí)別的事務(wù)相同的鎖語義執(zhí)行掃描。等同于 HOLDLOCK。
10．TABLOCK：指定使用表級(jí)鎖，而不是使用行級(jí)或頁面級(jí)的鎖，SQL Server在該語句執(zhí)行完后釋放這個(gè)鎖，而如果同時(shí)指定了HOLDLOCK，該鎖一直保持到這個(gè)事務(wù)結(jié)束。
11．TABLOCKX：指定在表上使用排它鎖，這個(gè)鎖可以阻止其他事務(wù)讀或更新這個(gè)表的數(shù)據(jù)，直到這個(gè)語句或整個(gè)事務(wù)結(jié)束。
12．UPDLOCK ：指定在讀表中數(shù)據(jù)時(shí)設(shè)置更新 鎖（update lock）而不是設(shè)置共享鎖，該鎖一直保持到這個(gè)語句或整個(gè)事務(wù)結(jié)束，使用UPDLOCK的作用是允許用戶先讀取數(shù)據(jù)（而且不阻塞其他用戶讀數(shù)據(jù)），并且保證在后來再更新數(shù)據(jù)時(shí)，這一段時(shí)間內(nèi)這些數(shù)據(jù)沒有被其他用戶修改。
應(yīng)用程序鎖:應(yīng)用程序鎖就是客戶端代碼生成的鎖，而不是sql server本身生成的鎖
處理應(yīng)用程序鎖的兩個(gè)過程：
sp_getapplock 鎖定應(yīng)用程序資源
sp_releaseapplock 為應(yīng)用程序資源解鎖
注意: 鎖定數(shù)據(jù)庫的一個(gè)表的區(qū)別
SELECT * FROM table WITH (HOLDLOCK) 其他事務(wù)可以讀取表，但不能更新刪除
SELECT * FROM table WITH (TABLOCKX) 其他事務(wù)不能讀取表,更新和刪除
注：NOLOCK的使用：
大家在寫查詢時(shí)，往往會(huì)在表后面加一個(gè)nolock或者是with(nolock)，其目的就是查詢是不鎖定表，解決阻塞死鎖，對(duì)于非銀行等嚴(yán)格要求事務(wù)的行業(yè)，搜索記錄中出現(xiàn)或者不出現(xiàn)某條記錄，都是在可容忍范圍內(nèi)，所以碰到死鎖，應(yīng)該首先考慮，我們業(yè)務(wù)邏輯是否能容忍出現(xiàn)或者不出現(xiàn)某些記錄，而不是尋求對(duì)雙方都加鎖條件下如何解鎖的問題。
1：數(shù)據(jù)量特別大的表，犧牲數(shù)據(jù)安全性來提升性能是可以考慮的；
2：允許出現(xiàn)臟讀現(xiàn)象的業(yè)務(wù)邏輯，反之一些數(shù)據(jù)完整性要求比較嚴(yán)格的場景就不合適了，像金融方面等。
3：數(shù)據(jù)不經(jīng)常修改的表，這樣會(huì)省于鎖定表的時(shí)間來大大加快查詢速度。
綜上所述，如果在項(xiàng)目中的每個(gè)查詢的表后面都加nolock，這種做法并不科學(xué)，起碼特別費(fèi)時(shí)間，不如行版本控制來的直接有效。而且會(huì)存在不可預(yù)期的技術(shù)問題。應(yīng)該有選擇性的挑選最適合的表來放棄共享鎖的使用。
nolock和with(nolock)的小區(qū)別：
1：SQL05中的同義詞，只支持with(nolock);
2：with(nolock)的寫法非常容易再指定索引。
3：跨服務(wù)器查詢語句時(shí)不能用with (nolock) 只能用nolock ，同一個(gè)服務(wù)器查詢時(shí)則with (nolock)和nolock都可以用
在查詢語句中使用 NOLOCK 和 READPAST ：
NOLOCK 和 READPAST 都是處理查詢、插入、刪除等操作時(shí)候，如何應(yīng)對(duì)鎖住的數(shù)據(jù)記錄。但是這時(shí)候一定要注意NOLOCK 和 READPAST的局限性，簡單來說：
NOLOCK 可能把沒有提交事務(wù)的數(shù)據(jù)也顯示出來．
READPAST 會(huì)把被鎖住的行不顯示出來
不使用 NOLOCK 和 READPAST，在 Select 操作時(shí)候則有可能報(bào)錯(cuò)誤：事務(wù)(進(jìn)程 ID **)與另一個(gè)進(jìn)程被死鎖在 鎖資源上，并且已被選作死鎖犧牲品。
演示：為了演示兩個(gè)事務(wù)死鎖的情況，我們下面的測試都需要在SQL Server Management Studio中打開兩個(gè)查詢窗口。保證事務(wù)不被干擾。
演示一： 沒有提交的事務(wù)，NOLOCK 和 READPAST處理的策略：
查詢窗口一請執(zhí)行如下腳本：
CREATE TABLE t1 (c1 int IDENTITY(1,1), c2 int)
go
BEGIN TRANSACTION
insert t1(c2) values(1)
在查詢窗口一執(zhí)行后，查詢窗口二執(zhí)行如下腳本：
select count(*) from t1 WITH(NOLOCK)
select count(*) from t1 WITH(READPAST)
結(jié)果與分析： 查詢窗口二依次顯示統(tǒng)計(jì)結(jié)果為： 1、0
查詢窗口一的命令沒有提交事務(wù)，所以 READPAST 不會(huì)計(jì)算沒有提交事務(wù)的這一條記錄，這一條被鎖住了，READPAST 看不到；而NOLOCK則可以看到被鎖住的這一條記錄。
如果這時(shí)候我們在查詢窗口二中執(zhí)行：
select count(*) from t1
就會(huì)看到這個(gè)執(zhí)行很久不能執(zhí)行完畢，因?yàn)檫@個(gè)查詢遇到了一個(gè)死鎖。
清除掉這個(gè)測試環(huán)境，需要在查詢窗口一中再執(zhí)行如下語句：
ROLLBACK TRANSACTION
drop table t1
演示二：對(duì)被鎖住的記錄，NOLOCK 和 READPAST處理的策略
查詢窗口一中執(zhí)行如下語句：
CREATE TABLE t2 (UserID int , NickName nvarchar(50))
go
insert t2(UserID,NickName) values(1,'郭紅俊')
insert t2(UserID,NickName) values(2,'蟈蟈俊')
go
BEGIN TRANSACTION
update t2 set NickName = '蟈蟈俊.net' where UserID = 2
請?jiān)诓樵兇翱诙袌?zhí)行如下腳本：
select * from t2 WITH(NOLOCK) where UserID = 2
select * from t2 WITH(READPAST) where UserID = 2
結(jié)果與分析：
查詢窗口二中， NOLOCK 對(duì)應(yīng)的查詢結(jié)果中我們看到了修改后的記錄，READPAST對(duì)應(yīng)的查詢結(jié)果中我們沒有看到任何一條記錄。 這種情況下就可能發(fā)生臟讀
五． 死鎖問題
在數(shù)據(jù)庫系統(tǒng)中，死鎖是指多個(gè)用戶（進(jìn)程）分別鎖定了一個(gè)資源，并又試圖請求鎖定對(duì)方已經(jīng)鎖定的資源，這就產(chǎn)生了一個(gè)鎖定請求環(huán)，導(dǎo)致多個(gè)用戶（進(jìn)程）都處于等待對(duì)方釋放所鎖定資源的狀態(tài)。這種死鎖是最典型的死鎖形式,，例如在同一時(shí)間內(nèi)有兩個(gè)事務(wù)A和B，事務(wù)A有兩個(gè)操作：鎖定表part和請求訪問表supplier；事務(wù)B也有兩個(gè)操作：鎖定表supplier和請求訪問表part。結(jié)果，事務(wù)A和事務(wù)B之間發(fā)生了死鎖。
死鎖的第二種情況是，當(dāng)在一個(gè)數(shù)據(jù)庫中時(shí)，有若干個(gè)長時(shí)間運(yùn)行的事務(wù)執(zhí)行并行的操作，當(dāng)查詢分析器處理一種非常復(fù)雜的查詢例如連接查詢時(shí)，那么由于不能控制處理的順序，有可能發(fā)生死鎖現(xiàn)象。
在SQL Server中，系統(tǒng)能夠自動(dòng)定期搜索和處理死鎖問題。系統(tǒng)在每次搜索中標(biāo)識(shí)所有等待鎖定請求的進(jìn)程會(huì)話，如果在下一次搜索中該被標(biāo)識(shí)的進(jìn)程仍處于等待狀態(tài)，SQL Server就開始遞歸死鎖搜索。當(dāng)搜索檢測到鎖定請求環(huán)時(shí)，SQL Server 通過自動(dòng)選擇可以打破死鎖的線程（死鎖犧牲品）來結(jié)束死鎖。SQL Server 回滾作為死鎖犧牲品的事務(wù)，通知線程的應(yīng)用程序（通過返回 1205 號(hào)錯(cuò)誤信息），取消線程的當(dāng)前請求，然后允許不間斷線程的事務(wù)繼續(xù)進(jìn)行。SQL Server 通常選擇運(yùn)行撤消時(shí)花費(fèi)最少的事務(wù)的線程作為死鎖犧牲品。另外，用戶可以使用 SET 語句將會(huì)話的 DEADLOCK_PRIORITY 設(shè)置為 LOW。DEADLOCK_PRIORITY 選項(xiàng)控制在死鎖情況下如何衡量會(huì)話的重要性。如果會(huì)話的設(shè)置為 LOW ，則當(dāng)會(huì)話陷入死鎖情況時(shí)將成為首選犧牲品。
理解了死鎖的概念，在應(yīng)用程序中就可以采用下面的一些方法來盡量避免死鎖了：
（1）合理安排表訪問順序。
（2）在事務(wù)中盡量避免用戶干預(yù)，盡量使一個(gè)事務(wù)處理的任務(wù)少些, 保持事務(wù)簡短并在一個(gè)批處理中。
（3）數(shù)據(jù)訪問時(shí)域離散法，數(shù)據(jù)訪問時(shí)域離散法是指在客戶機(jī)/服務(wù)器結(jié)構(gòu)中，采取各種控制手段控制對(duì)數(shù)據(jù)庫或數(shù)據(jù)庫中的對(duì)象訪問時(shí)間段。主要通過以下方式實(shí)現(xiàn): 合理安排后臺(tái)事務(wù)的執(zhí)行時(shí)間，采用工作流對(duì)后臺(tái)事務(wù)進(jìn)行統(tǒng)一管理。工作流在管理任務(wù)時(shí)，一方面限制同一類任務(wù)的線程數(shù)（往往限制為1個(gè)），防止資源過多占用; 另一方面合理安排不同任務(wù)執(zhí)行時(shí)序、時(shí)間，盡量避免多個(gè)后臺(tái)任務(wù)同時(shí)執(zhí)行，另外， 避免在前臺(tái)交易高峰時(shí)間運(yùn)行后臺(tái)任務(wù)。
（4）數(shù)據(jù)存儲(chǔ)空間離散法。數(shù)據(jù)存儲(chǔ)空間離散法是指采取各種手段，將邏輯上在一個(gè)表中的數(shù)據(jù)分散到若干離散的空間上去，以便改善對(duì)表的訪問性能。主要通過以下方法實(shí)現(xiàn): 第一，將大表按行或列分解為若干小表; 第二，按不同的用戶群分解。
（5）使用盡可能低的隔離性級(jí)別。隔離性級(jí)別是指為保證數(shù)據(jù)庫數(shù)據(jù)的完整性和一致性而使多用戶事務(wù)隔離的程度，SQL92定義了4種隔離性級(jí)別：未提交讀、提交讀、可重復(fù)讀和可串行。如果選擇過高的隔離性級(jí)別，如可串行，雖然系統(tǒng)可以因?qū)崿F(xiàn)更好隔離性而更大程度上保證數(shù)據(jù)的完整性和一致性，但各事務(wù)間沖突而死鎖的機(jī)會(huì)大大增加，大大影響了系統(tǒng)性能。
（6）使用綁定連接, 綁定連接允許兩個(gè)或多個(gè)事務(wù)連接共享事務(wù)和鎖，而且任何一個(gè)事務(wù)連接要申請鎖如同另外一個(gè)事務(wù)要申請鎖一樣，因此可以允許這些事務(wù)共享數(shù)據(jù)而不會(huì)有加鎖的沖突。
可以使用SET DEADLOCK_PRIORITY控制在發(fā)生死鎖情況時(shí)會(huì)話的反應(yīng)方式。如果兩個(gè)進(jìn)程都鎖定數(shù)據(jù)，并且直到其它進(jìn)程釋放自己的鎖時(shí)，每個(gè)進(jìn)程才能釋放自己的鎖，即發(fā)生死鎖情況。
SET LOCK_TIMEOUT 設(shè)置允許應(yīng)用程序設(shè)置語句等待阻塞資源的最長時(shí)間。當(dāng)語句等待的時(shí)間大于 LOCK_TIMEOUT 設(shè)置時(shí)，系統(tǒng)將自動(dòng)取消阻塞的語句，并給應(yīng)用程序返回"已超過了鎖請求超時(shí)時(shí)段"的 1222 號(hào)錯(cuò)誤信息
示例：下例將鎖超時(shí)期限設(shè)置為 1,800 毫秒。 SET LOCK_TIMEOUT 1800
查看鎖的信息
1 執(zhí)行 EXEC SP_LOCK 報(bào)告有關(guān)鎖的信息
2 查詢分析器中按Ctrl+F2可以看到鎖的信息
幾個(gè)有關(guān)鎖的問題
1. 如何鎖一個(gè)表的某一行
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM table ROWLOCK WHERE id = 1
2 鎖定數(shù)據(jù)庫的一個(gè)表
SELECT * FROM table WITH (HOLDLOCK)
3 不鎖定數(shù)據(jù)庫的一個(gè)表，可能有臟數(shù)據(jù)
SELECT * FROM table WITH (NOLOCK)
各數(shù)據(jù)庫加鎖語句：
sybase： pdate 表 set col1=col1 where 1=0
MSSQL：select * from 表 (tablockx) where 1=0
oracle： LOCK TABLE 表 IN EXCLUSIVE MODE
加鎖后其它人不可操作，直到加鎖用戶解鎖，用commit或rollback解鎖
設(shè)table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
1）排它鎖
在第一個(gè)連接中執(zhí)行以下語句
begin tran
update table1 set A='aa' where B='b2' waitfor delay '00:00:30'--等待30秒
commit tran
在第二個(gè)連接中執(zhí)行以下語句
begin tran
select * from table1 where B='b2'
commit tran
若同時(shí)執(zhí)行上述兩個(gè)語句，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即要等待30秒
2）共享鎖
在第一個(gè)連接中執(zhí)行以下語句
begin tran
select * from table1 holdlock --holdlock人為加鎖
where B='b2' waitfor delay '00:00:30' --等待30秒
commit tran
在第二個(gè)連接中執(zhí)行以下語句
begin tran
select A,C from table1 where B='b2'
update table1 set A='aa' where B='b2'
commit tran
若同時(shí)執(zhí)行上述兩個(gè)語句，則第二個(gè)連接中的select查詢可以執(zhí)行而update必須等待第一個(gè)事務(wù)釋放共享鎖轉(zhuǎn)為排它鎖后才能執(zhí)行 即要等待30秒
3）死鎖
增設(shè)table2(D,E)
D E
d1 e1
d2 e2
在第一個(gè)連接中執(zhí)行以下語句
begin tran
update table1 set A='aa' where B='b2' waitfor delay '00:00:30'
update table2 set D='d5' where E='e1'
commit tran
在第二個(gè)連接中執(zhí)行以下語句
begin tran
update table2 set D='d5' where E='e1' waitfor delay '00:00:10'
update table1 set A='aa' where B='b2'
commit tran
同時(shí)執(zhí)行，系統(tǒng)會(huì)檢測出死鎖，并中止進(jìn)程
六． 行版本控制：
當(dāng)啟用了基于行版本控制的隔離級(jí)別時(shí)，數(shù)據(jù)庫引擎將維護(hù)修改的每一行的版本。應(yīng)用程序可以指定事務(wù)使用行版本查看事務(wù)或查詢開始時(shí)存在的數(shù)據(jù)，而不是使用鎖保護(hù)所有讀取。通過使用行版本控制，讀取操作阻止其他事務(wù)的可能性將大大降低。也就是相當(dāng)于針對(duì)所有的表在查詢時(shí)都會(huì)加上nolock，同樣會(huì)產(chǎn)生臟讀的現(xiàn)象，但差別在于在一個(gè)統(tǒng)一管理的地方。說到了基于行版本控制的隔離級(jí)別，這里有必要說下隔離級(jí)別的概念。
隔離級(jí)別的用處：控制鎖的應(yīng)用，即什么場景應(yīng)用什么樣的鎖機(jī)制。
最終目的：解決并發(fā)處理帶來的種種問題。
隔離級(jí)別的分類：
1：未提交讀,隔離事務(wù)的最低級(jí)別，只能保證不讀取物理上損壞的數(shù)據(jù);
2：已提交讀,數(shù)據(jù)庫引擎的默認(rèn)級(jí)；
3：可重復(fù)讀；
4：可序列化；隔離事務(wù)的最高級(jí)別，事務(wù)之間完全隔離。
聯(lián)系客服
