Multi-User Databases

1. Multi-User Databases Chapter 9

2. Terminology • Data administration • Database administration • Database administrator or DBA • Facilitates the development and use of the database • Balances the conflicting goals of • Protecting the database • Maximizing its availability and benefit to users

3. Tasks of DBA

4. Managing Database Structure • Participating in initial database design • Participating in initial database implementation • Controlling and managing changes to the database • Developing procedures and policies by which users an register their needs for changes • Documenting the database structure

5. Summary of DBA’s responsibilities for managing database structure

6. Problems

7. Solution

8. Transactions • Transactions • Logical units of work • Atomic

9. Concurrency Control • Needed to ensure that one user’s work does not interfere with another • Concurrent transactions

10. Processing different data

11. Processing Same Data

12. Resource Locking

13. Locks • Implicit • Explict • Lock granularity • Lock types • Lock styles • Desired results

14. Deadlock – Deadly embrace

15. Optimistic Locking

16. Pessimistic locking

17. Transaction Boundaries

18. Some useful terminology • Transaction-level consistency • Dirty read • Nonrepeatble reads • Phantom reads • Isolation levels – see figure 9-10 • Scrollable cursor • Static cursor • Keyset • Dynamic cursor

19. Database Security Illustration

20. Database Recovery • Recovery via reprocessing • Recovery via rollback/rollforward • Necessity for a log