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

第7章

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

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

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

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

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

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

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

ROLLBACKTRAN[SACTION][transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]]各參數(shù)說(shuō)明如下：(1)transaction_name。是BEGINTRANSACTION上的事務(wù)指派的名稱。transaction_name必須遵循標(biāo)識(shí)符規(guī)則。嵌套事務(wù)時(shí)transaction_name必須是來(lái)自最遠(yuǎn)的BEGINTRANSACTION語(yǔ)句的名稱。(2)@tran_name_variable是用戶定義的、含有有效事務(wù)名稱的變量名稱。(3)savepoint_name。是來(lái)自SAVETRANSACTION語(yǔ)句的保存點(diǎn)名稱，必須符合標(biāo)識(shí)符規(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)，其語(yǔ)法格式如下：

SAVETRAN[SACTION]{savepoint_name|@savepoint_variable}6．事務(wù)嵌套與BEGIN…END語(yǔ)句類似，BEGINTRANSACTION和COMMITTRANSACTION語(yǔ)句也可以進(jìn)行嵌套，即事務(wù)可以嵌套執(zhí)行?！纠?.1】定義一個(gè)事務(wù)，向“學(xué)生信息”表添加一條記錄，并設(shè)置保存點(diǎn)。然后再刪除該記錄，并回滾到事務(wù)的保存點(diǎn)，提交事務(wù)。語(yǔ)句如下：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ù)的隱式啟動(dòng)。USEjxglGOSETIMPLICIT_TRANSACTIONSON--啟動(dòng)隱式事務(wù)模式GO--第一個(gè)事務(wù)由INSERT語(yǔ)句啟動(dòng)INSERTINTO成績(jī)VALUES(’2001020222’,’100101’,100)COMMITTRANSACTION--提交第一個(gè)隱式事務(wù)GO--第二個(gè)隱式事務(wù)由SELECT語(yǔ)句啟動(dòng)SELECTCOUNT(*)FROM成績(jī)DELETEFROM成績(jī)WHEREstu_id=’2001020222’COMMITTRANSACTION--提交第二個(gè)隱式事務(wù)GOSETIMPLICIT_TRANSACTIONSOFF--關(guān)閉隱式事務(wù)模式GO7.3鎖簡(jiǎn)介

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

READRR=R-200UPDATER1000

t4t5t6t7

ROLLBACK800

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

READR

1000

READRR=R-300t4t5t6t7

READR

700

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

READR

R=R-2001000

READR

t4t5t6t7

UPDATERREADR

800700R=R-300

UPDATER7.3.1SQLServer鎖的模式

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

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

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

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

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