對象
① 鎖：每條SQL語句
② 隔離：事務(wù)
鎖
①
并發(fā)問題
丟失更新
未確認(rèn)的讀取（臟讀）
不一致的分析（非重復(fù)讀）：多次讀取相同的數(shù)據(jù)（行）不一致（其他用戶更改update）
幻像讀：多次讀取有不存在和新增的數(shù)據(jù)（其他用戶插入insert或刪除delete）
隔離級別
|
隔離級別
|
臟讀
|
不可重復(fù)讀取
|
幻像
|
說明
|
|
未提交讀(read uncommitted)
|
是
|
是
|
是
|
如果其他事務(wù)更新，不管是否提交，立即執(zhí)行
|
|
提交讀(read committed默認(rèn))
|
否
|
是
|
是
|
讀取提交過的數(shù)據(jù)。如果其他事務(wù)更新沒提交，則等待
|
|
可重復(fù)讀(repeatable read)
|
否
|
否
|
是
|
查詢期間，不允許其他事務(wù)update
|
|
可串行讀(serializable)
|
否
|
否
|
否
|
查詢期間，不允許其他事務(wù)insert或delete
|
提交讀
假設(shè)存在表A，如下所示
|
A1
|
A2
|
A3
|
|
11
|
21
|
31
|
|
12
|
22
|
32
|
打開查詢分析器并打開兩個(gè)連接，分別輸入如下兩個(gè)事務(wù)：
--事務(wù)Ⅰ
SET TRANSACTION ISOLATION LEVEL READ Committed
begin tran
update A set A2 = 20 where A1 = 11
waitfor delay '00:00:10'
rollback tran
--事務(wù)Ⅱ
SET TRANSACTION ISOLATION LEVEL READ Committed
select * from A where A1 = 11
如果先運(yùn)行事務(wù)Ⅰ，然后緊接著運(yùn)行事務(wù)Ⅱ，則事務(wù)Ⅱ要等待10秒鐘（一個(gè)連接在修改數(shù)據(jù)塊時(shí)別的連接也不能查詢這個(gè)數(shù)據(jù)塊，直到解鎖。反之亦然：讀的時(shí)候不能寫和修改）。
如果把事務(wù)Ⅱ改為如下
SET TRANSACTION ISOLATION LEVEL READ UNCommitted
select * from A where A1 = 11
那么事務(wù)Ⅱ不需等待，立即執(zhí)行（可以看出READ UNCommitted事務(wù)select不對數(shù)據(jù)發(fā)出共享鎖）
鎖：(這里主要講解 共享鎖 和 排他鎖 兩種經(jīng)常用到的鎖)
共享鎖主要是為了共享讀（select），如果存在事務(wù)（一個(gè)或多個(gè)）擁有對表中數(shù)據(jù)（關(guān)于鎖數(shù)據(jù)的多少，視鎖的粒度而定）的共享鎖，不允許對鎖定的數(shù)據(jù)進(jìn)行更新(update)（從鎖的角度講，即不允許事務(wù)獲取排他鎖，要等到所有的共享鎖都釋放掉）。反之，如果事務(wù)對數(shù)據(jù)已經(jīng)具有排他鎖（只能有一個(gè)），其他的事務(wù)就不能對鎖定的數(shù)據(jù)獲取共享鎖和排他鎖（即排他鎖與共享鎖不能兼容，更多信息請查看鎖兼容性），在此特別強(qiáng)調(diào)一下 鎖定的數(shù)據(jù) ，因?yàn)橛械馁Y料上講解到“一個(gè)連接寫的時(shí)候，另一個(gè)連接可以寫”，實(shí)際上寫的這種情況是各個(gè)連接的讀寫的數(shù)據(jù)不是相同的行，也就是說各個(gè)連接鎖定的數(shù)據(jù)不同。
根據(jù)以上分析，我們總結(jié)為六個(gè)字為“共享讀，排他寫”。
了解了鎖的情況之后，又涉及到一個(gè)問題。事務(wù)究竟要保持鎖多久呢?
一般來說，共享鎖的鎖定時(shí)間與事務(wù)的隔離級別有關(guān)，如果隔離級別為Read Committed的默認(rèn)級別，只在讀取(select)的期間保持鎖定，即在查詢出數(shù)據(jù)以后就釋放了鎖；如果隔離級別為更高的Repeatable read或Serializable，直到事務(wù)結(jié)束才釋放鎖。另說明，如果select語句中指定了HoldLock提示，則也要等到事務(wù)結(jié)束才釋放鎖。
排他鎖直到事務(wù)結(jié)束才釋放。
做出了以上分析，現(xiàn)在我們可能會存在這樣的疑問，到底在執(zhí)行SQL語句的時(shí)候發(fā)出什么樣的鎖呢，這就由事務(wù)的隔離級別決定了。一般情況，讀語句(select)發(fā)出共享鎖，寫語句(update,insert,delete)發(fā)出排他鎖。但是，如果這樣不能滿足我們的要求怎么辦呢，有沒有更多選擇呢，別急，SQLserver為我們提供了鎖定提示的概念。
鎖定提示對SQL語句進(jìn)行特別指定，這個(gè)指定將覆蓋事務(wù)的隔離級別。下面對各個(gè)鎖定提示分別予以介紹（更多資料請查看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é)束。等同于SERIALIZABLE
③ XLOCK：發(fā)出排他鎖，保持到事務(wù)結(jié)束。
④ UPDLOCK：發(fā)出更新鎖，保持到事務(wù)事務(wù)結(jié)束。（更新鎖：不阻塞別的事物，允許別的事物讀數(shù)據(jù)（即更新鎖可與共享鎖兼容），但他確保自上次讀取數(shù)據(jù)后數(shù)據(jù)沒有被更新）
⑤ READPAST：發(fā)出共享鎖，但跳過鎖定行，它不會被阻塞。適用條件：提交讀的隔離級別，行級鎖，select語句中。
類型3
① ROWLOCK：行級鎖
② PAGLOCK：頁級鎖
③ TABLOCK：表鎖
④ TABLOCKX：表排他鎖
講解完鎖后，下面結(jié)合一個(gè)具體實(shí)例，具體看一下鎖的使用。
在很多系統(tǒng)中，經(jīng)常會遇到這種情況，要保持一個(gè)編號的唯一，如會計(jì)軟件中的憑證的編號。一種編號的處理是這樣的，把表中的最大編號保存到表中，然后在這個(gè)編號上累加，形成新的編號。這個(gè)過程對并發(fā)處理要求非常高，下面我們就來模擬這個(gè)過程，看如何保持編號的唯一性。
新建一張表code來保存憑證的最大編號。字段如下：編號:bh(numeric(18,0)),憑證表名pinzheng(varchar(50))
假設(shè)表中有這樣的一條記錄：
|
Bh
|
Pinzheng
|
|
18000
|
會計(jì)憑證
|
新建一個(gè)存儲過程來生成新的憑證編號，如下：
CREATE PROCEDURE up_getbh AS
Begin Tran
Declare @numnewbh numeric(18,0)
select @numnewbh = bh FROM code WITH (UPDLOCK,ROWLOCK) where pinzheng = '會計(jì)憑證'
set @numnewbh = @numnewbh + 1
update code set bh = @numnewbh where pinzheng = '會計(jì)憑證'
print @numnewbh
Commit tran
GO
然后，打開查詢分析器，并多開幾個(gè)連接（筆者開了8個(gè)連接，模擬有8個(gè)人同時(shí)并發(fā)，讀者可以開更多的連接進(jìn)行試驗(yàn)），把類似以下這樣的語句復(fù)制到每個(gè)連接窗口中，
declare @i numeric(18,0)
set @i = 1
while @i = 1
Begin
if getdate() > '2004-07-22 14:23' --設(shè)定一個(gè)時(shí)間，到此時(shí)間同時(shí)執(zhí)行upgetbh存儲過程
set @i = 0
end
exec up_getbh
然后，接連運(yùn)行各個(gè)連接，到2004-7-22 14：23 這一刻，各個(gè)連接同時(shí)運(yùn)行up_getbh。從運(yùn)行結(jié)果可以看出連接順序出現(xiàn)18001開始個(gè)數(shù)字，并沒有重號或丟號的現(xiàn)象。
分析：由于up_getbh中的select語句使用了更新鎖，因更新鎖之間不能兼容，所以各個(gè)連接要等到所有其他的連接釋放掉鎖才能執(zhí)行，而更新鎖的釋放要等到事務(wù)結(jié)束，這樣就不會發(fā)生號出錯(cuò)的現(xiàn)象了。
posted on 2010-02-04 15:01
何克勤 閱讀(742)
評論(0) 編輯 收藏 所屬分類:
數(shù)據(jù)庫和SQL