Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 12: Transaction management

Chia sẻ: Kiếp Này Bình Yên | Ngày: | Loại File: PPTX | Số trang:32

Thêm vào BST

Báo xấu

67
lượt xem 5
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 12 giới thiệu về transaction management. Các nội dung được trình bày trong chương gồm có: Transactions, transaction ACID properties, concurrency in a DBMS, atomicity oftransactions,... Mời các bạn cùng tham khảo.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Bài giảng Hệ quản trị cơ sở dữ liệu - Chương 12: Transaction management

Chương 12 Transaction Management 1
2 10.1 Transactions • Concurrent execution of user programs is essential for good DBMS performance. – Because disk accesses are frequent, and relatively slow, it is important to keep the cpu humming by working on several user programs concurrently. • A user’s program may carry out many operations on the data retrieved from the database, but the DBMS is only concerned about what data is read/written from/to the
3 10.2 Transaction ACID Properties • Atomic – Either all actions are carried out or none are. – Not worry about incomplete transaction. • Consistency – DBMS assumes that the consistency holds for each transaction. • Isolation – Transactions are isolated, or protected, from the effects of concurrently scheduling other
4 10.3 Concurrency in a DBMS • Users submit transactions, and can think of each transaction as executing by itself. – Concurrency is achieved by the DBMS, which interleaves actions (reads/writes of DB objects) of various transactions. – Each transaction must leave the database in a consistent state if the DB is consistent when the transaction begins. • DBMS will enforce some ICs, depending on the ICs declared in CREATE TABLE statements.
5 10.3 Concurrency in a DBMS • Beyond this, the DBMS does not really understand the semantics of the data. (e.g., it does not understand how the interest on a bank account is computed). • Issues: Effect of interleaving transactions, and crashes.
6 10.4 Atomicity of Transactions • A transaction is seen by DBMS as a series or list of actions. – Read/Write database object. – A transaction might commit after completing all its actions. – or it could abort (or be aborted by the DBMS) after executing some actions.
7 10.4 Atomicity of Transactions • Transactions are atomic: a user can think of a transaction as always executing all its actions in one step, or not executing any actions at all. – DBMS logs all actions so that it can undo the actions of aborted transactions.
8 Example • Consider two transactions (Transactions): T1: BEGIN A=A+100, B=B100 END T2: BEGIN A=1.06*A, B=1.06*B END • Intuitively, the first transaction is transferring $100 from B’s account to A’s account. The second is crediting both accounts with a 6% interest payment. • There is no guarantee that T1 will execute before T2 or vice-versa, if both are
9 Example • Consider a possible interleaving (schedule): T1: A=A+100, B=B100 T2: A=1.06*A, B=1.06*B T1: A=A+100, B=B100 – T2: is This A=1.06*A, B=1.06*B OK. But what about: T1: R(A), W(A), R(B), W(B) T2: R(A), W(A), R(B), W(B) – The DBMS’s view of the second schedule:
10 10.5 Scheduling Transactions • Schedule: an actual or potential execution sequence. – A list of actions from a set of transactions as seen by DBMS. – The order in which two actions of a transaction T appear in a schedule must be the same as the order in which they appear in T.
11 10.5 Scheduling Transactions • Classification: – Serial schedule: Schedule that does not interleave the actions of different transactions. – Equivalent schedules: For any database state, the effect (on the set of objects in the database) of executing the first schedule is identical to the effect of executing the second schedule. – Serializable schedule: A schedule that is equivalent to some serial execution of the transactions on any consistent database instance. • (Note: If each transaction preserves consistency,
12 10.6 Concurrent Execution of Transaction – E.g. Serializable schedule, Equal to the serial schedule T1;T2. T1: R(A), W(A), R(B), W(B), C T2: R(A), W(A), R(B), W(B),C – E.g. Serializable schedule, Equal to the serial schedule T1: T2;T1. W(A),R(B), W(B), C R(A), T2: R(A), W(A), R(B), W(B), C
13 10.6 Concurrent Execution of Transaction • Why concurrent execution? – CPU and I/O can work in parallel to increase system throughput. – Interleaved execution of a short transaction with a long transaction allows the short transaction to complete quickly, thus prevent stuck transaction or unpredicatable delay in response time.
14 10.7 Anomalies with Interleaved Execution • Reading Uncommitted Data(WR Conflicts, “dirty reads”): e.g. T1: A+100, B+100, T2: A*1.06, B*1.06 T1: R(A), W(A), R(B), W(B), Abort T2: R(A), W(A), C • Unrepeatable T1: R(A), Reads (RW Conflicts): E.g., T1: R(A), R(A), W(A), C T2: check ifR(A), W(A), C A >0, decrement, T2: R(A), decrement T1: W(A), W(B), C T2: W(A), W(B), C • Overwriting Uncommitted Data (WW Conflicts):
15 10.8 Schedules involving Aborted Transactions • Serializable schedule: – A schedule whose effect on any consistent database instance is guaranteed to be identical to that of some complete serial schedule over the set of committed transactions. – Aborted transactions being undone T1: R(A),W(A), Abort completely– T2: we have to do cascading abort. R(A),W(A),R(B),W(B), Commit
16 10.8 Schedules involving Aborted Transactions • Eg: Can we do cascading abort above? We have to abort changes made by T2, but T2 is already committed – we say the above schedule is an Unrecoverable schedule. • What we need is Recoverable schedule
17 10.9 Recoverable Schedules • Recoverable schedule: transactions commit only after all transactions whose changes they read commit. – In such a case, we can do cascading abort – Eg below: Note that T2 cannot commit before T1, therefore when T1 aborts, we can abort T2 as well. T1: R(A),W(A), Abort T2: R(A),W(A),R(B),W(B),
18 10.9 Recoverable Schedules • Another technique: A transaction reads changes only of committed transactions. Advantage of this approach is: the schedule is recoverable, and we will never have to cascade aborts.
19 10.10 Lock-based Concurrency Control • Only serializable, recoverable schedules are allowed. • No actions of committed transactions are lost while undoing aborted transactions. • Lock protocol: a set of rules to be followed by each transaction ( and enforced by DBMS) to ensure that, even though actions of several transactions is interleaved, the net effect is identical to executing all transactions in some serial
20 10.10 Lock-based Concurrency Control • Strict Two-phase Locking (Strict 2PL) Protocol: – Rule 1: Each Transaction must obtain a S (shared) lock on object before reading, and an X (exclusive) lock on object before writing. – Rule 2: All locks held by a transaction are released when the transaction completes. • (Non-strict) 2PL Variant: Release locks anytime, but cannot acquire locks after releasing any lock.