SLIDE 14.1 - Transactions Show slide p 627 What is a transaction? SLIDE 14.2 - Transactions Show slide p 627 Technical: * Collection of operations that form a single logical unit of work The entire set of operations execute in their entirety or not at all * What if one of those operations itself fails? * What happens when there are multiple concurrent transactions from multiple different users? * Result can't be affected by other updates being applied to the database at the same time * How can you run all of those transactions at the same time and STILL guarentee the integrity of each one of those transactions? * What happens when the environment fails? DB engine, OS, or hardware, disk drive, power How do you guarentee integrity of transactions then? SLIDE 14.3 - Transaction Concept Show slide p 629 READ 1 * In this chapter we will be looking at a very simplified database language Look at Disk to memory and Memory to disk No inserts or deletes Just arithmetic Single data value Bank application Reads and writes of existing values Read() reads if off of disk and brings it into main memory Write() writes if from main memory to disk Assume it writes it to disk immediately READ 2 READ 3 * Who else is reading and writing to A and B ? SLIDE 14.4 - Example of Fund Transfer - Atomicity and Durability Show slide p 630 * Same transactions as before READ 2 Suppose A had $1000, B had $2000 If it fails after the write of A but before the write of B How much is in each account ? A = $950, B=$2000 $50 is LOST DB is not consistent with the real state of the world * When is it ok for the DB to be in an inconsistent state ? During the transactions Should NOT be visible to others * How does the DB protect against failure in transactions ? Write old values out to a log If transaction fails, old values are read back in As thought the transactions never even started READ 3 * No system failure can result in loss of data once the transaction has completed Even if the system fails Data in main memory may be lost Assume that data on disk is never lost * How does the database do this? 1 Write the results to disk BEFORE the transactions completes 2 Results of the transactions can be reconstructed based on the information in the log * Atomicity and Durability are the responsiblity of the DB engine SLIDE 14.5 - Example of Fund Transfer - Consistency Show slide p 630 * Same transactions as before READ 2 * Without this, money would be created or destroyed Assuming that Atomicity and Durability has been met by the DB Responsiblity of the PROGRAMMER ! * Defining Integrity constraints on the tables and columns help with this READ 3 SLIDE 14.6 - Example of Fund Transfer - Isolation Show slide p 631 * DB may be TEMPORARILY inconsistent DURING the transaction READ 1 READ 2 READ 3 * Isolation property of a transaction ensures that the concurrent execution of transactions results in a system state that is equivalent to a state that could have been obatined had these transactions executed one at a time in some order * Responsibility of the DB concurrency-control system SLIDE 14.7 - ACID Properties Show slide p 631 READ ALL SLIDE 14.8 - Transaction State Show slide p 632-3 * What are the charactertics of volatile storage? information does not survive system crash fast direct access Kinds: main memory & cache * What are the charactertics of nonvolatile storage? survives system crash slower Kinds: disk, tape, flash, etc * What about stable storage? NEVER lost (grain of salt) How do we get this? Redundant copies kept on different media with independent failure modes Must keep redundant copies conisistent * What does it mean to roll back a transaction? Restore DB to the state it had BEFORE the transaction started READ 1 * Transaction id is written with both old value and new value of any updates ONLY after the DB itself is modified READ REST SLIDE 14.9 - Transaction State Show slide p 634 * A transaction has terminated if it has either committed or aborted SLIDE 14.10 - Concurrent Executions Show slide p 635 READ ALL * How do you control concurrent transactions in particular, multiple writes to the DB from multiple transactions and keep the DB in a consistent state ? SLIDE 14.11 - Schedules Show slide p 635 READ ALL SLIDE 14.12 - Schedule 1 Show slide p 638 READ ALL * How is consistency defined for this database ? Total amount in A+B is preserved * How much money is in each account BEFORE we execute ? A = $1000, B = $2000 How much is A+B ? $3000 * How much money is in each account after both have finished ? A = $855, B = $2145 How much is A+B ? $3000 t0 t1 t2 A 1000 950 855 B 2000 2050 2145 SLIDE 14.13 - Schedule 2 Show slide p 638 * How much money is in each account after both have finished ? A = $855, B = $2145 How much is A+B ? $3000 t0 t1 t2 A 1000 900 850 B 2000 2100 2150 SLIDE 14.14 - Schedule 3 Show slide p 639 READ * After the execution takes place, we arrive at the same state as T1 and T2 executed serially SLIDE 14.15 - Schedule 4 Show slide p 639 * What value of A gets read in T1 & T2 ? 1000 * What value of A gets written in T2 ? 900 * What value of A gets written in T1 ? 950 * What value of B gets read in T1 & T2 ? 2000 * What value of B gets read in T1 ? 2050 * What value of B gets written in T2 ? 2100 * What is A+B ? 950 + 2100 = 3050 SLIDE 14.16 - Serializability Show slide p 641 READ 1 * Consistency is the responsibility of the programmer READ 2 * If each individual transaction is consistent, then a bunch of transactions run one after another will also be consistent READ 3 * Serializable - Any schedule that gives the same results as a schedule that could have occurred without concurrent execution * Gives the same results as if the transactions had been executed one at a time SLIDE 14.17 - Simplified view of transactions Show slide p 641 READ ALL SLIDE 14.18 - Conflicting Instructions Show slide p 642 READ 1 * If 2 consecutive instructions from 2 different transactions both access the same data element Q and at least ONE of these instructions WRITEs to Q There is a CONFLICT READ 2 * The results of the transaction will changed depending on which of those instructions is executed first * If the 2 instructions don't conflict, you can change their order SLIDE 14.19 - Conflict Serializability Show slide p 643 READ 1 * If I and J are consecutive instructions in 2 different transactions in a schedule S IF I and J don't conflict We can change their order to produce a new schedule Can ONLY swap instructions IF they don't conflict ! ! ! Both schedules produce the same result They are CONFLICT EQUIVALENT READ 2 SLIDE 14.20 - Conflict Serializability Show slide p 643 READ 1 Chap_14_Slide_20.xlsx * Swap T1 read(B) with T2 write(a) * Swap T1 read(B) with T2 read(a) * Swap T1 write(B) with T2 write(a) * Swap T1 write(B) with T2 read(a) * Schedule S is CONFLICT SERIALIZABLE IF it is conflict equivalent to a serial schedule SLIDE 14.21 - Conflict Serializability Show slide p 643 * Can't SWAP because ALL instructions conflict Only NON-conflicting instructions may be swapped SLIDE 14.22 - View Serializability Show slide p ? READ ALL SLIDE 14.23 - View Serializability Show slide p ? SLIDE 14.24 - Other Notions of Serializability Show slide p ? Serial schedule t0 t1 t2 A 1000 950 1990 B 2000 2050 1010 UnSerialized schedule t0 t1 t2 final A 1000 950 960 960 B 2000 2040 1990 2040 A+B = 3000 SLIDE 14.25 - Testing for Serializability Show slide p 644 READ 1 READ 2 * Vertices are the bubbles * Edges are the arcs Arrows imply order of transactions in time SLIDE 14.26 - Testing for Conflict Serializability Show slide p 645 READ 1 * There are no cycles in the graph Schedule is conflict serializable SKIP 2 READ 3 * (a) has 2 acceptable orderings * Ignore T5 SLIDE 14.26 - Testing for View Serializability Show slide p 646 SKIP ALL * Not used in practice due to its high degree of computational complexity SLIDE 14.28 - Recoverable Schedules Show slide p 647 * What happens to other concurrent transactions when ONE of them fails? READ 1 * Any transaction that reads data that was written previously by another transaction that fails also needs to be aborted READ 2 * Notice there is NO commit in T8 We call this a partial schedule because there is no commit or abort for T8 * Notice T9 reads a value that was written by T8 T9 commits after the read(A) T8 is still in the Active state Could still fail T9 cannot be aborted - even if T8 fails Impossible to recover correctly from the failure of T8 READ 3 * A recoverable schedule is one where, for each pair of transactions where one is dependent on the other the dependent transaction does not commit until the first one commits SLIDE 14.29 - Cascading Rollbacks Show slide p 647 READ ALL SLIDE 14.30 - Cascadeless Schedules Show slide p 648 SKIP 1 * If T2 reads data written by T1 T1 must commit that data first to prevent cascading roll backs READ 2 READ 3 SLIDE 14.31 - Concurrency Control Show slide p 648 * Programmers don't need to worry about concurrency that is the DB engine's job READ 1 * If every transaction has the property that it maintains database consistency when executed alone then serializability ensures that concurrent executions maintain consistency * If any transaction fails, ONLY that transaction needs to be rolled back READ 2 * Ensuring serializability may allow too little concurrency sometimes "dirty" data is ok Errors introduces by this may be small enough that it doesn't matter SKIP 3 READ 4 SLIDE 14.32 - Concurrency Control Show slide p 648 READ ALL SLIDE 14.33 - Concurrency Control vs. Serializability Tests Show slide p 648 SLIDE 14.34 - Weak Levels of Consistency Show slide p 649 SLIDE 14.35 - Levels of Consistency in SQL SQL-92 Show slide p 649 READ 1-4 * Changing isolation level must be done as the first statement of a transaction * Auto commit must be turned off It is on by default * Oracle supports Snapshot isolation (non-ANSI) Serializable sees only data that was committed before the TRANSACTION began. Read committed This is the DEFAULT transaction isolation level. Each query executed by a transaction sees only data that was committed before the QUERY (not the transaction) began. If the transaction contains DML that requires row locks held by another transaction, then the DML statement waits until the row locks are released. Read only sees only data that was committed before the TRANSACTION began. Does not allow INSERT, UPDATE, and DELETE statements. NO read uncommitted mode SLIDE 14.36 - Transaction Definition in SQL Show slide p 653 READ ALL * Why would you want to abort your own transaction ? * PROJECT If you didn't turn off AUTO COMMIT - you will lose serious points