1 / 137

Part5 Transaction Management and Index Chapter 10- Transaction Management

Part5 Transaction Management and Index Chapter 10- Transaction Management. Lu Wei College of Software and Microelectronics Northwestern Polytechnical University. Database Systems. Outline. Transaction Support Concurrency Control Programming with Transactions Database Recovery

deliz
Download Presentation

Part5 Transaction Management and Index Chapter 10- Transaction Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Part5 Transaction Management and IndexChapter 10- Transaction Management Lu Wei College of Software and Microelectronics Northwestern Polytechnical University Database Systems

  2. Outline • Transaction Support • Concurrency Control • Programming with Transactions • Database Recovery • Advanced Transaction Models Lu Wei

  3. Outline • Transaction Support • Concurrency Control • Programming with Transactions • Database Recovery • Advanced Transaction Models Lu Wei

  4. Transaction Support • Introduction • Transaction model Lu Wei

  5. Transaction Support • Introduction • Transaction model Lu Wei

  6. Introduction • Two core concepts in database system: • Data model—data abstraction • Transaction support—activity abatraction • Three characteristics of DBMS we discussed • Data abstraction • Reliability • Efficiency • Data abstraction is supported by data model and the other two characteristics of DBMSs are supported by transaction. Lu Wei

  7. Introduction • This module will introduce and discuss transaction management: • ACID properties -atomicity, consistency, isolation, and durability . • The means of actually providing a database system's transactions with these properties. Lu Wei

  8. Transaction Support • Introduction • Transaction model Lu Wei

  9. Transaction model • Definition of transaction • A transaction is the execution of a program segment that performs some function or task by accessing a shared database. • An action, or series of actions, carried out by a single user or application program, which reads or updates the contents of the database. • A transaction is a logical unit of work on the database. • Example: • deposit, withdraw, or transfer money; • reserve a room in a hotel; reserve a seat on a flight Lu Wei

  10. Transaction model • Effect of transaction • A transaction always takes the database from one consistent state to another, although we accept that consistency may be violated while the transaction is in progress. • Application program is series of transactions with non-database processing in between. Lu Wei

  11. Transaction model • Read and write operations of transactions 主存 变量Y 数据库 读 磁盘块 数据项x 缓冲区 写 Lu Wei

  12. Transaction model • State of a transaction • A transaction must end in either of two result Lu Wei

  13. Transaction model • ACID Properties of Transactions • As data models support data abstraction, transactions can be thought of similarly as supporting activity abstraction. • Transactions provide application programmers with a high-level execution interface that hides both the effects of concurrency among the different transactions, and the presence of failures. Lu Wei

  14. Transaction model • ACID Properties of Transactions • In this way, programmers are relieved from dealing with the complexity of concurrent programming and failures. They need only to focus on designing the business and application logic, and developing correct individual transactions. Lu Wei

  15. Transaction model • ACID Properties of Transactions • Transactions provide QoS (Quality of Service) guarantees of data consistency and database integrity—despite system failures and concurrent data sharing. • The above properties and guarantees with regard to transactions are commonly referred to as the ACID properties: Atomicity, Consistency, Isolation, and Durability. Lu Wei

  16. Transaction model • Atomicity(原子性) • Atomicity requires that either "all or none" of the transaction's operations be performed. • All the operations of a transaction are treated as a single, indivisible, atomic unit. Lu Wei

  17. Transaction model • Consistency(一致性) • Consistency requires that a transaction maintain the integrity constraints on the database. • Transactions are assumed to be correct and are treated as the unit of consistency. Lu Wei

  18. Transaction model • Isolation(隔离性) • Isolation requires that a transaction execute without any interference from other concurrent transactions. • Transactions are assumed to be independent. Lu Wei

  19. Transaction model • Durability(持久性) • Durability requires that all the changes made by a committed transaction become permanent in the database, surviving any subsequent failures. Lu Wei

  20. Transaction model • A DBMS supports the ACID properties of transactions by implementing three different sets of algorithms: • concurrency control protocols, ensures the isolation property; • recovery protocols, ensures atomicity and durability properties; Lu Wei

  21. Transaction model • A DBMS supports the ACID properties of transactions by implementing three different sets of algorithms: • triggering mechanisms, enforces the integrity constraints on a database. • Consistency is ensured by DBMS and application developers and DBMS provides triggering mechanisms only. Lu Wei

  22. Transaction model • DBMS Transaction Subsystem Lu Wei

  23. Outline • Transaction Support • Concurrency Control • Programming with Transactions • Database Recovery • Advanced Transaction Models Lu Wei

  24. Concurrency Control • Definition of concurrency control • The need for concurrency control • Schedule of transactions • Techniques for concurrency control • Locking methods • Timestamping methods • ANSI SQL2 Isolation Levels Lu Wei

  25. Definition of concurrency control • The Process of managing simultaneous operations on the database without having them interfere with one another. Lu Wei

  26. The need for concurrency control • A major objective in developing a database is to enable many users to access shared data concurrently. • Concurrent access is relatively easy if all users are only reading data, as there is no way that they can interfere with one another. • Although two transactions may be correct in themselves, interleaving of operations may produce an incorrect result. Lu Wei

  27. The need for concurrency control • The lost update problem Lu Wei

  28. The need for concurrency control • The uncommitted dependency (or dirty read) problem Lu Wei

  29. The need for concurrency control • Nonrepeatable read (fuzzy) problem Lu Wei

  30. The need for concurrency control • The inconsistent analysis problem--a situation of nonrepeatable read Lu Wei

  31. The need for concurrency control • Phantom read problem (幻读问题)-- a situation of nonrepeatable read Lu Wei

  32. The need for concurrency control • Try to resolve the problems • Every transaction is correct itself and serial execution can prevents such problems occurring. The interleaving of operation may produce problems. • The lost update problem can be avoided by preventing T1 from reading the balx until after T2’s update has been completed. Lu Wei

  33. The need for concurrency control • Try to resolve the problems • The dirty read problem can be avoided by preventing T3 from reading balx until after the decision has been made to either commit or abort T4’s effects. • The nonrepeatable read problem can be avoided by preventing transaction T5 from reading balx until after T6 has completed its upates. Lu Wei

  34. The need for concurrency control • One obvious solution is to allow only one transaction to execute at a time: one transaction is committed before the next transaction is allowed to begin. • However, the aim of a multi-user DBMS is also to maximize the degree of concurrency or parallelism in the system, so that transactions that can execute without interfering with one another can run in parallel. Lu Wei

  35. Schedule of transactions • The objective of a concurrency control protocol is to schedule transactions in such a way as to avoid any interference between them, and hence prevent the types of problem described in the previous section. Lu Wei

  36. Schedule of transactions • Schedule (调度) • A sequence of the operations by a set of concurrent transactions that preserves the order of the operations in each of the individual transactions. • Serial schedule (串行调度) • A schedule where the operations of each transaction are executed consecutively (连续) without any interleaved operations from other transactions. • Nonserial/concurrent schedule Lu Wei

  37. Schedule of transactions • Examples 设事务1为ABC，事务2为PQR (每个字母表示一个操作), 则 ABCPQR 和 PQRABC 都是串行调度； ABPCPQ 和 PAQBCR 都是并行调度； ACPBQR 和 PABCRQ 都不是调度。 思考：此例有多少个调度？其中有多少个串行调度？ Lu Wei

  38. Schedule of transactions • Examples 下边的例子说明不同调度的执行效果可能是不同的。 例：设初始值为a=20，b=30 Lu Wei

  39. Schedule of transactions T0 T1 read(a); a:=a-10; write(a); read(b); b:=b+10; write(b); read(a); tmp:=a/10; a:=a-tmp; write(a); read(b); b=:b+tmp; write(b); T0 T1 read(a); tmp:=a/10; a:=a-tmp; write(a); read(b); b=:b+tmp; write(b); read(a); a:=a-10; write(a); read(b); b:=b+10; write(b); T0 T1 read(a); a:=a-10; write(a); read(a); tmp:=a/10; a:=a-tmp; write(a); read(b); b:=b+10; write(b); read(b); b=:b+tmp; write(b); T0 T1 read(a); a:=a-10; read(a); tmp:=a/10; a:=a-tmp; write(a); read(b); write(a); read(b); b:=b+10; write(b); b=:b+tmp; write(b); 并行调度 串行调度     Lu Wei

  40. Schedule of transactions 运行结束时 A=9，B=41 A+B=50 运行结束时 A=8，B=42 A+B=50 运行结束时 A=9，B=41 A+B=50 与相同 运行结束时 A=10，B=32 A+B=42结果错误 原因:状态不一致 Lu Wei

  41. Schedule of transactions • Serial execution never leaves the database in an inconsistent state, so every serial execution is considered correct, although different results may be produced. • The objective of serializability is to find nonserial schedules that allow transactions to execute concurrently without interfering with one another, and thereby produce a database state that could be produced by a serial execution. Lu Wei

  42. Schedule of transactions • Serializable/correct schedule (可串行化/正确调度) • If a set of transactions executes concurrently, we say the (nonserial) schedule is serializable (or correct) if it produces the same results as some serial execution. • To prevent inconsistency from transactions interfering with one another, it is essential to guarantee serializability of concurrent transactions. Lu Wei

  43. Schedule of transactions • Definition of equivalent schedule (等价调度) • Two schedules are equivalent if and only if they produce the same result. • Types of equivalent schedule • Conflict equivalent (冲突等价) • View(state) equivalent (视图/状态等价) Lu Wei

  44. Schedule of transactions • Conflict equivalent • In serializability, the ordering of conflict operations is important. • Conflict operations on the same data item: • Read-write • Write-read • Write-write Lu Wei

  45. Schedule of transactions • Definition of conflict equivalent • 若调度S1对来自不同事务的相继两个非冲突操作交换次序变成调度S2，则称调度S1和S2是冲 突等价的。 • Definition of conflict serializable • 若调度S冲突等价于某串行调度，则称之为冲突可串行化调度。此时S可以通过交换某些相继非冲突操作变成串行调度。 Lu Wei

  46. Schedule of transactions a b c Lu Wei

  47. Schedule of transactions • Definition of view equivalent 若调度S1与调度S2满足下面条件， 则称它们是状态等价的： 对每个数据项Q， (1)若S1的事务T读Q的初值，则S2的事务T也读Q的初值。 (2)若S1的事务T读Q，则S2的事务T也读Q； 若S1中事务T1读到的Q值是事务T2产生的， 则S2中事务T1读到的Q值也是事务T2产生的。 (3)若S1中事务T执行最后的写Q操作， 则S2中事务T也执行最后的写Q操作。 Lu Wei

  48. Schedule of transactions • Definition of view serializable 若调度S视图等价于串行调度，则称之为视图可串行。 冲突可串行调度必然视图可串行调度，反之却未必正确。 冲突等价 视图等价 等价 Lu Wei

  49. Schedule of transactions • Examples T0 T1 1 read(a); 2 write(a); 3 read(b); 4 write(b); 5 read(b); 6 write(b). 7 read(a); 8 write(a). 非冲突可串行化调度 非视图可串行化调度 可串行化调度 Lu Wei

  50. T0 T1 read(a); a:=a-10; write(a); read(a); a:=a-1; write(a); read(b); b:=b+10; write(b); read(b); b=:b+1; write(b); 冲突可串行化调度 Lu Wei

More Related