本篇文章主要介紹SqlServer使用時(shí)的注意事項(xiàng)。

雖然現(xiàn)在開發(fā)中普通使用各種Orm框架，總結(jié)操作sql的機(jī)會(huì)越來越少，但是想成為一個(gè)高級(jí)程序員，數(shù)據(jù)庫的使用是必須要會(huì)的。而數(shù)據(jù)庫的使用純熟程度，也側(cè)面反映了一個(gè)開發(fā)的水平。

下面介紹SqlServer在使用和設(shè)計(jì)的過程中需要注意的事項(xiàng)。

SqlServer注意事項(xiàng)

Sql事務(wù)啟動(dòng)語句

開始事務(wù)：BEGIN TRANSACTION

提交事務(wù)：COMMIT TRANSACTION

回滾事務(wù)：ROLLBACK TRANSACTION

相關(guān)注意事項(xiàng)

保持事務(wù)簡短，事務(wù)越短，越不可能造成阻塞。

在事務(wù)中盡量避免使用循環(huán)while和游標(biāo)，以及避免采用訪問大量行的語句。

事務(wù)中不要要求用戶輸入。

在啟動(dòng)事務(wù)前完成所有的計(jì)算和查詢等操作。

避免同一事務(wù)中交錯(cuò)讀取和更新。可以使用表變量預(yù)先存儲(chǔ)數(shù)據(jù)。即存儲(chǔ)過程中查詢與更新使用兩個(gè)事務(wù)實(shí)現(xiàn)。

超時(shí)會(huì)讓事務(wù)不執(zhí)行回滾，超時(shí)后如果客戶端關(guān)閉連接sqlserver自動(dòng)回滾事務(wù)。如果不關(guān)閉，將造成數(shù)據(jù)丟失，而其他事務(wù)將在這個(gè)未關(guān)閉的連接上執(zhí)行，造成資源鎖定，甚至服務(wù)器停止響應(yīng)。

避免超時(shí)后還可打開事務(wù) SET XACT_ABORT ON統(tǒng)計(jì)信息可以優(yōu)化查詢速度,統(tǒng)計(jì)信息準(zhǔn)確可以避免查詢掃描，直接進(jìn)行索引查找。

sp_updatestats可以更新統(tǒng)計(jì)信息到最新。

低內(nèi)存會(huì)導(dǎo)致未被客戶端連接的查詢計(jì)劃被清除。

修改表結(jié)構(gòu)，修改索引后，查詢計(jì)劃會(huì)被清除，可以再修改后運(yùn)行幾遍查詢。

DDL DML交錯(cuò)和查詢內(nèi)部SET選項(xiàng)將重新編譯查詢計(jì)劃。

order by 影響查詢速度。

where中使用函數(shù)則會(huì)調(diào)用篩選器進(jìn)行掃描，掃描表要盡量避免。

updlock和holdlock同時(shí)使用可以在早期鎖定后面需要更新的資源，維護(hù)資源完整性，避免沖突。

如果不需要使用臨時(shí)表的統(tǒng)計(jì)信息來進(jìn)行大數(shù)據(jù)查詢，表變量是更好的選擇。

事務(wù)使用注意事項(xiàng)

設(shè)置事務(wù)隔離級(jí)別(未提交讀,讀臟),相當(dāng)于(NOLOCK) 的語句：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

隔離級(jí)別描述如下：

1.READ UNCOMMITTED

READ UNCOMMITTED:未提交讀，讀臟數(shù)據(jù)。

默認(rèn)的讀操作:需要請求共享鎖,允許其他事物讀鎖定的數(shù)據(jù)但不允許修改。

READ UNCOMMITTED:讀操作不申請鎖,允許讀取未提交的修改,也就是允許讀臟數(shù)據(jù),讀操作不會(huì)影響寫操作請求排他鎖。

2.READ COMMITTED

READ COMMITTED（已提交讀）是SQL SERVER默認(rèn)的隔離級(jí)別，可以避免讀取未提交的數(shù)據(jù)，隔離級(jí)別比READ UNCOMMITTED未提交讀的級(jí)別更高；

該隔離級(jí)別讀操作之前首先申請并獲得共享鎖，允許其他讀操作讀取該鎖定的數(shù)據(jù)，但是寫操作必須等待鎖釋放，一般讀操作讀取完就會(huì)立刻釋放共享鎖。

3.REPEATABLE READ

REPEATABLE READ(可重復(fù)讀)：保證在一個(gè)事務(wù)中的兩個(gè)讀操作之間，其他的事務(wù)不能修改當(dāng)前事務(wù)讀取的數(shù)據(jù)，該級(jí)別事務(wù)獲取數(shù)據(jù)前必須先獲得共享鎖同時(shí)獲得的共享鎖不立即釋放一直保持共享鎖至事務(wù)完成，所以此隔離級(jí)別查詢完并提交事務(wù)很重要。

4.SERIALIZABLE

SERIALIZABLE(可序列化)，對于前面的REPEATABLE READ能保證事務(wù)可重復(fù)讀，但是事務(wù)只鎖定查詢第一次運(yùn)行時(shí)獲取的數(shù)據(jù)資源（數(shù)據(jù)行），而不能鎖定查詢結(jié)果之外的行，就是原本不存在于數(shù)據(jù)表中的數(shù)據(jù)。因此在一個(gè)事務(wù)中當(dāng)?shù)谝粋€(gè)查詢和第二個(gè)查詢過程之間，有其他事務(wù)執(zhí)行插入操作且插入數(shù)據(jù)滿足第一次查詢讀取過濾的條件時(shí)，那么在第二次查詢的結(jié)果中就會(huì)存在這些新插入的數(shù)據(jù)，使兩次查詢結(jié)果不一致，這種讀操作稱之為幻讀。

為了避免幻讀需要將隔離級(jí)別設(shè)置為SERIALIZABLE

5.SNAPSHOT

SNAPSHOT快照：SNAPSHOT和READ COMMITTED SNAPSHOT兩種隔離（可以把事務(wù)已經(jīng)提交的行的上一版本保存在TEMPDB數(shù)據(jù)庫中）

SNAPSHOT隔離級(jí)別在邏輯上與SERIALIZABLE類似

READ COMMITTED SNAPSHOT隔離級(jí)別在邏輯上與 READ COMMITTED類似

不過在快照隔離級(jí)別下讀操作不需要申請獲得共享鎖，所以即便是數(shù)據(jù)已經(jīng)存在排他鎖也不影響讀操作。而且仍然可以得到和SERIALIZABLE與READ COMMITTED隔離級(jí)別類似的一致性；如果目前版本與預(yù)期的版本不一致，讀操作可以從TEMPDB中獲取預(yù)期的版本。

如果啟用任何一種基于快照的隔離級(jí)別，DELETE和UPDATE語句在做出修改前都會(huì)把行的當(dāng)前版本復(fù)制到TEMPDB中，而INSERT語句不需要在TEMPDB中進(jìn)行版本控制，因?yàn)榇藭r(shí)還沒有行的舊數(shù)據(jù)

無論啟用哪種基于快照的隔離級(jí)別都會(huì)對更新和刪除操作產(chǎn)生性能的負(fù)面影響，但是有利于提高讀操作的性能因?yàn)樽x操作不需要獲取共享鎖；

5.1SNAPSHOT

SNAPSHOT 在SNAPSHOT隔離級(jí)別下,當(dāng)讀取數(shù)據(jù)時(shí)可以保證操作讀取的行是事務(wù)開始時(shí)可用的最后提交版本

同時(shí)SNAPSHOT隔離級(jí)別也滿足前面的已提交讀,可重復(fù)讀,不幻讀;該隔離級(jí)別實(shí)用的不是共享鎖,而是行版本控制

使用SNAPSHOT隔離級(jí)別首先需要在數(shù)據(jù)庫級(jí)別上設(shè)置相關(guān)選項(xiàng)

5.2READ COMMITTED SNAPSHOT

READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔離級(jí)別是讀操作之前的最后已提交版本,而不是事務(wù)前的已提交版本，有點(diǎn)類似前面的READ COMMITTED能保證已提交讀,但是不能保證可重復(fù)讀,不能避免幻讀,但是又比 READ COMMITTED隔離級(jí)別多出了不需要獲取共享鎖就可以讀取數(shù)據(jù)，說了理論，下面直接上代碼進(jìn)行分析：

SqlServer【鎖】注意事項(xiàng)

一、頁鎖實(shí)例

T1: select * from table (paglock)

T2: update table set column1='hello' where id>10

說明

T1執(zhí)行時(shí)，會(huì)先對第一頁加鎖，讀完第一頁后，釋放鎖，再對第二頁加鎖，依此類推。假設(shè)前10行記錄恰好是一頁(當(dāng)然，一般不可能一頁只有10行記錄），那么T1執(zhí)行到第一頁查詢時(shí)，并不會(huì)阻塞T2的更新。

----------------------------------------------------------------------------------------------------

二、行鎖實(shí)例

T1: select * from table (rowlock)

T2: update table set column1='hello' where id=10

說明

T1執(zhí)行時(shí)，對每行加共享鎖，讀取，然后釋放，再對下一行加鎖；T2執(zhí)行時(shí)，會(huì)對id=10的那一行試圖加鎖，只要該行沒有被T1加上行鎖，T2就可以順利執(zhí)行update操作。

----------------------------------------------------------------------------------------------------

三、整表鎖實(shí)例

T1: select * from table (tablock)

T2: update table set column1='hello' where id = 10

說明

T1執(zhí)行，對整個(gè)表加共享鎖。 T1必須完全查詢完，T2才可以允許加鎖，并開始更新。

----------------------------------------------------------------------------------------------------

以上是sql鎖的一些常用技巧，希望對大家有幫助。