数据库-事务管理的详细讲解

1Transaction Management2ObjectivesuFunction and importance of transactions.uProperties of transactions.uConcurrency Control Meaning of serializability.How locking can ensure serializability.Deadlock and how it can be resolved.Granularity of locking.3ObjectivesuRecovery Control Some causes of database failure.Purpose of transaction log file.How to recover following database failure.4Transaction SupportTransactionAction,or series of actions,carried out by user or application,which accesses or changes contents of database.uLogical unit of work on the database.uApplication program is series of transactions with non-database processing in between.uTransforms database from one consistent state to another,although consistency may be violated during transaction.5Example Transaction6Transaction SupportuCan have one of two outcomes:Success-transaction commits and database reaches a new consistent state.Failure-transaction aborts,and database must be restored to consistent state before it started.Such a transaction is rolled back or undone.uCommitted transaction cannot be aborted.uAborted transaction that is rolled back can be restarted later.7State Transition Diagram for Transaction 8Properties of Transactions uFour basic(ACID)properties of a transaction are:Atomicity All or nothing property.Consistency Must transform database from one consistent state to another.Isolation Partial effects of incomplete transactions should not be visible to other transactions.DurabilityEffects of a committed transaction are permanent and must not be lost because of later failure.9DBMS Transaction Subsystem10Concurrency Control Process of managing simultaneous operations on the database without having them interfere with one another.uPrevents interference when two or more users are accessing database simultaneously and at least one is updating data.uAlthough two transactions may be correct in themselves,interleaving of operations may produce an incorrect result.11Need for Concurrency ControluThree examples of potential problems caused by concurrency:Lost update problem.Uncommitted dependency problem.Inconsistent analysis problem.12Lost Update ProblemuSuccessfully completed update is overridden by another user.uT1 withdrawing 10 from an account with balx,initially 100.uT2 depositing 100 into same account.uSerially,final balance would be 190.13Lost Update ProblemuLoss of T2s update avoided by preventing T1 from reading balx until after update.14Uncommitted Dependency ProblemuOccurs when one transaction can see intermediate results of another transaction before it has committed.uT4 updates balx to 200 but it aborts,so balx should be back at original value of 100.uT3 has read new value of balx(200)and uses value as basis of 10 reduction,giving a new balance of 190,instead of 90.15Uncommitted Dependency ProblemuProblem avoided by preventing T3 from reading balx until after T4 commits or aborts.16Inconsistent Analysis ProblemuOccurs when transaction reads several values but second transaction updates some of them during execution of first.uSometimes referred to as dirty read or unrepeatable read.uT6 is totaling balances of account x(100),account y(50),and account z(25).uMeantime,T5 has transferred 10 from balx to balz,so T6 now has wrong result(10 too high).17Inconsistent Analysis ProblemuProblem avoided by preventing T6 from reading balx and balz until after T5 completed updates.18SerializabilityScheduleSequence of reads/writes by set of concurrent transactions.Serial ScheduleSchedule where operations of each transaction are executed consecutively without any interleaved operations from other transactions.uNo guarantee that results of all serial executions of a given set of transactions will be identical.19Nonserial ScheduleuSchedule where operations from set of concurrent transactions are interleaved.uObjective of serializability is to find nonserial schedules that allow transactions to execute concurrently without interfering with one another.uIn other words,want to find nonserial schedules that are equivalent to some serial schedule.Such a schedule is called serializable.20Concurrency Control TechniquesuTwo basic concurrency control techniques:Locking,Timestamping.21LockingTransaction uses locks to deny access to other transactions and so prevent incorrect updates.uGenerally,a transaction must claim a shared(read)or exclusive(write)lock on a data item before read or write.uLock prevents another transaction from modifying item or even reading it,in the case of a write lock.22Locking-Basic RulesuIf transaction has shared lock on item,can read but not update item.uIf transaction has exclusive lock on item,can both read and update item.uReads cannot conflict,so more than one transaction can hold shared locks simultaneously on same item.uExclusive lock gives transaction exclusive access to that item.23Two-Phase Locking(2PL)Transaction follows 2PL protocol if all locking operations precede first unlock operation in the transaction.uTwo phases for transaction:Growing phase-acquires all locks but cannot release any locks.Shrinking phase-releases locks but cannot acquire any new locks.24Preventing Lost Update Problem using 2PL25Preventing Uncommitted Dependency Problem using 2PL26Preventing Inconsistent Analysis Problem using 2PL27DeadlockAn impasse that may result when two(or more)transactions are each waiting for locks held by the other to be released.28Database Recovery Process of restoring database to a correct state in the event of a failure.29Recovery FacilitiesuDBMS should provide following facilities to assist with recovery:Backup mechanism,which makes periodic backup copies of database.Logging facilities,which keep track of current state of transactions and database changes.Checkpoint facility,which enables updates to database in progress to be made permanent.Recovery manager,which allows DBMS to restore database to consistent state following a failure.30Log Fileu Transaction records contain:Transaction identifier.Type of log record,(transaction start,insert,update,delete,abort,commit).Identifier of data item affected by database action(insert,delete,and update operations).Before-image of data item.After-image of data item.Log management information.31Sample Log File32Log FileuLog file may be duplexed or triplexed.uLog file sometimes split into two separate random-access files.uPotential bottleneck;critical in determining overall performance.