IT420: Database Management and Organization

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

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

3. Session Control • HTTP – no built-in way to maintain state between two transactions • Want: Track a user during a single session on a website – remember state • Show content personalized to user • Implement shopping carts Kroenke, Database Processing

4. PHP Session Control • Session ID – cryptographically random number • Generated for each session • Stored on client site • Cookie • URL • Session variables • Created by PHP script • Stored on the server site • If session id visible (cookie or URL), session variables can be accessed by all scripts Kroenke, Database Processing

5. Implementing Sessions in PHP • Start a session – session_start() • Register session variables • $_SESSION[‘myvar’] = ‘some value’ • Use session variables • session_start() • if ( isset($_SESSION[‘myvar’] ) ) { //OK code} • Deregister variables • unset($_SESSION[‘myvar’]) • Destroy session • session_destroy() Kroenke, Database Processing

6. Session Demo Kroenke, Database Processing

7. sesstart.php • <?php • //Create session • session_start(); • //Create session variable - Save user name • $_SESSION['login'] = $_POST[login]; • //Display session variable • echo 'Content of $_SESSION[\'login\'] is '. $_SESSION['login']."<BR>"; • echo '<br /><a href="ss1.php">page 1</a>'; • ?> Kroenke, Database Processing

8. ss1.php • <?php • // Use session variable • session_start(); • echo 'Content of $_SESSION[\'login\'] is '. $_SESSION['login']." <BR>"; • echo '<br /><a href="ss2.php">page 2</a>'; • ?> Kroenke, Database Processing

9. ss2.php – Use, Unset • <?php • // Use session variable • session_start(); • echo 'Content of $_SESSION[\'login\'] is '. $_SESSION['login']." <BR>"; • // Unset session variable- should not be visible anymore • unset($_SESSION['login']); • echo 'We unset the session varible</br>'; • echo '<br /><a href="ss3.php">page 3</a>'; • ?> Kroenke, Database Processing

10. ss3.php – Cannot Use Session Var • <?php • //Try use session variable • session_start(); • if (empty($_SESSION['login'])) • echo 'Empty <br>'; • else • echo 'Not Empty <br>'; • echo 'Content of $_SESSION[\'login\'] is '. $_SESSION['login']." <BR>"; • //Destroy session • session_destroy(); • ?> Kroenke, Database Processing

11. Class Exercise • Write PHP to implement db authentication • Login page: get user info • First page: check user • Next pages: display only if user logged in • Logout page Kroenke, Database Processing

12. Overview • Session control • Managing multi-user databases Kroenke, Database Processing

13. 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

14. 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

15. 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

16. 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

17. 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 or none of them do Kroenke, Database Processing

18. Errors Introduced WithoutAtomic Transaction Kroenke, Database Processing

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

20. 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

21. Concurrent Transaction Processing Kroenke, Database Processing

22. Lost-Update Problem Kroenke, Database Processing

23. Inconsistent-Read Problem • Dirty reads – read uncommitted data • Unrepeatable reads Kroenke, Database Processing

24. 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

25. 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 • An exclusive lock prohibits other users from reading the locked resource • A shared lock allows other users to read the locked resource, but they cannot update it Kroenke, Database Processing

26. Concurrent Processingwith Explicit Locks Kroenke, Database Processing

27. 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

28. 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

29. 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

30. Deadlock Kroenke, Database Processing

31. 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

32. Optimistic Locking Kroenke, Database Processing

33. Pessimistic Locking Kroenke, Database Processing

34. 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

35. Marking Transaction Boundaries Kroenke, Database Processing

36. 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

37. 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

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