IT420: Database Management and Organization

1. IT420: Database Management and Organization Managing Multi-user Databases 29 March 2006 Adina Crăiniceanu www.cs.usna.edu/~adina

2. Goals • Managing multi-user databases Kroenke, Database Processing

3. Reminders/Announcements • SAVE YOUR WORK TO X: DRIVE • Project 2 • Individual project • MySQL DBMS • Part 1 due next Tuesday • SQL queries • Available today • Due next Wednesday • Exam next week, Friday Kroenke, Database Processing

4. PHP Miscellaneous • int mysql_insert_id() • Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query • Return value: • The ID generated for an AUTO_INCREMENT column by the previous INSERT query on success • 0 if the previous query does not generate an AUTO_INCREMENT value • FALSE if no MySQL connection was established. Kroenke, Database Processing

5. PHP Miscellaneous • array mysql_fetch_assoc(resource res) • Returns associative array • array mysql_fetch_row(resource res) • Returns enumerated array • array mysql_fetch_array(resource res) • Return enumerated and associative array Kroenke, Database Processing

6. Example: Arrays $result = mysql_query(“Select Name From Users”); while ($row = mysql_fetch_assoc($result)){ echo $row[‘Name’]. ‘</br>’; } while ($row = mysql_fetch_row($result)){ echo $row[0]. ‘</br>’; } Kroenke, Database Processing

7. PHP – Delete Session Variables • unset($_SESSION[‘myvar’]) • Delete session variable myvar • $_SESSION = array(); • Delete ALL session variables Kroenke, Database Processing

8. Overview • Miscellaneous • Managing multi-user databases Kroenke, Database Processing

9. Database Administration • All large and small databases need database administration • NCLCA database (small DB) • Both “user” and “administrator” • Easy to change and manage • What about large, multi-user DB? • Much more difficult to manage • May require a staff to manage (if large enough) Kroenke, Database Processing

10. DBA Tasks • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Providing for database recovery • Managing the DBMS • Maintaining the data repository Kroenke, Database Processing

11. Managing Database Structure • Participate in database and application development • Assist in requirements stage and data model creation • Play an active role in database design and creation • Facilitate changes to database structure • Seek community-wide solutions • Assess impact on all users • Provide configuration control forum • Be prepared for problems after changes are made • Maintain documentation Kroenke, Database Processing

12. DBA Tasks • Managing database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Providing for database recovery • Managing the DBMS • Maintaining the data repository Kroenke, Database Processing

13. Concurrency Control • Concurrency control: ensure that one user’s work does not inappropriately influence another user’s work • No single concurrency control technique is ideal for all circumstances • Trade-offs need to be made between level of protection and throughput Kroenke, Database Processing

14. Atomic Transactions • A transaction, or logical unit of work (LUW), is a series of actions taken against the database that occurs as an atomic unit • Either all actions in a transaction occur - COMMIT • Or none of them do - ABORT Kroenke, Database Processing

15. Errors Introduced WithoutAtomic Transaction Kroenke, Database Processing

16. Make changes permanent Undo changes Errors Prevented WithAtomic Transaction Kroenke, Database Processing

17. Transactions Examples • Reserve an airline seat. Buy an airline ticket. • Withdraw money from an ATM. • Verify a credit card sale. • Order an item from an Internet retailer. Kroenke, Database Processing

18. Concurrent Transaction • Concurrent transactions: transactions that appear to users as they are being processed at the same time • In reality, CPU can execute only one instruction at a time • Transactions are interleaved • Concurrency problems • Lost updates • Inconsistent reads Kroenke, Database Processing

19. Concurrent Transaction Processing Kroenke, Database Processing

20. Lost-Update Problem Kroenke, Database Processing

21. DBMS’s View • User A: Read item 100 Set count 5 Write item 100 • User B: Read item 100 Set count 7 Write item 100 • T1: R(item) W(item) Commit • T2: R(item) W(item) Commit Kroenke, Database Processing

22. Inconsistent-Read Problem • Dirty reads – read uncommitted data • T1: R(A), W(A), R(B), W(B), Abort • T2: R(A), W(A), Commit • Unrepeatable reads • T1: R(A),R(A), W(A), Commit • T2: R(A), W(A), Commit Kroenke, Database Processing

23. Resource Locking • Resource locking prevents multiple applications from obtaining copies of the same record when the record is about to be changed Kroenke, Database Processing

24. Lock Terminology • Implicit locks are locks placed by the DBMS • Explicit locks are issued by the application program • Lock granularity refers to size of a locked resource • Rows, page, table, and database level • Large granularity is easy to manage but frequently causes conflicts • Types of lock • Exclusive lock (X)- prohibits other users from reading the locked resource • Shared lock (S) - allows other users to read the locked resource, but they cannot update it • When would you use exclusive vs. shared? Kroenke, Database Processing

25. Serializable Transactions • Serializable transactions refer to two transactions that run concurrently and generate results that are consistent with the results that would have occurred if they had run separately • Two-phased locking is one of the techniques used to achieve serializability Kroenke, Database Processing

26. Two-phased Locking • Two-phase locking • Transactions are allowed to obtain locks as necessary (growing phase) • Once the first lock is released (shrinking phase), no other lock can be obtained • Strict two-phase locking • All locks are released at the end of transaction (COMMIT or ROLLBACK) • More restrictive but easier to implement than two-phase locking Kroenke, Database Processing

27. Deadlock • Deadlock: two transactions are each waiting on a resource that the other transaction holds • Preventing deadlock • Allow users to issue all lock requests at one time • Require all application programs to lock resources in the same order • Breaking deadlock • Almost every DBMS has algorithms for detecting deadlock • When deadlock occurs, DBMS aborts one of the transactions and rollbacks partially completed work Kroenke, Database Processing

28. Deadlock Kroenke, Database Processing

29. Optimistic versus PessimisticLocking • Optimistic locking assumes that no transaction conflict will occur: • DBMS processes a transaction; checks whether conflict occurred: • If not, the transaction is finished • If so, the transaction is repeated until there is no conflict • Pessimistic locking assumes that conflict will occur: • Locks are issued before a transaction is processed, and then the locks are released • Optimistic locking is preferred for the Internet and for many intranet applications Kroenke, Database Processing

30. Optimistic Locking Kroenke, Database Processing

31. Pessimistic Locking Kroenke, Database Processing

32. Declaring Lock Characteristics • Most application programs do not explicitly declare locks due to its complication • Instead, they mark transaction boundaries and declare locking behavior they want the DBMS to use • Transaction boundary markers: BEGIN, COMMIT, and ROLLBACK TRANSACTION • Advantage • If the locking behavior needs to be changed, only the lock declaration need be changed, not the application program Kroenke, Database Processing

33. Marking Transaction Boundaries Kroenke, Database Processing

34. ACID Transactions • Acronym ACID transaction is one that is Atomic, Consistent, Isolated, and Durable • Atomic means either all or none of the database actions occur • Durable means database committed changes are permanent Kroenke, Database Processing

35. ACID Transactions • Consistency means either statement level or transaction level consistency • Statement level consistency: each statement independently processes rows consistently • Transaction level consistency: all rows impacted by either of the SQL statements are protected from changes during the entire transaction • With transaction level consistency, a transaction may not see its own changes Kroenke, Database Processing

36. Statement Level Consistency UPDATE CUSTOMER SET AreaCode = ‘425’ WHERE ZipCode = ‘21666’ Kroenke, Database Processing

37. Transaction Level Consistency Start transaction UPDATE CUSTOMER SET AreaCode = ‘425’ WHERE ZipCode = ‘21666’ ….other transaction work UPDATE CUSTOMER SET Discount = 0.25 WHERE AreaCode = ‘425’ End Transaction Kroenke, Database Processing

38. ACID Transactions • Isolation means application programmers are able to declare the type of isolation level and to have the DBMS manage locks so as to achieve that level of isolation • SQL-92 defines four transaction isolation levels: • Read uncommitted • Read committed • Repeatable read • Serializable Kroenke, Database Processing

39. Transaction Isolation Level Kroenke, Database Processing

40. Cursor Type • A cursor is a pointer into a set of records • It can be defined using SELECT statements • Four cursor types • Forward only: the application can only move forward through the recordset • Scrollable cursors can be scrolled forward and backward through the recordset • Static: processes a snapshot of the relation that was taken when the cursor was opened • Keyset: combines some features of static cursors with some features of dynamic cursors • Dynamic: a fully featured cursor • Choosing appropriate isolation levels and cursor types is critical to database design Kroenke, Database Processing

41. Cursor Summary Kroenke, Database Processing