Transcript DB administration, Transactions

IT420: 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
 MySQL 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 MySQL 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’]. ‘</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
 T1: R(item)
W(item)
 T2:
R(item)
Commit
W(item) Commit
Kroenke, Database Processing
21
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
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
AreaCode = ‘425’
WHERE ZipCode = ‘21666’
Kroenke, Database Processing
36
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
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