IT 420 Database Management and Organization Managing Multi-user

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

Goals § Managing multi-user databases Kroenke, Database Processing 2

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

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 My. SQL connection was established. Kroenke, Database Processing 4

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 5

$Example: Arrays $result = mysql_query(“Select Name From Users”); while ($row = mysql_fetch_assoc($result)){ echo $row[‘Name’].$

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 6

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

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

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 9

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 10

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 11

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 12

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 13

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 14

Errors Introduced Without Atomic Transaction Kroenke, Database Processing 15

Errors Prevented With Atomic Transaction Make changes permanent Undo changes Kroenke, Database Processing 16

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 17

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 18

Concurrent Transaction Processing Kroenke, Database Processing 19

Lost-Update Problem Kroenke, Database Processing 20

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 § T 1: R(item) W(item) § T 2: R(item) Commit W(item) Commit Kroenke, Database Processing 21

Inconsistent-Read Problem § Dirty reads – read uncommitted data § T 1: R(A), W(A), R(B), W(B), Abort § T 2: R(A), W(A), Commit § Unrepeatable reads § T 1: R(A), W(A), Commit § T 2: R(A), W(A), Commit Kroenke, Database Processing 22

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 23

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 24

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 25

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 twophase locking Kroenke, Database Processing 26

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 27

Deadlock Kroenke, Database Processing 28

Optimistic versus Pessimistic Locking § 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 29

Optimistic Locking Kroenke, Database Processing 30

Pessimistic Locking Kroenke, Database Processing 31

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 32

Marking Transaction Boundaries Kroenke, Database Processing 33

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 34

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 35

Statement Level Consistency UPDATE CUSTOMER SET Area. Code = ‘ 425’ WHERE Zip. Code = ‘ 21666’ Kroenke, Database Processing 36

Transaction Level Consistency Start transaction UPDATE CUSTOMER SET Area. Code = ‘ 425’ WHERE Zip. Code = ‘ 21666’ …. other transaction work UPDATE CUSTOMER SET Discount = 0. 25 WHERE Area. Code = ‘ 425’ End Transaction Kroenke, Database Processing 37

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 38

Transaction Isolation Level Kroenke, Database Processing 39

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 40

Cursor Summary Kroenke, Database Processing 41