SQL7中LOCK的理解(2)

　　下面介紹的是鎖的並發問題和解決方法
　　在實際情況中通常會因為lock發生四中情況
　　下面用個定座系統舉例所有的語句都工作在默認隔離級別下
　　 丟失的更新
　　user run
　　begin tran
　　select 空座位 得到
　　update 定座位
　　commit
　　
　　如果user在user update之前運行同樣的程序都會得到空座位是的信息當然user也可以定座位結果就是user的定座是最終有效的user的定座被覆蓋為了避免這種情況看看用隔離級別能不能解決這四種隔離級別最多在表上施加S由於S是兼容的因此user照樣能得到空座位是的信息丟失的更新也照樣會發生SQL提供了表級鎖定選項TABLOCKX能避免這種情況的發生但喪失了並發
　　
　　
　　 髒讀
　　user run
　　begin tran
　　update row A
　　commit
　　
　　如果user能在user update的同時讀出表中的數據(包含 row A) 那麼就產生了髒讀因為user可能commit也可能rollbackupdate並沒有被確認默認情況下是不會產生髒讀的因為update將產生行的X表的IX而表IX和S是不兼容的那麼在什麼情況下我們需要髒讀呢如果我們不需要確切的數據我們需要的是一個大概的趨勢個別數據的不確定沒有關系想想一個股票系統同一時刻的改寫會很多也就是表上會有許多IX鎖如果我們需要查詢表獲得大致情況那麼只有等改寫結束為了提高並發此時我們需要髒讀解決方法就是在user的會話環境中設置事務隔離級別為read uncommitted
　　
　　
　　 不可重復讀
　　user run
　　begin tran
　　select tab where
　　
　　
　　select tab where
　　commit
　　
　　當user在交易中執行兩次select所得到的結果不一樣這便是不可重復讀發生的原因是user在user第二次select之前對tab做了修改select語句雖然對表有讀鎖但在默認情況下讀鎖在select語句執行完就被釋放而不是保持到交易結束為了避免不可重復讀的發生只要select產生的鎖保持到交易結束就可以了將user的會話環境中設置事務隔離級別為repeatable read
　　
　　
　　 幻影
　　user run
　　begin tran
　　select tab where
　　
　　
　　select tab where
　　commit
　　雖然將user的會話環境中設置事務隔離級別為repeatable read但當user在交易中執行兩次select所得到的結果還會不一樣這便是幻影因為user僅僅將select出來的行加了S鎖但user可以insert滿足where條件的新行使得兩次select的結果不一樣解決方法是將user的會話環境中設置事務隔離級別為serializable或者在表級鎖定選項中選holdlock
　　
　　
　　鎖定超時
　　前面說過當某個語句因為鎖而不能立即執行時會等待直到鎖被釋放但如果持有鎖的語句執行時間過長(未優化)那麼就會等待過長影響響應因此我們通過lock_timeout來將等待過長的語句rollback需要注意的是當數據量增加時可能發生不希望的lock_timeout原因是SQL需要更多的時間來處理數據為此我們也需要適當的增加lock_timeout
　　索引在鎖中的應用
　　大家都知道索引可以提高查詢速度其實索引還有一個很重要的功能就是提高並發
　　例當user run update tab set col=xxx時user對tab run select * from tab將會等待直到user執行完但如果user執行select * from tab where col<>xxx也就是user要查詢的行並不是user改寫的行結果如何呢？同樣user也要等到user執行完這樣就沒有什麼並發可言如果我們在col上建立index就可以讓user無須等待立即執行更常用的是父表和子表的情況通常為了保證引用完整性SQL在修改數據時都會將依賴表加鎖但如果有index情況就好的多
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22171.html