使用Sqlserver更新鎖防止資料髒讀

silent發表於2016-01-29
SQLServer

　　有時候我們需要控制某條記錄在程式讀取後就不再進行更新,直到事務執行完釋放後才可以。這時候我們就可以將所有要操作當前記錄的查詢加上更新鎖,以防止查詢後被其它事務修改。這種操作只鎖定表中某行而不會鎖定整個表，體驗更好。

　　測試sql程式碼如下：

　　在一個查詢中執行如下語句

begin tran
 SELECT InvestState FROM InvestOrdersABC WITH (UPDLOCK) where id=10005
 waitfor delay '00:00:10' 
 update InvestOrdersABC set InvestState='2' where id=10005
commit tran

　　1、在另外的一個查詢中執行以下語句

SELECT InvestState FROM InvestOrdersABC  where id=10005

　　發現在第一個事務執行完以前查到的數值還是原來的數值0，直到更新完成後才會變成2,如果加上鎖，程式碼如下：

SELECT InvestState FROM InvestOrdersABC WITH (UPDLOCK) where id=10005

　　發現sql語句必須等到第一個連線裡的事務完成才執行完成，這是因為這個sql的連線的更新鎖認為第一個事務裡的更新鎖可能會對資料進行修改，因此必須等事務執行完成才執行。此時更新鎖變為排他鎖。

　　2、如果執行更新操作：

begin tran
 update InvestOrders set InvestState='3' where id=10005
commit tran

 　　發現無法更改，只能等到第一個查詢完成後才會進行修改。其實和加鎖不加鎖已經沒什麼關係，為什麼呢？因為SQL Server在執行INSERT、 UPDATE 或DELETE 命令時，會自動使用獨佔鎖。

　　3、上文的事務未加隔離級別，事務的預設隔離級別為READ  committed，不加鎖因此在第1點裡還可以進行查詢。當資料庫事務的隔離級別為REPEATABLE READ，SERIALIZABLE時，如果查詢需要加共享鎖：

SELECT InvestState FROM InvestOrdersABC WITH (HoldLOCK) where id=10005


 

相關文章