伴隨著時(shí)間的增長(zhǎng)，公司的數(shù)據(jù)庫(kù)會(huì)越來(lái)越多，查詢(xún)速度也會(huì)越來(lái)越慢。打開(kāi)數(shù)據(jù)庫(kù)看到幾十萬(wàn)條的數(shù)據(jù)，查詢(xún)起來(lái)難免不廢時(shí)間。

　　要提升SQL的查詢(xún)效能，一般來(lái)說(shuō)大家會(huì)以建立索引(index)為第一考慮。其實(shí)除了index的建立之外，當(dāng)我們?cè)谙耂QL Command時(shí)，在語(yǔ)法中加一段WITH (NOLOCK)可以改善在線(xiàn)大量查詢(xún)的環(huán)境中數(shù)據(jù)集被LOCK的現(xiàn)象藉此改善查詢(xún)的效能。

　　不過(guò)有一點(diǎn)千萬(wàn)要注意的就是，WITH (NOLOCK)的SQL SELECT有可能會(huì)造成Dirty Read，就是讀到無(wú)效的數(shù)據(jù)。

　　下面對(duì)于SQLSERVER的鎖爭(zhēng)用及nolock,rowlock的原理及使用作一個(gè)簡(jiǎn)單描述：

鎖爭(zhēng)用的描述

　　那些不僅僅使用行級(jí)鎖的數(shù)據(jù)庫(kù)使用一種稱(chēng)為混和鎖(lock escalation)的技術(shù)來(lái)獲取較高的性能。除非很明確知道是針對(duì)整個(gè)數(shù)據(jù)表，否則這些數(shù)據(jù)庫(kù)的做法是開(kāi)始使用行級(jí)鎖， 然后隨著修改的數(shù)據(jù)增多，開(kāi)始使用大范圍的鎖機(jī)制。

　　不幸的是，這種混和鎖的方法會(huì)產(chǎn)生和放大新的問(wèn)題：死鎖。如果兩個(gè)用戶(hù)以相反的順序修改位于不同表的記錄，而這兩條記錄雖然邏輯上不相關(guān)， 但是物理上是相鄰的，操作就會(huì)先引發(fā)行鎖，然后升級(jí)為頁(yè)面鎖。這樣， 兩個(gè)用戶(hù)都需要對(duì)方鎖定的東西，就造成了死鎖。

例如：

　　用戶(hù)A修改表A的一些記錄，引發(fā)的頁(yè)面鎖不光鎖定正在修改的記錄，還會(huì)有很多其它記錄也會(huì)被鎖定。

　　用戶(hù)B修改表B的一些記錄，引發(fā)的頁(yè)面鎖鎖定用戶(hù)A和其它正在修改的數(shù)據(jù)。

　　用戶(hù)A想修改用戶(hù)B在表B中鎖定(并不一定正在修改的)數(shù)據(jù)。

　　用戶(hù)B想修改或者僅僅想訪(fǎng)問(wèn)用戶(hù)A在表A中鎖定(并不一定正在修改)的數(shù)據(jù)。

　　為了解決該問(wèn)題，數(shù)據(jù)庫(kù)會(huì)經(jīng)常去檢測(cè)是否有死鎖存在，如果有，就把其中的一個(gè)事務(wù)撤銷(xiāo)，好讓另一個(gè)事務(wù)能順利完成。一般來(lái)說(shuō)，都是撤銷(xiāo) 那個(gè)修改數(shù)據(jù)量少的事務(wù)，這樣回滾的開(kāi)銷(xiāo)就比較少。使用行級(jí)鎖的數(shù)據(jù)庫(kù) 很少會(huì)有這個(gè)問(wèn)題，因?yàn)閮蓚€(gè)用戶(hù)同時(shí)修改同一條記錄的可能性極小，而且由于極其偶然的修改數(shù)據(jù)的順序而造成的鎖也少。

　　而且，數(shù)據(jù)庫(kù)使用鎖超時(shí)來(lái)避免讓用戶(hù)等待時(shí)間過(guò)長(zhǎng)。查詢(xún)超時(shí)的引入也是為了同樣目的。我們可以重新遞交那些超時(shí)的查詢(xún)，但是這只會(huì)造成數(shù)據(jù)庫(kù)的堵塞。如果經(jīng)常發(fā)生超時(shí)，說(shuō)明用戶(hù)使用SQL Server的方式有問(wèn)題。正常情況是很少會(huì)發(fā)生超時(shí)的。

　　在服務(wù)器負(fù)載較高的運(yùn)行環(huán)境下，使用混合鎖的SQL Server鎖機(jī)制，表現(xiàn)不會(huì)很好。 原因是鎖爭(zhēng)用(Lock Contention)。鎖爭(zhēng)用造成死鎖和鎖等待問(wèn)題。在一個(gè)多用戶(hù)系統(tǒng)中，很多用戶(hù)會(huì)同時(shí)在修改數(shù)據(jù)庫(kù)，還有更多的用戶(hù)在同時(shí)訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)，隨時(shí)會(huì)產(chǎn)生鎖，用戶(hù)也爭(zhēng)先恐后地獲取鎖以確保自己的操作的正確性，死鎖頻繁發(fā)生，這種情形下，用戶(hù)的心情可想而知。

　　確實(shí)，如果只有少量用戶(hù)，SQL Server不會(huì)遇到多少麻煩。內(nèi)部測(cè)試和發(fā)布的時(shí)候，由于用戶(hù)較少，也很難發(fā)現(xiàn)那些并發(fā)問(wèn)題。但是當(dāng)激發(fā)幾百個(gè)并發(fā)，進(jìn)行持續(xù)不斷地INSERT,UPDATE，以及一些 DELETE操作時(shí)，如何觀察是否有麻煩出現(xiàn)，那時(shí)候你就會(huì)手忙腳亂地去解鎖。

鎖爭(zhēng)用的解決方法

　　SQL Server開(kāi)始是用行級(jí)鎖的，但是經(jīng)常會(huì)擴(kuò)大為頁(yè)面鎖和表鎖，最終造成死鎖。

　　即使用戶(hù)沒(méi)有修改數(shù)據(jù)，SQL Server在SELECT的時(shí)候也會(huì)遇到鎖。幸運(yùn)的是，我們可以通過(guò)SQL Server 的兩個(gè)關(guān)鍵字來(lái)手工處理：NOLOCK和ROWLOCK。

它們的使用方法如下：

SELECT COUNT(UserID)
　FROM Users WITH (NOLOCK)
　WHERE Username LIKE 'football'

和

UPDATE Users WITH (ROWLOCK)
SET Username = 'admin' WHERE Username = 'football'

NOLOCK的使用

　　NOLOCK可以忽略鎖，直接從數(shù)據(jù)庫(kù)讀取數(shù)據(jù)。這意味著可以避開(kāi)鎖，從而提高性能和擴(kuò)展性。但同時(shí)也意味著代碼出錯(cuò)的可能性存在。你可能會(huì)讀取到運(yùn)行事務(wù)正在處理的無(wú)須驗(yàn)證的未遞交數(shù)據(jù)。 這種風(fēng)險(xiǎn)可以量化。

ROWLOCK的使用

　　ROWLOCK告訴SQL Server只使用行級(jí)鎖。ROWLOCK語(yǔ)法可以使用在SELECT,UPDATE和DELETE語(yǔ)句中，不過(guò) 我習(xí)慣僅僅在UPDATE和DELETE語(yǔ)句中使用。如果在UPDATE語(yǔ)句中有指定的主鍵，那么就總是會(huì)引發(fā)行級(jí)鎖的。但是當(dāng)SQL Server對(duì)幾個(gè)這種UPDATE進(jìn)行批處理時(shí)，某些數(shù)據(jù)正好在同一個(gè)頁(yè)面(page)，這種情況在當(dāng)前情況下 是很有可能發(fā)生的，這就象在一個(gè)目錄中，創(chuàng)建文件需要較長(zhǎng)的時(shí)間，而同時(shí)你又在更新這些文件。當(dāng)頁(yè)面鎖引發(fā)后，事情就開(kāi)始變得糟糕了。而如果在UPDATE或者DELETE時(shí)，沒(méi)有指定主鍵，數(shù)據(jù)庫(kù)當(dāng)然認(rèn)為很多數(shù)據(jù)會(huì)收到影響，那樣 就會(huì)直接引發(fā)頁(yè)面鎖，事情同樣變得糟糕。

下面寫(xiě)一個(gè)例子，來(lái)說(shuō)明一下NOLOCK的作用，這里使用一個(gè)有一萬(wàn)多條的數(shù)據(jù)庫(kù)來(lái)測(cè)試，先不用NOLOCK來(lái)看一下：

declare @start DATETIME;
declare @end DATETIME;
SET @start = getdate();
select * from Captions_t;
SET @end = getdate();
select datediff(ms,@start,@end);

這里為了是效果更加明顯，使用了Select * ，來(lái)看一下執(zhí)行結(jié)果，如下圖：

這里顯示的使用時(shí)間是34720ms,下面使用NOLOCK來(lái)看一下：

declare @start DATETIME;
declare @end DATETIME;
SET @start = getdate();
select * from Captions_t18 with (NOLOCK);
SET @end = getdate();
select datediff(ms,@start,@end);

運(yùn)行結(jié)果如下圖：

這次使用的時(shí)間是2563ms，差距體現(xiàn)出來(lái)了吧。個(gè)人感覺(jué)時(shí)間不應(yīng)該差這么多，總之性能是提高了不少。

nolock和with(nolock)的幾個(gè)小區(qū)別：

1.SQL Server 2005中的同義詞，只支持with(nolock);

2.with(nolock)的寫(xiě)法非常容易再指定索引。

3.跨服務(wù)器查詢(xún)語(yǔ)句時(shí)，不能用with (nolock) 只能用nolock，同一個(gè)服務(wù)器查詢(xún)時(shí)則with (nolock)和nolock都可以用。比如：select * from [IP].a.dbo.table1 with (nolock) 這樣會(huì)提示錯(cuò)誤，select * from a.dbo.table1 with (nolock) 這樣就可以成功地查詢(xún)。

以上內(nèi)容就是本文介紹sql server 性能優(yōu)化之nolock的全部?jī)?nèi)容，希望對(duì)大家有所幫助。

最新評(píng)論