北邮-数据库系统原理(英文)-15-15

PART5TRANSACTIONMANAGEMENTC,Pascalprograms词法/语法/语义分析中间代码生成(中间)代码优化目标代码生成程序编译/编译器程序执行/OS进程管理&进程调度并发控制死锁处理query扫描和语法/语义分析查询优化(优化后)查询执行计划查询代码生成Queryprocessing/DBMSprocess/thread事务处理/DBMSChapter.13,14Chapter.15,16,17transaction关系代数表达式&查询树查询计划执行的代码事务管理目标程序代码(§14)&事务调度并发控制死锁处理恢复技术(§13)(§15)(§16)(§17)Fig.13.0.1DatabaseSystemConcepts-Chapter15Transactions-3DBS中，从DBMS的角度，用户对DB的访问体现为DBS中1个或多个事务的执行事务是DBS中应用程序/数据库应用系统的基本逻辑单位，也是DBMS管理DBS运行的基本单位,§15.1多用户DBS中，多个用户对DB的并发访问体现为DBS中多个数据库事务的并发执行事务是动态的，具有一定生命周期事务具有多种状态，事务的执行体现为各状态间的转换过程,§15.2,Fig.15.1IntroductiontoPart5DatabaseSystemConcepts-Chapter15Transactions-4在DBS中，事务执行时，特别是当多个事务对共享数据进行并发访问时，为维护DBS系统中数据的正确性(integrities)，事务必须满足一定的约束条件，表现为事务的4个基本特征(ACID),§15.1原子性(atomicity)，一致性(consistency)，独立性/隔离性(isolation)，永久性/持续性/操作结果永久保持性(durability)DBMS中的recovery-managementcomponent负责保障事务的原子性。§15.3,§17事务设计者/DBS应用程序编程者负责保障事务的一致性(P611)IntroductiontoPart5(cont.)DatabaseSystemConcepts-Chapter15Transactions-5DBMS中的concurrency-controlcomponent负责保障事务的独立性。§16DBMS中的recovery-managementcomponent负责保障事务的永久性。§15.3,§17当DBS中存在多个并发事务时，DBMS通过事务调度对各事务进行并发控制(§15.4)，以保证并发事务的运行结果正确性DBMS中的concurrency-controlcomponent依据事务调度可串行性（Serializability)的基本原理，对事务进行并发控制和调度,§15.5,15.6,15.7IntroductiontoPart5(cont.)DatabaseSystemConcepts-Chapter15Transactions-6具体实现技术包括基于锁的并发控制技术(§16.1)、基于时间戳的并发控制技术(§16.2)、基于验证的并发控制技术(§16.3)、多版本控制(§16.5)、多粒度控制技术(§16.4)此外，DBMS的concurrency-controlcomponent还需要对多个并发执行的事务进行死锁处理(§16.6)IntroductiontoPart5(cont.)Chapter15TRANSACTIONSDatabaseSystemConcepts-Chapter15Transactions-8DefinitionandcompositionReadandwriteACIDproperties§15.1TransactionConceptDatabaseSystemConcepts-Chapter15Transactions-9ConceptaunitofDBSapplicationprogramexecutingthataccessesandpossiblyupdatesvariousdataitemsinDBAnotherdefinitionatransactionisameansbywhichanapplicationprogrammercanpackagetogetherasequenceofDBoperationssothattheDBScanprovideanumberofguarantees,knownasACIDpropertiesofatransaction15.1.1DefinitionandCompositionDatabaseSystemConcepts-Chapter15Transactions-10Componentsofthetransactionacollectionofoperationsthatformasinglelogicunitofapplicationworksoperations:readorwriteonDB,orotheroperationswritteninquerylanguages(e.g.SQL)orprogramminglanguages(e.g.C,C++,Java)begin-transactionop1;op2;.opn;end-transaction(e.g.commitorabort/rollback)DefinitionandComposition(cont.)DatabaseSystemConcepts-Chapter15Transactions-11Conceptually,atransactioncanbedefinedbyreadandwriteoperations,whichareindependentofDBSquerylanguages(e.g.SQL)andDBMS(e.g.SQLServer)E.g.1.Account-transferT1:transfer$50fromaccountAtoaccountBT1:begin-transactionread(account_A);account_A:=account_A-50;write(account_A);read(account_B);account_B:=account_B+50;write(account_B);end-transactionDefinitionandComposition(cont.)begin-transactionread(amount_A);amount_A:=amount_A-50;ifamount_A0thenthenbeginprint(‘insufficientfunds’)rollbackT2endelsebeginwrite(account_A);read(amount_B);amount_B:=amount_B+50;write(amount_B);commitT2endabort/rollbackT2:撤销之前对loan_A的修改,即恢复loan_A的原值，并立即结束T2，不再执行其后的各操作Fig.15.0.1Account_transferT2withrollbackDatabaseSystemConcepts-Chapter15Transactions-13Inviewofimplementation,atransactioncanbewritteninquerylanguages(e.g.T-SQL)andprogramminglanguages(C,C++,Java)E.g.2.ImplementationofAccount_transferinT-SQLFig.15.0.2DefinitionandComposition(cont.)DECLARE@transfer_namevarchar(10)/*事务变量定义*/SET@transfer_name=‘I-transfer-from-A-to-B’/*事务命名*/BEGINTRANSACTION@transfer_name/*事务开始*/USEACCOUNT/*打开数据库ACCOUNT*/GO/*将上述批SQL语句提交SQLServer*/UPDATEaccount_A/*修改A帐户*/SETbalance=balance–50WHEREbranch_name=‘Brooklyn’UPDATEaccount_B/*修改B帐户*/SETbalance=balance+50WHEREbranch_name=‘Brooklyn’GOCOMMITTRANSACTION@transfer_name/*事务提交*/GOFig.15.0.2Account_transferT3inT-SQLDatabaseSystemConcepts-Chapter15Transactions-15AtransactioncorrespondstoaSQLstatement,orasequenceofstatements,oranapplicationprogramrelatedtoDBaccessAnapplicationprogrammaycorrespondtoseveraltransactionsTransactionsvsDBSApplicationProgramsDatabaseSystemConcepts-Chapter15Transactions-16Eachdataaccessintransactions,suchasselect,UpdateinSQL,orAPIinODBC,istranslated/decomposedbyDBMSintooneormorereadandwriteoperationsRefertoFig.15.0.3DBMSexecutesthesereadandwriteoperationstofulfilldataaccessintransactionsForeachtransaction,DBMSallocatesalocalbufferinmainmemoryastheworkingareaforthistransactionThedatabasepermanentlyresidesondisk,butsomeportionofitistemporarilyresidinginthediskbufferinmainmemory15.1.2ReadandWriteOperationsFig.15.0.3readandwriteoperationsBXDBfileondisklocalbufferforTiread(x)write(y)DBMSTransactiondataaccessesondataitemxandyissuedbyTi,e.g.select,insert,delete,update,…xi,yiBYBZBWBXBYdiskbufferinput(X)output(Y)/reflectBZ……BUBVBQBR…逻辑读写物理读写DatabaseSystemConcepts-Chapter15Transactions-18read(X)transferthedataitemXfromDBfilesondiskorthedisk/systembuffertothelocalbufferofthetransactionthatexecutethereadoperationwrite(X)conceptually,transferthedataitemXfromthelocalbufferofthetransactionthatexecutethewriteoperationbacktotheDBfilesondiskInrealDBS,thewriteoperatio