數(shù)據(jù)庫基礎(chǔ)PPT第7章 事務(wù)處理及鎖

第7章

事務(wù)處理與鎖7.1事務(wù)概述7.2事務(wù)處理7.3鎖簡介7.4死鎖及其排除簡介Page129十月2023教學(xué)要求：通過本章學(xué)習(xí)，讀者應(yīng)掌握以下內(nèi)容：通過學(xué)習(xí)本章，讀者應(yīng)掌握以下內(nèi)容：了解SQLServer中事務(wù)與鎖的概念了解并發(fā)控制，掌握理解事務(wù)控制語句了解可以鎖定的資源項和鎖的類型了解死鎖的概念，死鎖的排除，鎖定信息的顯示Page229十月20237.1事務(wù)概述

多用戶并發(fā)存取同一數(shù)據(jù)可能會導(dǎo)致產(chǎn)生數(shù)據(jù)的不一致性問題，正確地使用事務(wù)處理可以有效控制這類問題的發(fā)生頻度。7.1.1事務(wù)的概念事務(wù)（Transaction）是用戶定義的一個數(shù)據(jù)庫操作序列，是一個不可分割的工作單位。要么所有操作順序完成，要么一個也不要做，絕不能只完成了部分操作，還有一些沒有完成。在關(guān)系數(shù)據(jù)庫中，一個事務(wù)可以是一條SQL語句、一組SQL語句或整個程序。7.1.2事務(wù)的特征

事務(wù)是作為單個邏輯工作單元執(zhí)行的一系列操作。事務(wù)作為一個邏輯工作單元有4個屬性。(1)原子性。事務(wù)必須是原子工作單元，對于其數(shù)據(jù)修改，要么全都執(zhí)行，要么全都不執(zhí)行。(2)一致性。事務(wù)在完成時，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。(3)隔離性。由并發(fā)事務(wù)所做的修改必須與任何其他并發(fā)事務(wù)所做的修改隔離。

(4)持久性。事務(wù)完成之后對系統(tǒng)的影響是永久的。事務(wù)一旦提交，它對數(shù)據(jù)庫的更新不再受后繼操作或故障的影響。7.2事務(wù)處理

1．顯示啟動事務(wù)（BEGINTRANSACTION）BEGINTRANSACTION語句用來顯示定義事務(wù)，其語法格式如下：BEGINTRANSACTION[SACTION][transaction_name|@tran_name_variable[WITHMARK[‘description’]]]

各參數(shù)說明如下：(1)transaction_name是給事務(wù)分配的名稱。transaction_name必須遵循標(biāo)識符規(guī)則，但是不允許標(biāo)識符多于32個字符。僅在嵌套的BEGIN…COMMIT或BEGIN…ROLLBACK語句的最外語句上使用事務(wù)名。(2)@tran_name_variable是用戶定義的、含有有效事務(wù)名稱的變量名稱。必須用char、varchar、nchar或nvarchar數(shù)據(jù)類型聲明該變量。(3)WITHMARK[‘description’]用來指定在日志中標(biāo)記事務(wù)。Description是描述該標(biāo)記的字符串。如果使用了WITHMARK，則必須指定事務(wù)名。WITHMARK允許將事務(wù)日志還原到命名標(biāo)記。2．隱式啟動事務(wù)通過API函數(shù)或T-SQLSETIMPLICIT_TRANSACTIONON語句，將隱性事務(wù)模式設(shè)置為打開。下一個語句自動啟動一個新事務(wù)。當(dāng)該事務(wù)完成時，下一個T-SQL語句又將啟動一個新事務(wù)。應(yīng)用程序再使用SETIMPLICIT_TRANSACTIONOFF語句關(guān)閉隱式事務(wù)模式3．事務(wù)提交（COMMITTRANSACTION）COMMITTRANSACTION語句用來標(biāo)志一個成功的隱式事務(wù)或顯示事務(wù)的結(jié)束。其語法格式為：

COMMIT[TRAN[SACTION][transaction_name|@tran_name_variable]]將顯示事務(wù)或隱式事務(wù)回滾到事務(wù)的起點(diǎn)或事務(wù)內(nèi)的某個保存點(diǎn)。其語法格式如下：

ROLLBACKTRAN[SACTION][transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]]各參數(shù)說明如下：(1)transaction_name。是BEGINTRANSACTION上的事務(wù)指派的名稱。transaction_name必須遵循標(biāo)識符規(guī)則。嵌套事務(wù)時transaction_name必須是來自最遠(yuǎn)的BEGINTRANSACTION語句的名稱。(2)@tran_name_variable是用戶定義的、含有有效事務(wù)名稱的變量名稱。(3)savepoint_name。是來自SAVETRANSACTION語句的保存點(diǎn)名稱，必須符合標(biāo)識符規(guī)則。(4)@savepoint_variable。是用戶定義的、含有有效保存點(diǎn)名稱的變量。必須用char、varchar、nchar或nvarchar數(shù)據(jù)類型聲明該變量。4．事務(wù)回滾ROLLBACKTRANSACTION

5．設(shè)置保存點(diǎn)在事務(wù)內(nèi)設(shè)置保存點(diǎn)，其語法格式如下：

SAVETRAN[SACTION]{savepoint_name|@savepoint_variable}6．事務(wù)嵌套與BEGIN…END語句類似，BEGINTRANSACTION和COMMITTRANSACTION語句也可以進(jìn)行嵌套，即事務(wù)可以嵌套執(zhí)行。【例7.1】定義一個事務(wù)，向“學(xué)生信息”表添加一條記錄，并設(shè)置保存點(diǎn)。然后再刪除該記錄，并回滾到事務(wù)的保存點(diǎn)，提交事務(wù)。語句如下：USEjxglGOBEGINTRANSACTIONINSERTINTO學(xué)生信息(stu_id,stu_name,stu_sex,stu_birth)VALUES('2001020222',張小明','男','2002/09/23')SAVETRANSACTIONsavepoint_1DELETEFROM學(xué)生信息WHEREstu_id='2001020222'ROLLBACKTRANSACTIONsavepoint_1COMMITTRANSACTIONGO【例7.2】事務(wù)的隱式啟動。USEjxglGOSETIMPLICIT_TRANSACTIONSON--啟動隱式事務(wù)模式GO--第一個事務(wù)由INSERT語句啟動INSERTINTO成績VALUES(’2001020222’,’100101’,100)COMMITTRANSACTION--提交第一個隱式事務(wù)GO--第二個隱式事務(wù)由SELECT語句啟動SELECTCOUNT(*)FROM成績DELETEFROM成績WHEREstu_id=’2001020222’COMMITTRANSACTION--提交第二個隱式事務(wù)GOSETIMPLICIT_TRANSACTIONSOFF--關(guān)閉隱式事務(wù)模式GO7.3鎖簡介

為了保證事務(wù)的隔離性和一致性，數(shù)據(jù)庫管理系統(tǒng)需要對并發(fā)操作進(jìn)行正確調(diào)度。如果沒有調(diào)度好而導(dǎo)致多個用戶同時訪問一個數(shù)據(jù)庫，那么當(dāng)他們的事務(wù)同時使用相同的數(shù)據(jù)時可能會產(chǎn)生數(shù)據(jù)的不一致性。這些問題主要體現(xiàn)在下面幾個方面。1．讀“臟”數(shù)據(jù)庫（DirtyRead）時間讀“臟”數(shù)據(jù)時間讀“臟”數(shù)據(jù)事務(wù)T1數(shù)據(jù)庫中R的值事務(wù)T2事務(wù)T1數(shù)據(jù)庫中R的值事務(wù)T2t0t1t2t3

READRR=R-200UPDATER1000

t4t5t6t7

ROLLBACK800

1000READR2．不可重復(fù)讀（None-RepeatableRead）時間不可重復(fù)讀時間不可重復(fù)讀事務(wù)T1數(shù)據(jù)庫中R的值事務(wù)T2事務(wù)T1數(shù)據(jù)庫中R的值事務(wù)T2t0t1t2t3

READR

1000

READRR=R-300t4t5t6t7

READR

700

UPDATERCOMMIT3．丟失修改（LostUpdate）時間丟失數(shù)據(jù)時間丟失數(shù)據(jù)事務(wù)T1數(shù)據(jù)庫中R的值事務(wù)T2事務(wù)T1數(shù)據(jù)庫中R的值事務(wù)T2t0t1t2t3

READR

R=R-2001000

READR

t4t5t6t7

UPDATERREADR

800700R=R-300

UPDATER7.3.1SQLServer鎖的模式

根據(jù)鎖定資源的方式的不同，SQLServer提供了共享鎖、更新鎖、排他鎖、意向鎖等。1．共享（Shared）鎖共享鎖允許并發(fā)事務(wù)讀取一個資源。資源上存在共享（S）鎖時，任何其他事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享（S）鎖，除非將事務(wù)隔離級別設(shè)置為可重復(fù)讀或更高級別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享（鎖）。它用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如SELECT語句。2．更新（Update）鎖更新鎖也稱為U鎖，它可以防止常見的死鎖。更新鎖用來預(yù)定要對資源施加X鎖，它允許其他事務(wù)讀，但不允許再施加U鎖或X鎖。3．排他（Exclusive）鎖排他（X）鎖可以防止并發(fā)事務(wù)對資源進(jìn)行訪問。其他事務(wù)不能讀取或修改排他（X）鎖鎖定的數(shù)據(jù)。用于數(shù)據(jù)修改操作，例如INSERT、UPDATE或DELETE，確保不會同時同一資源進(jìn)行多重更新。4．意向（Intent）鎖數(shù)據(jù)庫引擎使用意向鎖來保護(hù)共享鎖（S）或排他（X）鎖，放置在鎖層次結(jié)構(gòu)的底層資源上。意向鎖的類型為意向共享（IS）、意向排他（IX）以及與意向排他共享（SIX）。5．架構(gòu)（Schema）鎖執(zhí)行表的數(shù)據(jù)定義語言（DDL）操作（如添加列或刪除列）時使用架構(gòu)修改鎖（Sch-M）。在架構(gòu)修改鎖（Sch-M）起作用期間，會防止對表的并發(fā)訪問。6．BU——大量更新（BulkUpdate）當(dāng)將數(shù)據(jù)大容量復(fù)制到表，且指定了TABLOCK提示或者使用sp_tableoption設(shè)置了tablelockonbulk表選項時，將使用大容量更新（BU）鎖。7．RANGE——鍵范圍（Key-Range）在使用可序列化事務(wù)隔離級別時，對于T-SQL語句讀取的記錄集，鍵范圍鎖可以隱式保護(hù)該記錄集中包含的行范圍。7.3.2SQLServer中鎖的查看

SQLServer為了盡量減少鎖定的開銷，允許一個事務(wù)鎖定不同類型的資源，具有多粒度鎖定機(jī)制。SQLServer可以對行、頁、鍵、鍵范圍、索引、表或數(shù)據(jù)庫獲取鎖。數(shù)據(jù)行(Row)。數(shù)據(jù)頁中的單行數(shù)據(jù)。索引行。索引頁中的單行數(shù)據(jù)，即索引的鍵值。頁(Page)。頁是SQLServer存取數(shù)據(jù)的基本單位，其大小是8KB。擴(kuò)展盤區(qū)(Extent)。一個盤區(qū)由8個連續(xù)的頁組成。表(Table)。數(shù)據(jù)庫(Database)。鎖定在較小的粒度（如行）可以提高并發(fā)度，因?yàn)槿绻i定了許多行，則需要持有更多的鎖，開銷較高。鎖定在較大的粒度（如表）會降低并發(fā)度，因?yàn)殒i定整個表限制了其他事務(wù)對表中任意部分的訪問，需要維護(hù)的鎖較小，開鎖較低。用系統(tǒng)存儲過程查看鎖使用系統(tǒng)存儲過程sp_lock查看SQLServer所持有的所有鎖的信息。系統(tǒng)存儲過程sp_lock的語法格式如下：

sp_lock[[@spid1=]‘spid1’][,@spid2=]‘spid2’]

參數(shù)spid1、spid2都是來自master.dbo.sysprocessesrSQLServer進(jìn)程ID號。Spid1的數(shù)據(jù)類型為int，默認(rèn)值為NULL。如果沒有指定spid，則顯示所有鎖的信息。【例7.3】查看SQLServer中當(dāng)前持有的所有鎖的信息。語句如下：USEmasterEXECsp_lock7.4死鎖及其排除簡介

在事務(wù)和鎖的使用過程中，死鎖是不可避免的。一般來說，對數(shù)據(jù)庫的修改由一個事務(wù)組成，此事務(wù)讀取記錄，獲取資源的共享鎖，如果要修改記錄行，需要轉(zhuǎn)換成排他鎖。如果兩個事務(wù)獲得了資源上的共享鎖，然后試圖同時更新數(shù)據(jù)，都要求加排他鎖，就會發(fā)生兩個事務(wù)互相等待對方釋放共享鎖的情況，這種現(xiàn)象稱為死鎖，如果不加干預(yù)，死鎖中的兩個事務(wù)都將無限期等待下去?！纠?.4】死鎖示例。測試用的基礎(chǔ)數(shù)據(jù)。CREATETABLELocktable1(c1intdefault(0));CREATETABLELocktable2(c1intdefault(0));INSERTINTOLocktable1values(1);INSERTINTOLocktable2values(1);打開兩個查詢窗口，分別執(zhí)行下面兩段SQL代碼。--查詢語句1BEGINTRANUPDATELocktable1SETc1=c1+2;WAITFORDelay’00:01:00’;SELECT*FROMLocktable2ROL