关 键 词：

数据库系统概念第六版 数据库 系统 概念 第六 配套 课后 习题 答案

资源描述：

《数据库系统概念第六版》配套课后习题答案,数据库系统概念第六版,数据库,系统,概念,第六,配套,课后,习题,答案

内容简介：

CHAPTER24Advanced ApplicationDevelopmentPractice Exercises24.1Many applications need to generate sequence numbers for each trans-action.a.Ifasequencecounterislockedintwo-phasemanner,itcanbecomea concurrency bottleneck. Explain why this may be the case.b.Many database systems support built-in sequence counters thatare not locked in two-phase manner; when a transaction requestsa sequence number, the counter is locked, incremented and un-locked.i.Explain how such counters can improve concurrency.ii.Explain why there may be gaps in the sequence numbers be-longing to the final set of committed transactions.Answer: If two-phase locking is used on the counter, the counter mustremainlockedinexclusivemodeuntilthetransactionisdoneacquiringlocks. During that time, the counter is unavailable and no concurrenttransactions can be startedregardlessof whether they would have dataconflicts with the transaction holding the counter.If locking is done outside the scope of a two-phase locking protocol,the counter is locked only for the brief time it takes to increment thecounter. SInce there is no other operation besides increment performedonthecounter,thiscreatesnoproblemexceptwhenatransactionaborts.During an abort, the counter cannot be restored to its old value butinsteadshouldremainasitstands.Thismeansthatsomecountervaluesareunusedsincethosevalueswereassignedtoabortedtransactions.Tosee why we cannot restore the old counter value, assume transactionTahas counter value 100 and then Tbis given the next value, 101. IfTaaborts and the counter were restored to 100, the value 100 wouldbe given to some other transaction Tcand then 101 would be given to910Chapter 24Advanced Application Developmenta second transaction Td. Now we have two non-aborted transactionswith the same counter value.24.2Suppose you are given a relation r(a,b,c).a.Give an example of a situation under which the performance ofequality selection queries on attribute a can be greatly affected byhow r is clustered.b.Suppose you also had range selection queries on attribute b. Canyou cluster r in such a way that the equality selection queries onr.a and the range selection queries on r.b can both be answeredefficiently? Explain your answer.c.If clustering as above is not possible, suggest how both typesof queries can be executed efficiently by choosing appropriateindices.Answer:a.Ifr isnotclusteredona,tuplesofr withaparticulara-valuecouldbe scattered throughout the area in which r is stored. This wouldmake it necessary to scan all of r. Clustering on a combined withan index, would allow tuples of r with a particular a-value to beretrieved directly.b.This is possible only if there is a special relationship between aand b such as “if tuple t1has an a-value less than the a-value oftuple t2, then t1must have a b-value less than that of t2”. Asidefrom special cases, such a clustering is not possible since the sortorder of the tuples on a is different from the sort order on b.c.The physical order of the tuples cannot always be suited to bothqueries.Ifr isclusteredona andwehavea B+-treeindexonb,thefirstquerycanbeexecutedveryefficiently.Forthesecond,wecanuse the usual B+-tree range-query algorithm to obtain pointers toall the tuples in the result relation, then sort those those pointersso that any particular disk block is accessed only once.24.3Suppose that a database application does not appear to have a sin-gle bottleneck; that is,CPUand disk utilization are both high, and alldatabase queues are roughly balanced. Does that mean the applicationcannot be tuned further? Explain your answer.Answer:it may still be tunable. Example using a materialized viewmay reduce both CPU and disk utilization24.4Suppose a system runs three types of transactions. Transactions of typeA run at the rate of 50 per second, transactions of type B run at 100 persecond, and transactions of type C run at 200 per second. Suppose themix of transactions has 25 percent of type A, 25 percent of type B, and50 percent of type C.Practice Exercises11a.What istheaveragetransactionthroughput ofthesystem,assum-ing there is no interference between the transactions?b.What factors may result in interference between the transactionsof different types, leading to the calculated throughput beingincorrect?Answer:a.Let there be 100 transactions in the system. The given mix oftransaction types would have 25 transactions each of type AandB, and 50 transactions of type C. Thus the time taken to executetransactionsonlyoftype Ais0.5secondsandthatfortransactionsonly of type B or only of type C is 0.25 seconds. Given that thetransactions do not interfere, the total time taken to execute the100 transactions is 0.5 + 0.25 + 0.25 = 1 second. i.e, the averageoverall transaction throughput is 100 transactions per second.b.One of the most important causes of transaction interference islock contention. In the previous example, assume that transac-tions of type A and B are update transactions, and that those oftype C are queries. Due to the speed mismatch between the pro-cessor and the disk, it is possible that a transaction of type A isholding alock on a“hot” itemof dataand waiting for adiskwriteto complete, while another transaction (possibly of type B or C)is waiting for the lock to be released by A. In this scenario someCPU cycles are wasted. Hence, the observed throughput wouldbe lower than the calculated throughput.Conversely, if transactions of type Aand type B are disk bound,and those of type C are CPU bound, and there is no lock con-tention, observedthroughput may evenbe better than calculated.Lock contention can also lead to deadlocks, in which case sometransaction(s) will have to be aborted. Transaction aborts andrestarts (which may also be used by an optimistic concurrencycontrol scheme) contribute to the observed throughput beinglower than the calculated throughput.Factors such as the limits on the sizes of data-structures and thevarianceinthetimetakenbybook-keepingfunctions of thetrans-action manager may also cause a difference in the values of theobserved and calculated throughput.24.5List some benefits and drawbacks of an anticipatory standard com-pared to a reactionary standard.Answer: In the absence of an anticipatory standard it may be difficultto reconcile between the differences among products developed byvarious organizations. Thus it may be hard to formulate a reactiona