Chapter 7

1. Chapter 7 Transaction Management and Concurrency Control

2. Study Objectives • Understand what a database transaction is and what its properties are • Identify how database transactions are managed • Learn what concurrency control is and what role it plays in maintaining the database’s integrity • Learn what locking methods are and how they work • Understand how database recovery management is used to maintain database integrity

3. Fundamental capability of a DBMS • Users don’t need to know how data is stored or manipulated • Users should be able to add, change, and delete records during updates • Users should be able to view and manipulate data during retrieval

4. What is a Transaction? • A set of steps completed by a DBMS to accomplish a single user task. • Must be either entirely completed or aborted • No intermediate states are acceptable

5. Transaction Properties • Atomicity • All transaction operations must be completed • Incomplete transactions must be aborted • Durability • Once a transaction is completed, its changes are made is permanent. • Serializability • Conducts transactions in serial order • Important in multi-user and distributed databases • Isolation • Transaction data cannot be used until its execution is completed

6. Transaction Management with SQL • Transaction support • COMMIT • ROLLBACK • User initiated transaction sequence must continue until: • COMMIT statement is reached • ROLLBACK statement is reached • End of a program reached • Program reaches abnormal termination

7. Concurrency Control • Coordinates simultaneous transaction execution in multiprocessing database • Ensure serializability of transactions in multiuser database environment • Solve potential problems in multiuser environments • Lost updates • Uncommitted data • Inconsistent retrievals

8. Best scenario of updating data Read from this slide to slide #11 Before update DBMS reads data from database into RAM for Ryan Ryan changes data in RAM DBMS updates database with Ryan’s change

9. Best scenario of updating data After Ryan’s update and before Elena’s DBMS reads database data into RAM for Elena Elena changes data in RAM DBMS updates database with Elena’s change

10. Concurrency Controlproblem Database before updates DBMS reads database data into RAM for Ryan DBMS reads database data into RAM for Elena Ryan changes data in RAM

11. Concurrency Controlproblem Slide #11 Elena updates data in RAM DBMS updates database with Ryan’s change DBMS updates database with Elena’s change; Ryan’s update is lost!

12. How to avoid previous slide problem?? • Simply use of batch processing concept • Update all data once a day • Considered as one phase locking • What is a problem with this method?

13. Locking Schemes • Two-Phase Locking • Locks are held until required updates completed • Deadlock • Occurs when two users hold more than one lock at a time • DBMS chooses method to break deadlock • One user becomes ‘victim’ • Locking on PC-Based DBMSs • Table or row locked, not both • Usually more limited than locking facilities on mainframe DBMSs

14. Two-Phase Locking Read from this slide to slide #16 Database before updates DBMS reads database data into RAM for Ryan and locks record Elena requests same record and request fails Ryan changes data in RAM; Elena’s request for same record again fails

15. Two-Phase Locking(con’t.) DBMS updates database with Ryan’s change; Elena’s request for same record again fails DBMS unlocks record; DBMS reads database data into RAM for Elena and locks record Elena changes data in RAM

16. Two-Phase Locking (con’t.) Slide #16 DBMS updates database with Elena’s change DBMS unlocks record

17. Two-Phase Locking Protocol • Growing phase • Shrinking phase • Governing rules • Two transactions cannot have conflicting locks • No unlock operation can precede a lock operation in the same transaction • No data are affected until all locks are obtained

18. Two-Phase Locking Protocol

19. Database-Level Locking Sequence

20. Table-Level Lock Example

21. Page-Level Lock Example

22. Row-Level Lock Example Lock row 2 request

23. Deadlocks • Occurs when two transactions wait for each other to unlock data • Called deadly embrace • Control techniques • Abort entire transactions.. • Abort most recent transaction • Abort transactions that require least changes

24. Deadlock

25. How Deadlock Conditions Created

26. Time Stamping Methods • Helps detect and resolve deadlocks • DBMS assigns a unique time when the update started • Last time field read • Last update time • DBMS executes conflicting operations in time stamp order • Eliminates processing time needed to apply and release locks

27. Recovery • Mechanism for recovering damaged database • The return of database to correct state is called recovery • Simplest recovery involves using backups • Other recovery methods • Journaling • Forward recovery • Backward recovery • PC-based

28. Forward Recovery What would be the answer of the question on page 239 if the catastrophe occurred after 9:00pm? Different than backward

29. Forward Recovery • Useful when a catastrophe destroys the database. • Since the Db is no longer current, the DBA executes a DBMS recovery program that applies the after committed transactions from the log to bring the database up to date. • Figure in next slide

30. Backward Recovery

31. Backward recovery • Useful when the DB has not actually been destroyed. • The DB is still valid (unlike forward recovery case) • Only transactions are either incorrect or in the midstream • Figure in next slide • Still possible to use “roll back – see SQL note for more details” as a recovery mechanism. • Roll back: go back to the previous state

32. Data Replication • Manage multiple copies of same data in multiple locations • Maintained for performance or other reasons • Ease of access and portability

33. Replicas From Master Database

34. DBMS Synchronizes Databases