Cours 7 Transactions Motivating Questions How does transaction

Cours 7: Transactions • Motivating Questions – How does transaction work ? – Why is it important for DBMS ? – How to use it ? C. Claramunt 1

Transactions, Recovery, and Concurrency Control • Transactions are the units to be considered in both recovery and concurrency control • Recovery techniques are needed to ensure that transactions complete successfully despite system failures • Logs are used to allow recovery, and checkpoints to identify what transactions need recovery • Concurrency control is needed to prevent concurrent transactions from interfering with each other • Locking and timestamping are the major techniques for concurrency control C. Claramunt 2

Properties of Transactions • Atomicity - a transaction is an atomic unit of processing; it is either performed in its entirety or not performed at all • Consistency preservation - a transaction must take the database from one consistent state to another • Isolation - changes made by a transaction should not be seen by other transactions until the initial transaction is committed • Durability - changes that are made and committed by a transaction must never be lost C. Claramunt 3

Atomicity • Either an operation completes fully or the operation does not happen at all • Low-level atomic operations are built into hardware • High-level atomic operations are called transactions • Transaction manager ensures that all transactions either – complete (“committed transaction”) – have no effect on the database (“aborted transaction”) C. Claramunt 4

System Model Memory Disk A read (A) A B read (A) A write (B) B write (B) A B local buffer T 1 C. Claramunt local buffer T 2 5

Assumptions • Each data item can be read and written only once by one single transaction. If can be modified many times in the local buffer • Values are written onto the global buffer space in the same sequential order that the write instructions are issued • If the transaction reads a value from the database, and this value was previously modified, it always gets the latest value • A data item can be a file, relation, record, physical page, etc. , determined by the designer C. Claramunt 6

Transaction Example • Transfer $50 from account A to account B • Transaction structure 1 2 3 4 5 6 read (A) A <- A -50 write (A) read (B) B <-B +50 write (B) • If initial values are A= 180 and B = 100, then after the execution A = 130 and B = 150 • If the system crashes between steps 3 and 6, then the database is in an inconsistent state C. Claramunt 7

Transaction Model • A transaction must see a consistent database • During transaction execution the database may be inconsistent • When the transaction is committed (completed successfully), the database must be consistent • A transaction which does not complete successfully is termed “aborted” • An aborted transaction must be “rolled back”, meaning the database is returned to its state prior to the transaction C. Claramunt 8

Problems in Ensuring Atomicity • Transaction failure: transaction cannot complete due to user abort or internal error condition • System errors: the database system must terminate an active transaction due to an error condition (e. g. , deadlock) • System crash: a power failure or other hardware failure causes the system to crash • Disk failure: a head crash or similar failure destroys all or part of disk storage C. Claramunt 9

Write-ahead Log (Incremental Log with Immediate Updates) • A log file is kept on stable storage • Each transaction Ti when it starts, registers itself on the log, by writing <Ti, starts> • Whenever Ti executes write (X), the record <Ti, X, old-value, new-value> is written sequentially on the log, and then the write (X) is executed • When Ti reaches its last statement, the record <Ti commits> is added to the log • If X is modified, then its corresponding log record is always first written on the log and then written on the database • Before Ti is committed, all its corresponding log records must be in stable storage C. Claramunt 10

Example Transactions T 1: read (A) A <-A + 50 read (B) B <- B + 100 write (B) read (C) C <- 2 C write (C) A <- A + B + C write (A) T 2: read (A) A <-A + 10 write (A) read (D) D <- D - 10 read (E) read (B) E <- E + B write (E) D <- D + E write (D) Initial values: A=100, B=300, C=5, D=60, E=80 C. Claramunt 11

Log Records 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. C. Claramunt <T 1 starts> <T 1, B, 300, 400> <T 1, C, 5, 10> <T 1, A, 100, 560> <T 1 commits> <T 2 starts> <T 2, A, 560, 570> <T 2, E, 80, 480> <T 2, D, 60, 530> <T 2 commits> Database Values I. III. IV. V. VI. B C A A E D 400 10 560 570 480 530 12

Possible Order of Writes Log 1 2 3 4 5 Time 6 7 8 9 10 C. Claramunt Database I II IV V VI 13

Consequences of a Crash • After a crash, the log is examined. • Various actions are taken depending on the last instruction written on the log • Example: Last instruction (i) Action i=0 nothing 1 <i<4 undo (T 1) 5 <i<9 redo (T 2) undo (T 2) i>10 redo (T 1) redo (T 2) C. Claramunt 14

Algorithm • Redo all transactions for which the log has both start and commit operations • Undo all transactions for which the log has a start operation but no commit operation • Remarks: – in a multitasking system more than one transaction may need to be undone – if a system crashes during the recovery stage, the new recovery must still give correct results – in this algorithm, a large number of transactions need to be redone, since we don’t not know how far behind the database updates are C. Claramunt 15

Incremental Log with Deferred Updates • Each transaction Ti when it starts, registers itself on the log, by writing <Ti, starts> • Whenever Ti executes write (X), the record <Ti, X, newvalue> is written sequentially on the log • When Ti reaches its last statement, the record <Ti commits> is added to the log • Before Ti is committed, all its corresponding log records must be in stable storage • Use the log records to perform the actual updates to the database after the commit • When system crash, only need to redo transactions for which the log has both start and commit operations C. Claramunt 16

Checkpointing • During execution, in addition to the activities of the previous method, periodically perform checkpointing – force log buffers on log – force database buffers on database – force “checkpoint record” on log • During recovery – undo all transactions that have not committed – redo all transactions that have committed after a checkpoint C. Claramunt 17

Checkpointing Example Tc time Tf T 1 T 2 T 3 T 4 checkpoint system failure • T 1 okay • T 2 and T 3 redone • T 4 undone C. Claramunt 18

Transactions: Summary • The concept of transaction is fundamental to database safeguards against and recovery from failure • A transaction is a sequence of instructions which alters the database so as to represent a single change in the world, and/or which retrieve information about a single snapshot of the world so as to support some task C. Claramunt 19

Report Exercise • The director of a financial organization has been alarmed concerning the vulnerability of relational databases. He has asked you to write a report advising her as to the risks to data as a consequence of systems failures and the vulnerability of data to unauthorized access. In your report you should identify the potential risks and steps that can be taken to minimize them (you can assume a RDBMS of your choice). C. Claramunt 20