SQL Server註意事項總結，高階程式員必背！

作者：kiba518

連結：https://www.cnblogs.com/kiba/p/9351119.html

本篇文章主要介紹SqlServer使用時的註意事項。

 

想成為一個高階程式員，資料庫的使用是必須要會的。而資料庫的使用純熟程度，也側面反映了一個開發的水平。

 

下麵介紹SqlServer在使用和設計的過程中需要註意的事項。

SqlServer註意事項

Sql事務啟動陳述句

 

開始事務：BEGIN TRANSACTION

提交事務：COMMIT TRANSACTION

回滾事務：ROLLBACK TRANSACTION

 

相關註意事項

 

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

 

在事務中儘量避免使用迴圈while和遊標，以及避免採用訪問大量行的陳述句。

事務中不要要求使用者輸入。

 

在啟動事務前完成所有的計算和查詢等操作。

 

避免同一事務中交錯讀取和更新。可以使用表變數預先儲存資料。即儲存過程中查詢與更新使用兩個事務實現。

 

超時會讓事務不執行回滾，超時後如果客戶端關閉連線sqlserver自動回滾事務。如果不關閉，將造成資料丟失，而其他事務將在這個未關閉的連線上執行，造成資源鎖定，甚至伺服器停止響應。

 

避免超時後還可開啟事務 SET XACT_ABORT ON統計資訊可以最佳化查詢速度,統計資訊準確可以避免查詢掃描，直接進行索引查詢。

 

sp_updatestats可以更新統計資訊到最新。

 

低記憶體會導致未被客戶端連線的查詢計劃被清除。

 

修改表結構，修改索引後，查詢計劃會被清除，可以再修改後執行幾遍查詢。

 

DDL DML交錯和查詢內部SET選項將重新編譯查詢計劃。

 

order by 影響查詢速度。

 

where中使用函式則會呼叫篩選器進行掃描，掃描表要儘量避免。

 

updlock和holdlock同時使用可以在早期鎖定後面需要更新的資源，維護資源完整性，避免衝突。

 

如果不需要使用臨時表的統計資訊來進行大資料查詢，表變數是更好的選擇。

事務使用註意事項

設定事務隔離級別(未提交讀,讀臟),相當於(NOLOCK) 的陳述句：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

 

隔離級別描述如下：

 

1、READ UNCOMMITTED

 

READ UNCOMMITTED:未提交讀，讀臟資料。

 

預設的讀操作:需要請求共享鎖,允許其他事物讀鎖定的資料但不允許修改。

 

READ UNCOMMITTED:讀操作不申請鎖,允許讀取未提交的修改,也就是允許讀臟資料,讀操作不會影響寫操作請求排他鎖。

 

2、READ COMMITTED

 

READ COMMITTED（已提交讀）是SQL SERVER預設的隔離級別，可以避免讀取未提交的資料，隔離級別比READ UNCOMMITTED未提交讀的級別更高；

 

該隔離級別讀操作之前首先申請並獲得共享鎖，允許其他讀操作讀取該鎖定的資料，但是寫操作必須等待鎖釋放，一般讀操作讀取完就會立刻釋放共享鎖。

 

3、REPEATABLE READ

 

REPEATABLE READ(可重覆讀)：保證在一個事務中的兩個讀操作之間，其他的事務不能修改當前事務讀取的資料，該級別事務獲取資料前必須先獲得共享鎖同時獲得的共享鎖不立即釋放一直保持共享鎖至事務完成，所以此隔離級別查詢完並提交事務很重要。

 

4、SERIALIZABLE

 

SERIALIZABLE(可序列化)，對於前面的REPEATABLE READ能保證事務可重覆讀，但是事務只鎖定查詢第一次執行時獲取的資料資源（資料行），而不能鎖定查詢結果之外的行，就是原本不存在於資料表中的資料。因此在一個事務中當第一個查詢和第二個查詢過程之間，有其他事務執行插入操作且插入資料滿足第一次查詢讀取過濾的條件時，那麼在第二次查詢的結果中就會存在這些新插入的資料，使兩次查詢結果不一致，這種讀操作稱之為幻讀。

為了避免幻讀需要將隔離級別設定為SERIALIZABLE

 

5、SNAPSHOT

 

SNAPSHOT快照：SNAPSHOT和READ COMMITTED SNAPSHOT兩種隔離（可以把事務已經提交的行的上一版本儲存在TEMPDB資料庫中）

SNAPSHOT隔離級別在邏輯上與SERIALIZABLE類似

READ COMMITTED SNAPSHOT隔離級別在邏輯上與 READ COMMITTED類似
不過在快照隔離級別下讀操作不需要申請獲得共享鎖，所以即便是資料已經存在排他鎖也不影響讀操作。而且仍然可以得到和SERIALIZABLE與READ COMMITTED隔離級別類似的一致性；如果目前版本與預期的版本不一致，讀操作可以從TEMPDB中獲取預期的版本。

 

如果啟用任何一種基於快照的隔離級別，DELETE和UPDATE陳述句在做出修改前都會把行的當前版本複製到TEMPDB中，而INSERT陳述句不需要在TEMPDB中進行版本控制，因為此時還沒有行的舊資料

 

無論啟用哪種基於快照的隔離級別都會對更新和刪除操作產生效能的負面影響，但是有利於提高讀操作的效能因為讀操作不需要獲取共享鎖；

 

5.1SNAPSHOT

 

SNAPSHOT 在SNAPSHOT隔離級別下,當讀取資料時可以保證操作讀取的行是事務開始時可用的最後提交版本

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

使用SNAPSHOT隔離級別首先需要在資料庫級別上設定相關選項

 

5.2 READ COMMITTED SNAPSHOT

 

READ COMMITTED SNAPSHOT也是基於行版本控制,但是READ COMMITTED SNAPSHOT的隔離級別是讀操作之前的最後已提交版本,而不是事務前的已提交版本，有點類似前面的READ COMMITTED能保證已提交讀,但是不能保證可重覆讀,不能避免幻讀,但是又比 READ COMMITTED隔離級別多出了不需要獲取共享鎖就可以讀取資料

SqlServer【鎖】註意事項

一、頁鎖實體

 

T1: select * from table (paglock)
T2: update table set column1=’hello’ where id>10

 

說明

T1執行時，會先對第一頁加鎖，讀完第一頁後，釋放鎖，再對第二頁加鎖，依此類推。假設前10行記錄恰好是一頁(當然，一般不可能一頁只有10行記錄），那麼T1執行到第一頁查詢時，並不會阻塞T2的更新。

———————-

 

二、行鎖實體

 

T1：select * from table (rowlock)
T2：update table set column1=’hello’ where id=10

 

說明

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

 

——————

 

三、整表鎖實體

 

T1：select * from table (tablock)
T2：update table set column1=’hello’ where id = 10

 

說明

T1執行，對整個表加共享鎖。 T1必須完全查詢完，T2才可以允許加鎖，並開始更新。