Transcription
Database Principles:Fundamentals of Design,Implementation, andManagementTenth EditionChapter 13Managing Transactionsand Concurrency
Objectives In this chapter, you will learn: What a database transaction is and what itsproperties are What concurrency control is and what role it playsin maintaining the database’s integrity What locking methods are and how they work How database recovery management is used tomaintain database integrity
What is a Transaction?Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
What is a Transaction? (continued) Any action that reads from and/or writes to adatabase may consist of: Simple SELECT statement to generate list of tablecontents Series of related UPDATE statements to change valuesof attributes in various tables Series of INSERT statements to add rows to one ormore tables Combination of SELECT, UPDATE, and INSERTstatements
What is a Transaction? (continued) Transaction is logical unit of work that must beeither entirely completed or aborted Successful transaction changes database fromone consistent state to another One in which all data integrity constraints aresatisfied Most real-world database transactions areformed by two or more database requests Equivalent of a single SQL statement in anapplication program or transaction
Evaluating Transaction Results Not all transactions update database SQL code represents a transaction becausedatabase was accessed Improper or incomplete transactions can havedevastating effect on database integrity Some DBMSs provide means by which user candefine enforceable constraints Other integrity rules are enforced automatically bythe DBMS
Evaluating Transaction Results(continued)Figure 9.2Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Transaction Properties Atomicity Requires that all operations (SQL requests) of atransaction be completed Consistency Indicates the permanence of database’s consistentstate
Transaction Properties (continued) Isolation Data used during execution of a transactioncannot be used by second transaction until firstone is completed Durability Ensures that once transaction changes are done(committed), they cannot be undone or lost, even inthe event of a system failure
Transaction Management with SQL ANSI has defined standards that govern SQLdatabase transactions Transaction support is provided by two SQLstatements: COMMIT and ROLLBACK
Transaction Management with SQL(continued) ANSI standards require that, when a transactionsequence is initiated by a user or an applicationprogram, it must continue through allsucceeding SQL statements until one of fourevents occurs COMMIT statement is reachedROLLBACK statement is reachedEnd of program is reachedProgram is abnormally terminated
The Transaction Log Transaction log stores: A record for the beginning of transaction For each transaction component (SQL statement): Type of operation being performed (update, delete, insert)Names of objects affected by transaction“Before” and “after” values for updated fieldsPointers to previous and next transaction log entries for thesame transaction Ending (COMMIT) of the transaction
The Transaction Log (continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Concurrency Control Coordination of simultaneous transactionexecution in a multiprocessing database system Objective is to ensure serializability oftransactions in a multiuser databaseenvironment
Concurrency Control (continued) Simultaneous execution of transactions over ashared database can create several data integrityand consistency problems Lost updates Uncommitted data Inconsistent retrievals
Lost UpdatesDatabase Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Lost Updates (continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Uncommitted DataDatabase Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Uncommitted Data (continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Inconsistent RetrievalsDatabase Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Inconsistent Retrievals(continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Inconsistent Retrievals(continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Concurrency Controlwith Locking Methods Lock Guarantees exclusive use of a data item to acurrent transaction Required to prevent another transaction fromreading inconsistent data Lock manager Responsible for assigning and policing the locksused by transactions
Lock Granularity Indicates level of lock use Locking can take place at following levels: DatabaseTablePageRowField (attribute)
Lock Granularity (continued) Database-level lock Entire database is locked Table-level lock Entire table is locked Page-level lock Entire diskpage is locked
Lock Granularity (continued) Row-level lock Allows concurrent transactions to access differentrows of same table, even if rows are located onsame page Field-level lock Allows concurrent transactions to access samerow, as long as they require use of different fields(attributes) within that row
Lock Granularity (continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Lock Granularity (continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Lock Granularity (continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Lock Granularity (continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Lock Types Binary lock Has only two states: locked (1) or unlocked (0) Exclusive lock Access is specifically reserved for transaction thatlocked object Must be used when potential for conflict exists Shared lock Concurrent transactions are granted Read access onbasis of a common lock
Lock Types (continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Deadlocks Condition that occurs when two transactionswait for each other to unlock data Possible only if one of the transactions wantsto obtain an exclusive lock on a data item No deadlock condition can exist among sharedlocks
Deadlocks (continued) Control through: Prevention Detection Avoidance
Database Recovery Management Database recovery Restores database from given state, usuallyinconsistent, to previously consistent state Based on atomic transaction property All portions of transaction must be treated as singlelogical unit of work, so all operations must be applied andcompleted to produce consistent database If transaction operation cannot be completed,transaction must be aborted, and any changes todatabase must be rolled back (undone)
Transaction Recovery Makes use of deferred-write and write-throughtechniques Deferred write Transaction operations do not immediately updatephysical database Only transaction log is updated Database is physically updated only aftertransaction reaches its commit point usingtransaction log information
Transaction Recovery (continued) Write-through Database is immediately updated by transactionoperations during transaction’s execution, evenbefore transaction reaches its commit point
Transaction Recovery(continued)Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel
Summary Transaction Sequence of database operations that accessdatabase Represents real-world events Must be logical unit of work No portion of transaction can exist by itself Takes database from one consistent state toanother One in which all data integrity constraints aresatisfied
Summary (continued) Transactions have five main properties:atomicity, consistency, isolation, durability, andserializability SQL provides support for transactions throughthe use of two statements: COMMIT andROLLBACK SQL transactions are formed by several SQLstatements or database requests
Summary (continued) Transaction log keeps track of all transactionsthat modify database Concurrency control coordinates simultaneousexecution of transactions Lock guarantees unique access to a data item bytransaction Two types of locks can be used in databasesystems: binary locks and shared/exclusive locks
Summary (continued) When two or more transactions wait indefinitelyfor each other to release lock, they are indeadlock, or deadly embrace Three deadlock control techniques: prevention,detection, and avoidance Concurrency control with time stampingmethods assigns unique time stamp to eachtransaction and schedules execution ofconflicting transactions in time stamp order
Summary (continued) Concurrency control with optimistic methodsassumes that the majority of databasetransactions do not conflict and thattransactions are executed concurrently, usingprivate copies of the data Database recovery restores database from givenstate to previous consistent state
Database Recovery Management Database recovery Restores database from given state, usually inconsistent, to previously consistent state Based on atomic transaction property All portions of transaction must be treated as single logical unit of work, so all operations must be applied and
