3204194
Description
Flashcards by nurhidayah.mazni, updated more than 1 year ago
|
Created by nurhidayah.mazni
over 9 years ago
|
|
| Question | Answer |
| Transaction Management | - Transaction is an action, or series of actions, carried out by user or application, which reads or updates contents of database. - Also a logical unit of work on the database. |
| Concept of Transactions | |
| Example Transaction | |
| Properties of Transactions | ** 4 basic properties (ACID) that must be maintained by a transaction, and they are: a) Atomicity - ‘All or nothing’ property. b) Consistency- Must transform database from one consistent state to another. c) Isolation - Partial effects of incomplete transactions should not be visible to other transactions. d) Durability - Effects of a committed transaction are permanent and must not be lost because of later failure. |
| Transaction Management – to ensure ACID | - Since database is shared by many users, there are possibilities that multiple transactions from different users are issued at the same time. |
| Concurrency Control | - Three examples of potential problems caused by concurrency: i) Lost update problem. ii) Uncommitted dependency problem. iii) Inconsistent analysis problem. |
| i) Lost Update Problem | ** Loss of (T2’s) update can be avoided by preventing (T1) from reading (bal x) until after update. |
| ii) Uncommitted Dependency Problem | - Problem can be avoided by preventing (T3) from reading (bal x) until after (T4) commits or aborts/rollbacks. |
| iii )Inconsistent Analysis Problem | ** Problem can be avoided by preventing (T6) from reading (bal x) and (bal z) until after (T5) completed updates or vice-versa. |
| How to handle the 3 problems | - Objective of a concurrency control protocol is to schedule transactions in such a way so as to avoid any interference. - Could run transactions serially, but this limits degree of concurrency or parallelism in system. |
| a) Serializability | All serial schedules ensure data consistency, even though the results of different order of transactions might not be identical. |
| Serializability (continued) | - In serializability, ordering of read/writes is important: (a) If two transactions only read a data item, they do not conflict and order is not important. T1: read x T2: read x (b) If two transactions either read or write completely separate data items, they do not conflict and order is not important. T1: read x T2: write y (c) If one transaction writes a data item and another reads or writes same data item, order of execution is important. T1: write x T2: write x OR T2: read x |
| Example of Conflict Serializable Schedules | |
| Example - Non-conflict serializable schedule | |
| b) Concurrency Control Techniques | - Two basic concurrency control techniques: ** Locking, ** Timestamping. - Both are conservative approaches: delay transactions in case they conflict with other transactions. -There are other optimistic methods which assume conflict is rare and only check for conflicts at commit. |
| First Technique - Locking | - Transaction uses locks to deny access to other transactions and so prevent incorrect updates. - Locking is the most widely used approach to ensure serializability. - Generally, a transaction must claim a shared (read) or exclusive (write) lock on a data item before read or write. - Lock prevents another transaction from modifying item or even reading it, in the case of a write lock. |
| Example - Incorrect Locking Schedule | |
| c) Two-Phase Locking (2PL) | - Two phases for transaction: i) Growing phase - acquires all locks but cannot release any locks. ii) Shrinking phase - releases locks but cannot acquire any new locks. In order to take care for the possibility of cascading rollbacks, leave release of all locks until end of transaction. |
| Preventing Lost Update Problem using 2PL | |
| Preventing Inconsistent Analysis Problem using 2PL | |
| Deadlock | ** A situation that may result when two (or more) transactions are each waiting for locks held by the other to be released. **Three general techniques for handling deadlock: i) Timeouts. ii) Deadlock prevention. iii) Deadlock detection and recovery. |
| i) Timeouts | a) Transaction that requests lock will only wait for a system-defined period of time. b) If lock has not been granted within this period, lock request times out. c) In this case, DBMS assumes transaction may be deadlocked, even though it may not be, and it aborts and automatically restarts the transaction. |
| ii) Deadlock Prevention | **Could order transactions using transaction timestamps: a) Wait-Die - only an older transaction can wait for younger one, otherwise transaction is aborted (dies) and restarted with same timestamp. b)Wound-Wait - only a younger transaction can wait for an older one. If older transaction requests lock held by younger one, younger one is aborted (wounded). |
| iii) Deadlock Detection and Recovery |
Want to create your own Flashcards for free with GoConqr? Learn more.