數(shù)據(jù)庫事務(wù)與鎖

數(shù)據(jù)庫事務(wù)與鎖第1頁，共31頁，2023年，2月20日，星期六學(xué)習(xí)目標(biāo)掌握事務(wù)掌握鎖第2頁，共31頁，2023年，2月20日，星期六一、事務(wù)事務(wù)是并發(fā)控制的基本單位。所謂事務(wù)，就是一個操作序列，這些操作要么都執(zhí)行，要么都不執(zhí)行，它是一個不可分割的工作單位。如果某一事務(wù)成功，則在該事務(wù)中進行的所有數(shù)據(jù)修改均會提交，成為數(shù)據(jù)庫中的永久組成部分。如果事務(wù)遇到錯誤且必須取消或回滾，則所有數(shù)據(jù)修改均被清除。第3頁，共31頁，2023年，2月20日，星期六1.1、事務(wù)的特性原子性一致性隔離性持久性第4頁，共31頁，2023年，2月20日，星期六1.2、事務(wù)的分類（1）自動提交事務(wù)每條單獨的語句都是一個事務(wù)。（2）顯式事務(wù)每個事務(wù)均以BEGINTRANSACTION語句顯式開始，以COMMIT或ROLLBACK語句顯式結(jié)束。（3）隱式事務(wù)在前一個事務(wù)完成時新事務(wù)隱式啟動，但每個事務(wù)仍以COMMIT或ROLLBACK語句顯式完成。通過SetImplicit_TransactionsOn命令。第5頁，共31頁，2023年，2月20日，星期六1.2.1、自動提交事務(wù)SQLServer2005將一切都作為事務(wù)來處理，如果用戶沒有定義事務(wù)，它會自動定義用戶或應(yīng)用程序各種數(shù)據(jù)庫操作的事務(wù)，這稱之為自動提交事務(wù)，也是SQLServer2005默認的事務(wù)處理模式。如：createtableaaa(intnotnull)Insertintoaavalues(1)Insertintoaavalues(null)Insertintoaavalues(2)其結(jié)果是第一和第三條語句插入，第二條失敗SQLServer2005在自動提交事務(wù)工作模式下，每條語句本身就是一個事務(wù)第6頁，共31頁，2023年，2月20日，星期六1.2.2、顯式事務(wù)顯示事務(wù)是執(zhí)行的SQL語句之前增加begintransaction，事務(wù)提交采用committransaction，事務(wù)回滾采用rollbacktransaction，保留事務(wù)點采用savetransaction比如：begintransactioninsertintoaavalues(1)insertintoaavalues(null)IF@@ERROR<>0BEGINrollbacktransactionreturnENDinsertintoaavalues(2)committransaction因為第二條語句的出錯，導(dǎo)致三條語句都不成功。

第7頁，共31頁，2023年，2月20日，星期六插入的知識點：TRY...CATCH對Transact-SQL實現(xiàn)類似于C#和C++語言中的異常處理的錯誤處理。Transact-SQL語句組可以包含在TRY塊中。如果TRY塊內(nèi)部發(fā)生錯誤，則會將控制傳遞給CATCH塊中包含的另一個語句組。改造如下：begintransactionBEGINTRYinsertintoaavalues(1) insertintoaavalues(null) insertintoaavalues(2) committransactionENDTRYBEGINCATCHprint'錯誤號為:'+cast(@@errorasvarchar(10))print'錯誤內(nèi)容為:'+ERROR_MESSAGE()rollbacktransactionENDCATCH第8頁，共31頁，2023年，2月20日，星期六保留點的使用保留點是事務(wù)內(nèi)部的一個標(biāo)識，可以做事務(wù)的局部回滾。改造如下：begintransactioninsertintoaavalues(1)Savetransactionaainsertintoaavalues(3)insertintoaavalues(2)rollbacktransactionaaCommittransaction其結(jié)果是后兩條得到了回滾，而第一條數(shù)據(jù)進行了提交。第9頁，共31頁，2023年，2月20日，星期六1.2.3、隱式事務(wù)要改變SQLServer2005的顯式事務(wù)缺省設(shè)置為隱式，需要采用如下的語句：

setimplicit_transactionson這樣就相當(dāng)于每個delete、update、insert語句都開始一個begintransaction,那么也需要committransaction或者rollbacktransaction來進行結(jié)束事務(wù)?？梢試L試deletefromaa后，再打開一個會話，去select*fromaa，發(fā)現(xiàn)了什么？結(jié)論是阻塞鎖的發(fā)生，所以對于一個事務(wù)的結(jié)束是網(wǎng)絡(luò)編程對數(shù)據(jù)庫操作的必須注意的地方，否則會造成網(wǎng)絡(luò)的癱瘓。采用setimplicit_transactionsoff關(guān)閉隱式事務(wù)。第10頁，共31頁，2023年，2月20日，星期六1.3設(shè)置事務(wù)隔離級別在SQL92標(biāo)準(zhǔn)中，事務(wù)隔離級別分為四種，分別為：ReadUncommitted、ReadCommitted、RepeatableRead、Serializable，其中ReadUncommitted與ReadCommitted為語句級別的，而RepeatableRead與Serializable是針對事務(wù)級別的。Sqlserver的隔離級別默認都是：ReadCommitted，可以使用語句SetTransactionIsolationLevelReadCommitted來設(shè)置第11頁，共31頁，2023年，2月20日，星期六ReadUncommitted一個會話可以讀取其他事務(wù)未提交的更新結(jié)果。 如果這個事務(wù)最后以回滾結(jié)束，這時的讀取結(jié)果就可能是錯誤的，所以多數(shù)的數(shù)據(jù)庫應(yīng)用都不會使用這種隔離級別。 注意:另外一個會話也要設(shè)置為ReadUncommitted.第12頁，共31頁，2023年，2月20日，星期六ReadCommitted只能讀取其他事務(wù)已經(jīng)提交的更新結(jié)果，否則，發(fā)生等待。 但是其他會話可以修改這個事務(wù)中被讀取的記錄，而不必等待事務(wù)結(jié)束，顯然，在這種隔離級別下，一個事務(wù)中的兩個相同的讀取操作，其結(jié)果可能不同。比如：begintransactionselect*fromaawaitfordelay'00:00:05'select*fromaarollbacktransaction第13頁，共31頁，2023年，2月20日，星期六RepeatableRead在一個事務(wù)中，如果在兩次相同條件的讀取操作之間沒有添加記錄的操作，也沒有其他更新操作導(dǎo)致在這個查詢條件下記錄數(shù)增多，則兩次讀取結(jié)果相同。換句話說，就是在一個事務(wù)中第一次讀取的記錄保證不會在這個事務(wù)期間發(fā)生改變。SQLServer是通過在整個事務(wù)期間給讀取的記錄加鎖實現(xiàn)這種隔離級別的，這樣，在這個事務(wù)結(jié)束前，其他會話不能修改事務(wù)中讀取的記錄，而只能等待事務(wù)結(jié)束，但是SQLServer不會阻礙其他會話向表中添加記錄，也不阻礙其他會話修改其他記錄。 如：

SETTRANSACTIONISOLATIONLEVELREPEATABLEREADGO

begintransactionselect*fromaawaitfordelay'00:00:15'select*fromaarollbacktransaction第14頁，共31頁，2023年，2月20日，星期六SerializableSqlserver:比RepeatableRead實現(xiàn)更加嚴格的控制，在整個事務(wù)中不允許插入數(shù)據(jù)。比如：SETTRANSACTIONISOLATIONLEVELSerializableGO

begintransactionselect*fromaawaitfordelay'00:00:15'select*fromaarollbacktransaction第15頁，共31頁，2023年，2月20日，星期六二、鎖鎖（LOCKING）是最常用的并發(fā)控制機構(gòu)。是防止其他事務(wù)訪問指定的資源控制、實現(xiàn)并發(fā)控制的一種主要手段。鎖是事務(wù)對某個數(shù)據(jù)庫中的資源（如表和記錄）存取前，先向系統(tǒng)提出請求，封鎖該資源，事務(wù)獲得鎖后，即取得對數(shù)據(jù)的控制權(quán)，在事務(wù)釋放它的鎖之前，其他事務(wù)不能更新此數(shù)據(jù)。當(dāng)事務(wù)撤消后，釋放被鎖定的資源。

第16頁，共31頁，2023年，2月20日，星期六2.1、為什么要引入鎖當(dāng)多個用戶同時對數(shù)據(jù)庫的并發(fā)操作時會帶來以下數(shù)據(jù)不一致的問題:◆丟失更新A,B兩個用戶讀同一數(shù)據(jù)并進行修改,其中一個用戶的修改結(jié)果破壞了另一個修改的結(jié)果◆臟讀A用戶修改了數(shù)據(jù),隨后B用戶又讀出該數(shù)據(jù),但A用戶因為某些原因取消了對數(shù)據(jù)的修改,數(shù)據(jù)恢復(fù)原值,此時B得到的數(shù)據(jù)就與數(shù)據(jù)庫內(nèi)的數(shù)據(jù)產(chǎn)生了不一致◆不可重復(fù)讀A用戶讀取數(shù)據(jù),隨后B用戶讀出該數(shù)據(jù)并修改,此時A用戶再讀取數(shù)據(jù)時發(fā)現(xiàn)前后兩次的值不一致并發(fā)控制的主要方法是封鎖,鎖就是在一段時間內(nèi)禁止用戶做某些操作以避免產(chǎn)生數(shù)據(jù)不一致第17頁，共31頁，2023年，2月20日，星期六2.2、鎖的粒度SQLServer具有多粒度鎖定，允許一個事務(wù)鎖定不同類型的的資源。為了使鎖定的成本減至最少，SQLServer自動將資源鎖定在適合任務(wù)的級別。鎖定在較小的粒度（例如行）可以增加并發(fā)但需要較大的開銷，因為如果鎖定了許多行，則需要控制更多的鎖。鎖定在較大的粒度（例如表）就并發(fā)而言是相當(dāng)昂貴的，因為鎖定整個表限制了其它事務(wù)對表中任意部分進行訪問，但要求的開銷較低，因為需要維護的鎖較少。SQLServer可以鎖定行、頁、擴展盤區(qū)、表、庫等資源。第18頁，共31頁，2023年，2月20日，星期六插入的話題：關(guān)于存儲結(jié)構(gòu)SQLServer的存儲結(jié)構(gòu)為：文件組－文件－區(qū)(8*8=64k)－頁(8K)，最小單位為頁，頁的類型有：第19頁，共31頁，2023年，2月20日，星期六按照粒度級別，SQLServer提供了如下類型的鎖:整個數(shù)據(jù)庫數(shù)據(jù)庫級鎖Database整個表表級鎖Table一組數(shù)據(jù)頁或者索引頁頁級鎖Extent一個數(shù)據(jù)頁或者索引頁頁級鎖Page索引中的行行級鎖Key表中的單個行行級鎖RID描述級別資源第20頁，共31頁，2023年，2月20日，星期六選擇多大的粒度，根據(jù)對數(shù)據(jù)的操作而定。如果是更新表中所有的行，則用表級鎖;如果是更新表中的某一行，則用行級鎖。

行級鎖是一種最優(yōu)鎖，因為行級鎖不可能出現(xiàn)數(shù)據(jù)既被占用又沒有使用的浪費現(xiàn)象。但是，如果用戶事務(wù)中頻繁對某個表中的多條記錄操作，將導(dǎo)致對該表的許多記錄行都加上了行級鎖，數(shù)據(jù)庫系統(tǒng)中鎖的數(shù)目會急劇增加，這樣就加重了系統(tǒng)負荷，影響系統(tǒng)性能。因此，在SQLServer中，還支持鎖升級(lockescalation)。

所謂鎖升級是指調(diào)整鎖的粒度，將多個低粒度的鎖替換成少數(shù)的更高粒度的鎖，以此來降低系統(tǒng)負荷。在SQLServer中當(dāng)一個事務(wù)中的鎖較多，達到鎖升級門限時，系統(tǒng)自動將行級鎖和頁面鎖升級為表級鎖。

特別值得注意的是，在SQLServer中，鎖的升級門限以及鎖升級是由系統(tǒng)自動來確定的，不需要用戶設(shè)置。第21頁，共31頁，2023年，2月20日，星期六2.3、鎖的模式鎖模式描述共享（S）用于不更改或不更新數(shù)據(jù)（只讀操作），如SELECT語句更新（U）用于可更新的資源中。防止當(dāng)多個會話在讀取、鎖定以及隨后可能進行的資源更新時發(fā)生常見形式的死鎖。排它（X）用于數(shù)據(jù)修改操作，例如INSERT、UPDATE或DELETE。確保不會同時對同一資源進行多重更新意向當(dāng)MicrosoftSQLServer數(shù)據(jù)庫引擎獲取低級別的鎖時，它還將在包含更低級別對象的對象上放置意向鎖.例如：當(dāng)鎖定行或索引鍵范圍時，數(shù)據(jù)庫引擎將在包含行或鍵的頁上放置意向鎖。當(dāng)鎖定頁時，數(shù)據(jù)庫引擎將在包含頁的更高級別的對象上放置意向鎖。

意向鎖的類型為：意向共享（IS）、意向排它（IX）以及意向排它共享（SIX）架構(gòu)在執(zhí)行依賴于表架構(gòu)的操作時使用。架構(gòu)鎖的類型為：架構(gòu)修改（Sch-M）和架構(gòu)穩(wěn)定（Sch-S）大容量更新（BU）向表中大容量復(fù)制數(shù)據(jù)第22頁，共31頁，2023年，2月20日，星期六2.4、查看鎖1查詢分析器上執(zhí)行EXECSP_LOCK報告有關(guān)鎖的信息2企業(yè)管理器上查看鎖，見下圖：第23頁，共31頁，2023年，2月20日，星期六2.5、如何避免死鎖1使用事務(wù)時，盡量縮短事務(wù)的邏輯處理過程，及早提交或回滾事務(wù)；2設(shè)置鎖超時參數(shù)為合理范圍，如：3分鐘-10分種；超過時間，自動放棄本次操作，避免進程懸掛；(SETLOCK_TIMEOUT3000)3所有的SP都要有錯誤處理（通過@error）4一般不要修改SQLSERVER事務(wù)的默認級別。(SET

TRANSACTION

ISOLATION

LEVEL)5在事務(wù)中盡量避免用戶干預(yù)，盡量使一個事務(wù)處理的任務(wù)少些。6盡量不要手工加鎖第24頁，共31頁，2023年，2月20日，星期六2.6、排它鎖的例子--

A事務(wù)先更新table1表，在更新時，對其他事務(wù)進行排他

begin

tran

update

table1

set

A='aa'

where

B='b2'；

waitfor

delay

'00:00:30'；

--等待30秒

commit

tran

--

A事務(wù)先更新table2表

begin

tran

select

*

from

table1

where

B='b2';

commit

tran

若同時執(zhí)行上述兩個事務(wù)，則select查詢必須等待update執(zhí)行完畢才能執(zhí)行即要等待30秒

第25頁，共31頁，2023年，2月20日，星期六2.7、共享鎖的例子--A事務(wù)先查詢aa表，在查詢時，加共享鎖，防止其他事務(wù)對該表進行修改操作begintransactionselect*fromaawith(holdlock)whereb='2'waitfordelay'00:00:30';--等待秒committransaction--

B事務(wù)先查詢table1表，后更改table1表begintransactionselect*fromaawhereb='2'updateaasetb='22'whereb='2'committransaction

注意：這個是發(fā)生在默認的隔離級別ReadCommitted下，如果發(fā)生在RepeatableRead或者Serializable下將會自動加共享鎖。若并發(fā)執(zhí)行上述兩個事務(wù)，則B事務(wù)中的select查詢可以執(zhí)行,而update必須等待第一個事務(wù)釋放共享鎖后才能執(zhí)行即要等待30秒第26頁，共31頁，2023年，2月20日，星期六2.8、死鎖的例子--

A事務(wù)先更新table1表，然后延時30秒，再更新table2表；

begin

tran

update

table1

set

A='aa'

where

B='b2';

--這將在

Table1

中生成排他行鎖，直到事務(wù)完成后才會釋放該鎖。

waitfor

delay

'00:00:30';

--進入延時

update

table2

set

D='d5'

where

E='e1'

;

commit

tran

--

B事務(wù)先更新table2表，然后延時10秒，再更新table1表；

begin

tran

update

table2

set

D='d5'

where

E='e1';

--這將在

Table2

中生成排他行鎖，直到事務(wù)完成后才會釋放該鎖

waitfor

delay

'00:00:10'

--進入延時

update

table1

set

A='aa'

where

B='b2'

;

commit

tran

若并發(fā)執(zhí)行上述兩個事務(wù)，A,B兩事務(wù)都要等待對方釋放排他鎖，這樣便形成了死鎖。第27頁，共31頁，2023年，2月20日，星期六2.9、手工加鎖的命令語法：select*fromtablewhit(命令)1．HOLDLOCK:在該表上保持共享鎖，直到整個事務(wù)結(jié)束，而不是在語句執(zhí)行完立即釋放所添加的鎖。

2．NOLOCK：不添加共享鎖和排它鎖，當(dāng)這個選項生效后，可能讀到未提交讀的數(shù)據(jù)或“臟數(shù)據(jù)”，這個選項僅僅應(yīng)用于SELECT語句。

3．PAGLOCK：指定添加頁鎖（否則通?？赡芴砑颖礞i）4．READCOMMITTED用與運行在提交讀隔離級別的事務(wù)相同的鎖語義執(zhí)行掃描。默認情況下，SQLServer2000在此隔離級別上操作。5．READPAST:跳過已經(jīng)加鎖的數(shù)據(jù)行，這個選項將使事務(wù)讀取數(shù)據(jù)時跳過那些已經(jīng)被其他事務(wù)鎖定的數(shù)據(jù)行，而不是阻塞直到其他事務(wù)釋放鎖，READPAST僅僅應(yīng)用于READCOMMITTED隔離性級別下事務(wù)操作中的SELECT語句操作6．READUNCOMMITTED：等同于NOLOCK。

7．REPEATABLEREAD：設(shè)置事務(wù)為可重復(fù)讀隔離性級別。

8．ROWLOCK：使用行級鎖，而不使用粒度更粗的頁級鎖和表級鎖。9．SERIALIZABLE：用與運行在可串行讀隔離級別的事務(wù)相同的鎖語義執(zhí)行掃描。等同于HOLDLOCK。10．TABLOCK：指定使用表級鎖，而不是使用行級或頁面級的鎖，SQLServer在該語句執(zhí)行完后釋放這個鎖，而如果同時指定了HOLDLOCK，該鎖一直保持到這個事務(wù)結(jié)束。11．TABLOCKX：指定在表上使用排它鎖，這個鎖可以阻止其他事務(wù)讀或更新這個表的數(shù)據(jù)，直到這個語句或整個事務(wù)結(jié)束。12．UPDLOCK：指定在讀表中數(shù)據(jù)時設(shè)置更新鎖（updatelock）而不是設(shè)置共享鎖，該鎖一直保持到這個語句或整個事務(wù)結(jié)束，使用UPDLOCK的作用是允許用戶先讀取數(shù)據(jù)（而且不阻塞其他用戶讀數(shù)據(jù)），并且保證在后來再更新數(shù)據(jù)時，這一段時間內(nèi)這些數(shù)據(jù)沒有被其他用戶修改SELECT*FROMtableWITH(HOLDLOCK)其他事務(wù)可以讀取表，但不能更新刪除SELECT*FROMtableWITH(TABLOCKX)其他事務(wù)不能讀取表,更新和刪除第28頁，共31頁，2023年，2月20日，星期六課程小結(jié)本課程是非常重要并且有難度，事務(wù)是數(shù)據(jù)庫運行的最小單位，可以說沒有事務(wù)，則數(shù)據(jù)庫根本無法實現(xiàn)數(shù)據(jù)的完整和一致性。我們在使用事務(wù)的同時，不知不覺用到了鎖，鎖是系統(tǒng)自動定義的一個對象，用以保證并發(fā)的順利完成。對于鎖我們不提倡手工控制，但是我們要知道鎖的原理以及并發(fā)操作是如何通過使用鎖來實現(xiàn)的。第29頁，共31頁，2023年，2