浙江大学数据库系统概念PPT第十五章 对应原版教材第五版PPT学习教案

1、会计学1浙江大学数据库系统概念浙江大学数据库系统概念PPT第十五章第十五章 对应原版教材第五版对应原版教材第五版第1页/共31页nIsolation. Although multiple transactions may execute concurrently, each transaction must be unaware of other concurrently executing transactions. Intermediate transaction results must be hidden from other concurrently executed transac

2、tions. nDurability. After a transaction completes successfully, the changes it has made to the database persist, even if there are system failures. To preserve integrity of data, the database system must ensure:第2页/共31页第3页/共31页database, it will see an inconsistent database (the sum A + B will be les

3、s than it should be).nCan be ensured trivially by running transactions serially,that is one after the other. nHowever, executing multiple transactions concurrently has significant benefits, as we will see.第4页/共31页transactions need not wait behind long ones.nConcurrency control schemes mechanisms to

4、achieve isolation, i.e., to control the interaction among the concurrent transactions in order to prevent them from destroying the consistency of the database第5页/共31页T1T21Read(x)2Read(x)3X = x+14Write(x)X = x*25Write(x)T1T21Write(T)2Read(T)3rollback第6页/共31页T1T21Read(x)2Write(x)3Read(x)X ?第7页/共31页nEq

5、uivalent schedulenSerializable Schedule第8页/共31页第9页/共31页In both Schedule 1 and 3, the sum A + B is preserved.第10页/共31页第11页/共31页第12页/共31页ilj are consecutive in a schedule and they do not conflict, their results would remain the same even if they had been interchanged in the schedule.第13页/共31页not confl

6、ict serializable:T3T4read(Q)write(Q)write(Q)We are unable to swap instructions in the above schedule to obtain either the serial schedule , or the serial schedule .第14页/共31页第15页/共31页transaction Tj(if any), then transaction Ti must in schedule Salso read the value of Q that was produced by transactio

7、n Tj .3.For each data item Q, the transaction (if any) that performs the final write(Q) operation in schedule S must perform the finalwrite(Q) operation in schedule S.As can be seen, view equivalence is also based purely on readsand writes alone.第16页/共31页nEvery view serializable schedule that is not

8、 conflict serializable has blind writes (write(Q) without having performed a read(Q) operation).第17页/共31页nDetermining such equivalence requires analysis of operations other than read and write.第18页/共31页nWe may label the arc by the item that was accessed.nExample 1xy第19页/共31页read(W)read(Y)write(Y)wri

9、te(Z)read(U)read(Y)write(Y)read(Z)write(Z)read(U)write(U)第20页/共31页T3T4T1T2T5第21页/共31页第22页/共31页concurrency they allow and the amount of overhead that they incur.nTests for serializability help understand why a concurrency control protocol is correct. 第23页/共31页nIf T8should abort, T9 would have read (a

10、nd possibly shown to the user) an inconsistent database state. Hence database must ensure that schedules are recoverable.Need to address the effect of transaction failures on concurrently running transactions.第24页/共31页第25页/共31页第26页/共31页ntransaction and begins a new one.nRollback work causes current transaction to abort.nLevels of consistency specified by SQL-92:nSerializable defaultnRepeatable readnRead committednRead uncommitted第27页/共31页ncommitted records can be read, but successive reads of record may return different (but committed) values.nRead uncommitted even uncommitted recor