ch12-TransactionManagement讲义.pptVIP

Levels of Consistency in SQL-92 Serializable — default Repeatable read — only committed records to be read, repeated reads of same record must return same value. However, a transaction may not be serializable – it may find some records inserted by a transaction but not find others. Read committed — only committed records can be read, but successive reads of record may return different (but committed) values. Read uncommitted — even uncommitted records may be read. Lower degrees of consistency useful for gathering approximate information about the database Transaction Definition in SQL Data manipulation language must include a construct for specifying the set of actions that comprise a transaction. In SQL, a transaction begins implicitly. A transaction in SQL ends by: Commit work commits current transaction and begins a new one. Rollback work causes current transaction to abort. Levels of consistency specified by SQL-92: Serializable — default Repeatable read Read committed Read uncommitted End of Chapter Schedule 7 Precedence Graph for (a) Schedule 1 and (b) Schedule 2 Illustration of Topological Sorting Precedence Graph fig. 15.21 Implementation of Isolation Schedules must be conflict or view serializable, and recoverable, for the sake of database consistency, and preferably cascadeless. A policy in which only one transaction can execute at a time generates serial schedules, but provides a poor degree of concurrency. Concurrency-control schemes tradeoff between the amount of concurrency they allow and the amount of overhead that they incur. Some schemes allow only conflict-serializable schedules to be generated, while others allow view-serializable schedules that are not conflict-serializable. Figure 15.6 Figure 15.12 * ?Silberschatz, Korth and Sudarshan 15.* Database System Concepts - 5th Edition, Sep 10, 2005. Database System Concepts, 5th Ed. ?Silberschatz, Korth and SudarshanSee for conditions on re-use Chapter 12: Transaction Management Chapter 12: Tr