|
事務(wù)(Transaction)是并發(fā)控制的基本單位。所謂事務(wù),它是一個(gè)操作序列，這些操作要么都執(zhí)行，要么都不執(zhí)行，它是一個(gè)不可分割的工作單位。例如，銀行轉(zhuǎn)帳工作：從一個(gè)賬號扣款并使另一個(gè)賬號增款，這兩個(gè)操作要么都執(zhí)行，要么都不執(zhí)行。所以，應(yīng)該把他們看成一個(gè)事務(wù)。事務(wù)是數(shù)據(jù)庫維護(hù)數(shù)據(jù)一致性的單位，在每個(gè)事務(wù)結(jié)束時(shí)，都能保持?jǐn)?shù)據(jù)一致性。
數(shù)據(jù)一致性問題 多用戶并發(fā)存取同一數(shù)據(jù)將會導(dǎo)致以下的數(shù)據(jù)不一致性問題。 • 丟失修改（ Lost Update） 在下表中，T1、T2、T3和T4表示順序的時(shí)間。 用戶 T 1 T 2 T 3 T 4 A x = 40 X = x-30 B X = 40 X = x-20 假設(shè)用戶A和B都讀取x ( x = 40 ) ,然后分別把x減少30和20。用戶A在t3把改后的x ( x = 10 )寫入數(shù)據(jù)庫。隨后，用戶B在t4把改后的x ( x = 20 )寫入數(shù)據(jù)庫。于是，對用戶A而言，他的修改在t4 處丟失了。 • 臟讀數(shù)據(jù)（ Dirty Read） 請看下表， 用戶 T1 T2 T3 T4 A x = 40 X = x + 30 X = x - 30 rollback B X = 70 X = x-20 用戶A在t2把x增加30（尚沒寫入數(shù)據(jù)庫），用戶B在t3由數(shù)據(jù)緩存讀出x = 70。但用戶A在t4時(shí)撤消（Undo）了對x的修改，數(shù)據(jù)庫中仍維持x = 40。但用戶B已把改變的數(shù)據(jù)（ x = 70）取走。 • 不能重復(fù)讀（Non-Repeatable Read） 用戶 T1 T2 T3 T4 T5 T6 A X=40 Y=30 X+Y=70 Z=30 X+Y+Z=100 B x=40 X=X+20 Commit X=x-20 用戶A、用戶B分別讀取x = 40后，在t 3用戶A取出y = 30并計(jì)算x + y = 70。在t4時(shí)用戶B把x增加20，并于t 5把x ( x = 60 )寫入數(shù)據(jù)庫。在t6時(shí)，用戶A取出z ( z = 30 )并繼續(xù)計(jì)算x + y + z = 100。但如果用戶A為進(jìn)行核算而把x、y、x重讀一次再進(jìn)行計(jì)算，卻出現(xiàn)x + y + z = 120?。▁已增加20）。 如何標(biāo)識一個(gè)事務(wù) 在SQL Server中，通常事務(wù)是指以BEGIN TRAN開始，到ROLLBACK或一個(gè)相匹配的COMMIT之間的所有語句序列。ROLLBACK表示要撤消（ U n d o）該事務(wù)已做的一切操作，回退到事務(wù)開始的狀態(tài)。COMMIT表示提交事務(wù)中的一切操作，使得對數(shù)據(jù)庫的改變生效。 在SQL Server中，對事務(wù)的管理包含三個(gè)方面： • 事務(wù)控制語句：它使程序員能指明把一系列操作（ Transact - SQL命令）作為一個(gè)工作單 位來處理。 • 鎖機(jī)制（ Locking）：封鎖正被一個(gè)事務(wù)修改的數(shù)據(jù)，防止其他用戶訪問到“不一致”的數(shù)據(jù)。 • 事務(wù)日志( Transaction Log)：使事務(wù)具有可恢復(fù)性。 SQL Server的鎖機(jī)制 所謂封鎖,就是一個(gè)事務(wù)可向系統(tǒng)提出請求，對被操作的數(shù)據(jù)加鎖( Lock )。其他事務(wù)必須等到此事務(wù)解鎖（ Unlock）之后才能訪問該數(shù)據(jù)。從而，在多個(gè)用戶并發(fā)訪問數(shù)據(jù)庫時(shí),確保不互相干擾?？涉i定的單位是：行、頁、表、盤區(qū)和數(shù)據(jù)庫。 1. 鎖的類型 SQL Server支持三種基本的封鎖類型：共享（ S）鎖，排它（X）鎖和更新（U）鎖。封鎖的基本粒度為行。 1) 共享（S）鎖：用于讀操作。 • 多個(gè)事務(wù)可封鎖一個(gè)共享單位的數(shù)據(jù)。 • 任何事務(wù)都不能修改加Ｓ鎖的數(shù)據(jù)。 • 通常是加Ｓ鎖的數(shù)據(jù)被讀取完畢，Ｓ鎖立即被釋放。 2) 獨(dú)占（Ｘ）鎖：用于寫操作。 • 僅允許一個(gè)事務(wù)封鎖此共享數(shù)據(jù)。 • 其他任何事務(wù)必須等到Ｘ鎖被釋放才能對該數(shù)據(jù)進(jìn)行訪問。 • Ｘ鎖一直到事務(wù)結(jié)束才能被釋放。 3) 更新（Ｕ）鎖。 • 用來預(yù)定要對此頁施加Ｘ鎖，它允許其他事務(wù)讀，但不允許再施加Ｕ鎖或Ｘ鎖。 • 當(dāng)被讀取數(shù)據(jù)頁將要被更新時(shí)，則升級為Ｘ鎖。 • Ｕ鎖一直到事務(wù)結(jié)束時(shí)才能被釋放。 2. 三種鎖的相容性 如下表簡單描述了三種鎖的相容性： 通常，讀操作（SELECT）獲得共享鎖，寫操作（ INSERT、DELETE）獲得獨(dú)占鎖；而更新操作可分解為一個(gè)有更新意圖的讀和一個(gè)寫操作，故先獲得更新鎖，然后再升級為獨(dú)占鎖。 執(zhí)行的命令 獲得鎖 其他進(jìn)程可以查詢？ 其他進(jìn)程可以修改？ Select title_id from titles S Yes No delete titles where price>25 X No No insert titles values( ...) X No No update titles set type=“general” U Yes No where type=“business” 然后Ｘ NO No 使用索引降低鎖并發(fā)性 我們?yōu)槭裁匆懻撴i機(jī)制？如果用戶操作數(shù)據(jù)時(shí)盡可能鎖定最少的數(shù)據(jù)，這樣處理過程,就不會等待被鎖住的數(shù)據(jù)解鎖，從而可以潛在地提高SQL Server的性能。如果有200個(gè)用戶打算修改不同顧客的數(shù)據(jù)，僅對存儲單個(gè)顧客信息的單一行進(jìn)行加鎖要比鎖住整個(gè)表好得多。那么，用戶如何只鎖定行而不是表呢？當(dāng)然是使用索引了。正如前面所提到的，對存有要修改數(shù)據(jù)的字段使用索引可以提高性能，因?yàn)樗饕苤苯诱业綌?shù)據(jù)所在的頁面，而不是搜索所有的數(shù)據(jù)頁面去找到所需的行。如果用戶直接找到表中對應(yīng)的行并進(jìn)行更新操作，只需鎖定該行即可，而不是鎖定多個(gè)頁面或者整個(gè)表。性能的提高不僅僅是因?yàn)樵谛薷臅r(shí)讀取的頁面較少，而且鎖定較少的頁面潛在地避免了一個(gè)用戶在修改數(shù)據(jù)完成之前其他用戶一直等待解鎖的情況。 事務(wù)的隔離級別 ANSI標(biāo)準(zhǔn)為SQL事務(wù)定義了4個(gè)隔離級別(isolation level)，隔離級別越高,出現(xiàn)數(shù)據(jù)不一致性的可能性就越小(并發(fā)度也就越低)。較高的級別中包含了較低級別中所規(guī)定了的限制。 • 隔離級別0：防止“丟失修改”，允許臟讀。 • 隔離級別1：防止臟讀。允許讀已提交的數(shù)據(jù)。 • 隔離級別2：防止“不可重復(fù)讀”。 • 隔離級別3：“可串行化”（serializable）。其含義為，某組并行事務(wù)的一種交叉調(diào)度產(chǎn)生的結(jié)果和這些事務(wù)的某一串行調(diào)度的結(jié)果相同（可避免破壞數(shù)據(jù)一致性）。SQL Server支持四種隔離級別，級別1為缺省隔離級別，表中沒有隔離級別2， 請參考表： SQL Server支持的隔離級別 封鎖方式 數(shù)據(jù)一致性保證 X鎖施加于被修改的頁 S鎖施加于被讀取的頁 防止丟失修改 防止讀臟數(shù)據(jù) 可以重復(fù)讀取 級別0 封鎖到事務(wù)結(jié)束 是 級別1（缺?。?nbsp; 封鎖到事務(wù)結(jié)束 讀后立即釋放 是 是 級別3 封鎖到事務(wù)結(jié)束 封鎖到事務(wù)結(jié)束 是 是 是 在SQL Server也指定級別2，但級別3已包含級別2。ANSI-92 SQL中要求把級別3作為所有事務(wù)的缺省隔離級別。 SQL Server用holdlock選項(xiàng)加強(qiáng)S鎖的限制，實(shí)現(xiàn)隔離級別3。SQL Server的缺省隔離級別為級別1，共享讀鎖（S鎖）是在該頁被讀完后立即釋放。在select語句中加holdlock選項(xiàng)，則可使S鎖一直保持到事務(wù)結(jié)束才釋放。她符合了ANSI隔離級別3的標(biāo)準(zhǔn)─“可串行化”。 下面這個(gè)例子中，在同一事務(wù)中對avg ( advance )要讀取兩次，且要求他們?nèi)≈挡蛔儵?#8220;可重復(fù)讀”，為此要使用選項(xiàng)holdlock。 BEGIN tran DECLARE @avg-adv money SELECT @avg-adv = avg(advance) FROM titles holdlock WHERE type = "business" if @avg-adv > 5000 SELECT title from titles WHERE type="business" and advance >@avg_adv COMMIT tran 在SQL Server中設(shè)定事務(wù)隔離級別的方法有三種： • 會話層設(shè)定 語法如下： SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE } 系統(tǒng)提供的系統(tǒng)存儲過程將在級別1下執(zhí)行，它不受會話層設(shè)定的影響。 • 語法層設(shè)定 在SELECT、DECLARE cursor及read text語句中增加選項(xiàng)。比如： SELECT...at isolation{0|read uncommitted} 注意：語法層的設(shè)定將替代會話層的設(shè)定。 • 利用關(guān)鍵詞設(shè)定 ─在SELECT語句中，加選項(xiàng)holdlock則設(shè)定級別3 ─在SELECT語句中，加noholdlock則設(shè)定級別0 如下程序清單中所列的腳本實(shí)例在authors表上持有一個(gè)共享鎖，它將用戶檢查服務(wù)器當(dāng)前活動的時(shí)間推遲兩分鐘。 程序清單測試事務(wù)隔離等級 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ GO BEGIN TRAN SELECT * FROM authors WHERE au_lname = 'Green' WAITFOR DELAY '00:02:00' ROLLBACK TRAN GO Activity Legend(活動圖標(biāo))表明：當(dāng)SQL Server檢索數(shù)據(jù)時(shí)會去掉頁面表意向鎖。Current Activity窗口(見圖3 - 3 )顯示共享鎖一直被保持直到事務(wù)完成為止(也就是說，直到WAITFOR和ROLLBACK TRAN語句完成)。 使用鎖定優(yōu)化程序提示 讓我們再深入考察程序清單的實(shí)例。通過改變優(yōu)化程序提示，用戶可以令SQL Server在authors表上設(shè)置一個(gè)獨(dú)占表鎖(如程序所示)。 BEGIN TRAN SELECT * FROM authors (tablockx) WHERE au_lname = 'Green' WAITFOR DELAY '00:02:00' ROLLBACK TRAN GO SELECT語句使用優(yōu)化程序提示tablockx來保持獨(dú)占表鎖直到事務(wù)結(jié)束為止。下表顯示了可用的鎖定優(yōu)化程序提示。 鎖定優(yōu)化程序提示及其描述 優(yōu)化程序提示 優(yōu)化程序提示描述 holdlock 保持鎖定直到事務(wù)結(jié)束 nolock 檢索數(shù)據(jù)時(shí)不使用鎖 paglock 使用頁面鎖 tablock 使用表鎖 tablockx 使用獨(dú)占表鎖 updlock 使用更新鎖 holdlock優(yōu)化程序提示能夠在整個(gè)事務(wù)期間保持共享鎖，讀者在可串行化和可重復(fù)讀事務(wù)隔離等級中對此已很熟悉了。如果用戶偶爾想使用共享鎖，最好使用系統(tǒng)默認(rèn)的讀交付事務(wù)隔離等級并需要使用holdlock優(yōu)化程序提示。holock優(yōu)化程序提示與讀不交付事務(wù)隔離等級有相同的功能，它通過在讀數(shù)據(jù)時(shí)不要任何鎖定而實(shí)現(xiàn)非交付數(shù)據(jù)的讀操作(從而避免了任何獨(dú)占鎖定引起的阻隔)。使用索引和鎖定優(yōu)化程序提示需要注意的是：用戶可以將這兩種類型的提示結(jié)合起來使 用，但必須將索引提示最后列出，這一點(diǎn)很重要。如下程序清單中的代碼給出了合法優(yōu)化程序提示的正確方法。如一個(gè)混合優(yōu)化程序提示 SELECT * FROM authors (paglock holdlock index=aunmind) Delphi的Dataset屬性已封裝好以上的解決方法﹐ 當(dāng)讀數(shù)據(jù)時(shí)： CacheSize property = 1000 CursorLocation = clUseClient LockType = ltReadOnly CursorType = ctKeyset CommandTimeOut = 30 當(dāng)打印數(shù)據(jù)時(shí)﹕ CacheSize property = 1000 CursorLocation = clUseClient LockType = ltReadOnly CursorType = ctOpenForwardOnly CommandTimeOut = 30 當(dāng)寫數(shù)據(jù)時(shí)： CacheSize property = 1000 CursorLocation = clUseServer LockType = ltPessimistic CursorType = ctKeyset CommandTimeOut = 5 |
