Transaction Management Torsten Grust Chapter 11 Transaction Management Concurrent and Consistent Data Access ACID Properties Anomalies Architecture and Implementation of Database Systems The Scheduler Summer 2016 Serializability Query Scheduling Locking Two-Phase Locking Multi-Version Concurrency Control Torsten Grust Wilhelm-Schickard-Institut für Informatik Universität Tübingen 1
Transaction The “Hello World” of Transaction Management Management Torsten Grust • My bank issued me a debit card to access my account. • Every once in a while, I use it at an ATM to withdraw some money from my account, causing the ATM to perform a ACID Properties transaction in the bank’s database. Anomalies The Scheduler Serializability Query Scheduling Example (ATM transaction) Locking 1 bal ← read_bal ( acct _ no ) ; Two-Phase Locking Multi-Version 2 bal ← bal − 100 � ; Concurrency Control 3 write_bal ( acct _ no , bal ) ; • My account is properly updated to reflect the new balance. 2
Transaction Concurrent Access Management Torsten Grust The problem is: My wife owns such a card for the very same account, too. ⇒ We might end up using our cards at different ATMs at the same time , i.e. , concurrently . ACID Properties Anomalies Example (Concurrent ATM transactions) The Scheduler Serializability me my wife DB state Query Scheduling Locking bal ← read ( acct ) ; 1200 Two-Phase Locking bal ← read ( acct ) ; 1200 Multi-Version Concurrency Control bal ← bal − 100 ; 1200 bal ← bal − 200 ; 1200 write ( acct , bal ) ; 1100 write ( acct , bal ) ; 1000 • The first update was lost during this execution. Lucky me! 3
Transaction If the Plug is Pulled . . . Management Torsten Grust • This time, I want to transfer money over to another account. Example (Money transfer transaction) ACID Properties // Subtract money from source (checking) account Anomalies 1 chk _ bal ← read_bal ( chk _ acct _ no ) ; The Scheduler 2 chk _ bal ← chk _ bal − 500 � ; Serializability 3 write_bal ( chk _ acct _ no , chk _ bal ) ; Query Scheduling Locking // Credit money to the target (savings) account Two-Phase Locking 4 sav _ bal ← read_bal ( sav _ acct _ no ) ; Multi-Version Concurrency Control 5 sav _ bal ← sav _ bal + 500 � ; � 6 7 write_bal ( sav _ acct _ no , sav _ bal ) ; • Before the transaction gets to step 7 , its execution is interrupted/cancelled (power outage, disk failure, software bug, . . . ). My money is lost � . 4
Transaction ACID Properties Management Torsten Grust To prevent these (and many other) effects from happening, a DBMS guarantees the following transaction properties : ACID Properties A Atomicity Either all or none of the updates in a database Anomalies transaction are applied. The Scheduler C Consistency Every transaction brings the database from one Serializability consistent state to another. (While the Query Scheduling Locking transaction executes, the database state may be Two-Phase Locking temporarily inconsistent.) Multi-Version I Concurrency Control Isolation A transaction must not see any effect from other transactions that run in parallel. D Durability The effects of a successful transaction remain persistent and may not be undone for system reasons. 5
Transaction Concurrency Control Management Torsten Grust Applications SQL Interface Web Forms SQL Commands Executor Parser ACID Properties Anomalies Operator Evaluator Optimizer The Scheduler Serializability Query Scheduling Files and Access Methods Transaction Locking Manager Recovery Two-Phase Locking Buffer Manager Manager Multi-Version Lock Concurrency Control Manager Disk Space Manager DBMS data files, indices, . . . Database 6
Transaction Anomalies: Lost Update Management Torsten Grust ACID Properties • We already saw an example of the lost update anomaly on Anomalies slide 3: The Scheduler Serializability Query Scheduling The effects of one transaction are lost due to an Locking uncontrolled overwrite performed by the second Two-Phase Locking transaction. Multi-Version Concurrency Control 7
Transaction Anomalies: Inconsistent Read Management Torsten Grust Reconsider the money transfer example (slide 4), expressed in SQL syntax: Example Transaction 1 Transaction 2 ACID Properties Anomalies 1 UPDATE Accounts The Scheduler SET balance = balance - 500 2 Serializability WHERE customer = 1904 3 Query Scheduling AND account_type = ’C’; 4 Locking 1 SELECT SUM(balance) Two-Phase Locking FROM Accounts 2 Multi-Version WHERE customer = 1904; 3 Concurrency Control 5 UPDATE Accounts SET balance = balance + 500 6 WHERE customer = 1904 7 AND account_type = ’S’; 8 • Transaction 2 sees a temporary, inconsistent database state. 8
Transaction Anomalies: Dirty Read Management Torsten Grust On a different day, once more my wife and me end up in front of ATMs at roughly the same time. This time, my transaction is cancelled ( aborted ): Example ACID Properties me my wife DB state Anomalies The Scheduler bal ← read ( acct ) ; 1200 Serializability bal ← bal − 100 ; 1200 Query Scheduling write ( acct , bal ) ; 1100 Locking bal ← read ( acct ) ; 1100 Two-Phase Locking Multi-Version bal ← bal − 200 ; 1100 Concurrency Control abort ; 1200 write ( acct , bal ) ; 900 • My wife’s transaction has already read the modified account balance before my transaction was rolled back ( i.e. , its effects are undone). 9
Transaction Concurrent Execution Management Torsten Grust • The scheduler decides the execution order of concurrent database accesses. The transaction scheduler ACID Properties Anomalies Client 1 Client 2 Client 3 The Scheduler 2 3 3 Serializability 2 2 1 Query Scheduling 1 1 Locking Scheduler Two-Phase Locking 1 Multi-Version Concurrency Control 2 1 1 Access and Storage Layer 10
Transaction Database Objects and Accesses Management Torsten Grust • We now assume a slightly simplified model of database access: 1 A database consists of a number of named objects . In ACID Properties a given database state, each object has a value . Anomalies 2 Transactions access an object o using the two The Scheduler operations read o and write o . Serializability Query Scheduling • In a relational DBMS we have that Locking Two-Phase Locking object ≡ attribute . Multi-Version Concurrency Control This defines the granularity of our discussion. Other possible granularities: object ≡ row , object ≡ table . 11
Transaction Transactions Management Torsten Grust Database transaction A database transaction T is a (strictly ordered) sequence of steps . Each step is a pair of an access operation applied to an object . • Transaction T = � s 1 , . . . , s n � ACID Properties Anomalies • Step s i = ( a i , e i ) The Scheduler • Access operation a i ∈ { r ( ead ) , w ( rite ) } Serializability Query Scheduling The length of a transaction T is its number of steps | T | = n . Locking Two-Phase Locking Multi-Version Concurrency Control We could write the money transfer transaction as 3 T = � ( read , Checking ) , ( write , Checking ) , 2 ( read , Saving ) , ( write , Saving ) � 1 or, more concisely, T = � r ( C ) , w ( C ) , r ( S ) , w ( S ) � . 12
Transaction Schedules Management Torsten Grust Schedule A schedule S for a given set of transactions T = { T 1 , . . . , T n } is an arbitrary sequence of execution steps 2 S ( k ) = ( T j , a i , e i ) k = 1 . . . m , 1 ACID Properties 1 Anomalies such that The Scheduler Serializability 1 S contains all steps of all transactions and nothing else and Query Scheduling 2 the order among steps in each transaction T j is preserved: Locking Two-Phase Locking ( a p , e p ) < ( a q , e q ) in T j ⇒ ( T j , a p , e p ) < ( T j , a q , e q ) in S Multi-Version Concurrency Control (read “ < ” as: occurs before ). We sometimes write S = � r 1 ( B ) , r 2 ( B ) , w 1 ( B ) , w 2 ( B ) � to abbreviate S ( 1 ) = ( T 1 , read , B ) S ( 3 ) = ( T 1 , write , B ) S ( 2 ) = ( T 2 , read , B ) S ( 4 ) = ( T 2 , write , B ) 13
Transaction Serial Execution Management Torsten Grust Serial execution One particular schedule is serial execution . • A schedule S is serial iff, for each contained transaction T j , all its steps are adjacent (no interleaving of transactions and thus no concurrency ). ACID Properties Anomalies Briefly: The Scheduler Serializability S = T π 1 , T π 2 , . . . , T π n (for some permutation π of 1 , . . . , n ) Query Scheduling Locking Two-Phase Locking 2 Consider again the ATM example from slide 3. Multi-Version 2 • S = � r 1 ( B ) , r 2 ( B ) , w 1 ( B ) , w 2 ( B ) � Concurrency Control 1 • This is a schedule, but it is not serial. 1 If my wife had gone to the bank one hour later (initiating transaction T 2 ), the schedule probably would have been serial. 2 • S = � r 1 ( B ) , w 1 ( B ) , r 2 ( B ) , w 2 ( B ) � 1 2 1 14
Recommend
More recommend
