[[249632]]

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

想成為一個(gè)高級(jí)程序員，數(shù)據(jù)庫(kù)的使用是必須要會(huì)的。而數(shù)據(jù)庫(kù)的使用純熟程度，也側(cè)面反映了一個(gè)開(kāi)發(fā)的水平。

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

SQL Server注意事項(xiàng)

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

開(kāi)始事務(wù)：BEGIN TRANSACTION

提交事務(wù)：COMMIT TRANSACTION

回滾事務(wù)：ROLLBACK TRANSACTION

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

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

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

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

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

避免同一事務(wù)中交錯(cuò)讀取和更新?？梢允褂帽碜兞款A(yù)先存儲(chǔ)數(shù)據(jù)。即存儲(chǔ)過(guò)程中查詢(xún)與更新使用兩個(gè)事務(wù)實(shí)現(xiàn)。

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

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

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

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

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

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

order by 影響查詢(xún)速度。

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

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

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

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

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

1、READ UNCOMMITTED

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

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

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

2、READ COMMITTED

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

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

3、REPEATABLE READ

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

4、SERIALIZABLE

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

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

5、SNAPSHOT

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

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

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

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

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

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

5.1SNAPSHOT

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

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

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

5.2 READ COMMITTED SNAPSHOT

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

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

一、頁(yè)鎖實(shí)例

T1: select * from table (paglock)

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

說(shuō)明

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

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

二、行鎖實(shí)例

T1：select * from table (rowlock)

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

說(shuō)明

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

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

三、整表鎖實(shí)例

T1：select * from table (tablock)

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

說(shuō)明

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