SQLserver鎖和事務(wù)隔離級(jí)別的比較與使用

1、SQLserver鎖和事務(wù)隔離級(jí)別的比較與使用對(duì)象 鎖：每條SQL語(yǔ)句 隔離：事務(wù)鎖并發(fā)問(wèn)題丟失更新未確認(rèn)的讀?。ㄅK讀）不一致的分析（非重復(fù)讀）：多次讀取相同的數(shù)據(jù)（行）不一致（其他用戶更改update） 幻像讀：多次讀取有不存在和新增的數(shù)據(jù)（其他用戶插入insert或刪除delete）隔離級(jí)別是是否否 是 是 是 否 如果其他事務(wù)更新，不管是否提交，立即執(zhí)行 讀取提交過(guò)的數(shù)據(jù)。如果其他事務(wù)更新沒(méi)提交，則等待 查詢期間，不允許其他事務(wù)update 查詢期間，不允許其他事務(wù)insert或delete 未提交讀(read uncommitted) 是 提交讀(read committed默認(rèn)) 否

2、 可重復(fù)讀(repeatable read) 可串行讀(serializable) 否 否提交讀假設(shè)存在表A，如下所示打開(kāi)查詢分析器并打開(kāi)兩個(gè)連接，分別輸入如下兩個(gè)事務(wù)：-事務(wù)SET TRANSACTION ISOLATION LEVEL READ Committedbegin tranupdate A set A2 = 20 where A1 = 11waitfor delay '00:00:10'rollback tran-事務(wù)SET TRANSACTION ISOLATION LEVEL READ Committedselect * from A where A1 = 1

3、1如果先運(yùn)行事務(wù)，然后緊接著運(yùn)行事務(wù)，則事務(wù)要等待10秒鐘（一個(gè)連接在修改數(shù)據(jù)塊時(shí)別的連接也不能查詢這個(gè)數(shù)據(jù)塊，直到解鎖。反之亦然：讀的時(shí)候不能寫和修改）。如果把事務(wù)改為如下SET TRANSACTION ISOLATION LEVEL READ UNCommittedselect * from A where A1 = 11那么事務(wù)不需等待，立即執(zhí)行（可以看出READ UNCommitted事務(wù)select不對(duì)數(shù)據(jù)發(fā)出共享鎖）鎖：(這里主要講解 共享鎖 和 排他鎖 兩種經(jīng)常用到的鎖)共享鎖主要是為了共享讀（select），如果存在事務(wù)（一個(gè)或多個(gè)）擁有對(duì)表中數(shù)據(jù)（關(guān)于鎖數(shù)據(jù)的多少，視鎖的粒度

4、而定）的共享鎖，不允許對(duì)鎖定的數(shù)據(jù)進(jìn)行更新(update)（從鎖的角度講，即不允許事務(wù)獲取排他鎖，要等到所有的共享鎖都釋放掉）。反之，如果事務(wù)對(duì)數(shù)據(jù)已經(jīng)具有排他鎖（只能有一個(gè)），其他的事務(wù)就不能對(duì)鎖定的數(shù)據(jù)獲取共享鎖和排他鎖（即排他鎖與共享鎖不能兼容，更多信息請(qǐng)查看鎖兼容性），在此特別強(qiáng)調(diào)一下 鎖定的數(shù)據(jù) ，因?yàn)橛械馁Y料上講解到“一個(gè)連接寫的時(shí)候，另一個(gè)連接可以寫”，實(shí)際上寫的這種情況是各個(gè)連接的讀寫的數(shù)據(jù)不是相同的行，也就是說(shuō)各個(gè)連接鎖定的數(shù)據(jù)不同。根據(jù)以上分析，我們總結(jié)為六個(gè)字為“共享讀，排他寫”。了解了鎖的情況之后，又涉及到一個(gè)問(wèn)題。事務(wù)究竟要保持鎖多久呢?一般來(lái)說(shuō)，共享鎖的鎖定時(shí)間與事

5、務(wù)的隔離級(jí)別有關(guān)，如果隔離級(jí)別為Read Committed的默認(rèn)級(jí)別，只在讀取(select)的期間保持鎖定，即在查詢出數(shù)據(jù)以后就釋放了鎖；如果隔離級(jí)別為更高的Repeatable read或Serializable，直到事務(wù)結(jié)束才釋放鎖。另說(shuō)明，如果select語(yǔ)句中指定了HoldLock提示，則也要等到事務(wù)結(jié)束才釋放鎖。排他鎖直到事務(wù)結(jié)束才釋放。做出了以上分析，現(xiàn)在我們可能會(huì)存在這樣的疑問(wèn)，到底在執(zhí)行SQL語(yǔ)句的時(shí)候發(fā)出什么樣的鎖呢，這就由事務(wù)的隔離級(jí)別決定了。一般情況，讀語(yǔ)句(select)發(fā)出共享鎖，寫語(yǔ)句(update,insert,delete)發(fā)出排他鎖。但是，如果這樣不能滿足

6、我們的要求怎么辦呢，有沒(méi)有更多選擇呢，別急，SQLserver為我們提供了鎖定提示的概念。鎖定提示對(duì)SQL語(yǔ)句進(jìn)行特別指定，這個(gè)指定將覆蓋事務(wù)的隔離級(jí)別。下面對(duì)各個(gè)鎖定提示分別予以介紹（更多資料請(qǐng)查看SQLserver的聯(lián)機(jī)幫助），筆者做出了以下分類。類型1 READUNCOMMITTED：不發(fā)出鎖 READCOMMITTED：發(fā)出共享鎖，保持到讀取結(jié)束 REPEATABLEREAD：發(fā)出共享鎖，保持到事務(wù)結(jié)束 SERIALIZABLE：發(fā)出共享鎖，保持到事務(wù)結(jié)束類型2 NOLOCK：不發(fā)出鎖。等同于READUNCOMMITTED HOLDLOCK：發(fā)出共享鎖，保持到事務(wù)結(jié)束。等同于SERIA

7、LIZABLE XLOCK：發(fā)出排他鎖，保持到事務(wù)結(jié)束。 UPDLOCK：發(fā)出更新鎖，保持到事務(wù)事務(wù)結(jié)束。（更新鎖：不阻塞別的事物，允許別的事物讀數(shù)據(jù)（即更新鎖可與共享鎖兼容），但他確保自上次讀取數(shù)據(jù)后數(shù)據(jù)沒(méi)有被更新） READPAST：發(fā)出共享鎖，但跳過(guò)鎖定行，它不會(huì)被阻塞。適用條件：提交讀的隔離級(jí)別，行級(jí)鎖，select語(yǔ)句中。類型3 ROWLOCK：行級(jí)鎖 PAGLOCK：頁(yè)級(jí)鎖 TABLOCK：表鎖 TABLOCKX：表排他鎖講解完鎖后，下面結(jié)合一個(gè)具體實(shí)例，具體看一下鎖的使用。在很多系統(tǒng)中，經(jīng)常會(huì)遇到這種情況，要保持一個(gè)編號(hào)的唯一，如會(huì)計(jì)軟件中的憑證的編號(hào)。一種編號(hào)的處理是這樣的，把

8、表中的最大編號(hào)保存到表中，然后在這個(gè)編號(hào)上累加，形成新的編號(hào)。這個(gè)過(guò)程對(duì)并發(fā)處理要求非常高，下面我們就來(lái)模擬這個(gè)過(guò)程，看如何保持編號(hào)的唯一性。新建一張表code來(lái)保存憑證的最大編號(hào)。字段如下：編號(hào):bh(numeric(18,0),憑證表名pinzheng(varchar(50)假設(shè)表中有這樣的一條記錄：新建一個(gè)存儲(chǔ)過(guò)程來(lái)生成新的憑證編號(hào)，如下：CREATE PROCEDURE up_getbh ASBegin TranDeclare numnewbh numeric(18,0)select numnewbh = bh FROM code WITH (UPDLOCK,ROWLOCK) wher

9、e pinzheng = '會(huì)計(jì)憑證'set numnewbh = numnewbh + 1update code set bh = numnewbh where pinzheng = '會(huì)計(jì)憑證'print numnewbhCommit tranGO然后，打開(kāi)查詢分析器，并多開(kāi)幾個(gè)連接（筆者開(kāi)了8個(gè)連接，模擬有8個(gè)人同時(shí)并發(fā)，讀者可以開(kāi)更多的連接進(jìn)行試驗(yàn)），把類似以下這樣的語(yǔ)句復(fù)制到每個(gè)連接窗口中，declare i numeric(18,0)set i = 1while i = 1Beginif getdate() > '2004-07-22 14:23' -設(shè)定一個(gè)時(shí)間，到此時(shí)間同時(shí)執(zhí)行upgetbh存儲(chǔ)過(guò)程 set i = 0endexec up_getbh然后，接連運(yùn)行各個(gè)連接，到2004-7-22 14：23 這一刻，各個(gè)連接同時(shí)運(yùn)行up_getbh。從運(yùn)