Database Administration Transaction Processing Locking and Deadlocks Backups and Recovery Performanc

1
Database AdministrationTransaction
ProcessingLocking and DeadlocksBackups and
RecoveryPerformance Issues

• HIIB62
• Databases and Data Modelling
• Contributions by Arthur Adamopoulos, Vince Bruno,
  Bill Davey, Hossein S. Zadeh, Ian Searle and Ian
  Storey

2
Data Administration

• Manage the data needs of the entire organization
  so all data is available.
• Database Planning
• Data Analysis, Design and Implementation
• Data Maintenance and Protection
• Education and Training
• Support to end users
• High level management role

3
Database Administration

• Manage physical database servers.
• More of a technical, hands on role.
• Installation and configuration of DBMS
• Data Analysis, Design and Implementation
• Performance Tuning
• Managing Security and Policies
• Managing backups and recovery

4
Changing Roles

• In larger companies, these two roles may be
  performed by distinct people or groups.
• In smaller organisations or departments, one
  person will often perform both roles.
• DBA roles are becoming specialized
• Product specific (eg Oracle DBA)
• Overlap with developers (server programming)
• Database types (eg data warehouses)
• Specific App Software (eg SAP or PeopleSoft)

5
Transaction Processing - no locking
STAFF
Read 100
Read 100
6
Transaction Processing - no locking
change to Brunswick
STAFF
Read 100
Read 100
7
Transaction Processing - no locking
change to Brunswick
change to 9552345
STAFF
Read 100
Read 100
8
Transaction Processing - no locking
change to Brunswick
change to 9552345
STAFF
100
Smith John Brunswick 552345
Read 100
Read 100
Write 100
9
LOST UPDATE PROBLEM
change to Brunswick
change to 9552345
STAFF
100
Smith John Fitzroy 9552345
Read 100
Read 100
Write 100
Write 100
10
Locking

• How do we solve this problem?
• When a user/process wants to use a resource, LOCK
  IT.
• No other user/process may change it until it is
  UNLOCKED.
• if used LOCKING in previous example order of
  execution would change, lets us see what
  happens.....

11
Transaction Processing - with locking
change to Brunswick
STAFF
100
Smith John Brunswick 552345
Read 100
Write 100
12
Transaction Processing - with locking
User A
User B
Staff id 100 Surname Smith Given
John Suburb Brunswick Phone 552345
Staff id 100 Surname Smith Given
John Suburb Brunswick Phone 552345
change to 9552345
STAFF
100
Smith John Brunswick 9552345
Read 100
Write 100
13
Level Of Locking

• 1. Database - backup
• 2. Table - batch work (updates)
• 3. Block/Page - most common
• 4. Record - 1 record, most commonly required
• 5. Field - allows 1 field/attribute to be locked,
  not very practical.

14
Lock Types

• Shared Locks
• other users/processes can read but not update
  i.e. allowing querying
• Exclusive Locks
• other users can do NOTHING.

15
Transaction

• Is a single business operation.
• A whole unit of work MUST be applied or NONE will
  be applied to database.
• eg. Transfer 100 from AccA to AccB.
• Read AccA
• Subtract 100 from AccA
• Write AccA
• Read AccB
• Add 100 to AccB
• Write AccB

16
Deadlocks

• Deadlocks arise from more than one process
  holding a resource and requesting other resources
  which are in turn held by other processes, for
  example

17
Deadlock Resolution

• Pessimistic Locking Scheme
• every process must lock ALL resources required
  first.
• if cannot, unlock everything and have another go
  later
• performance slow due to overhead of locking
  everything.

18
Deadlock Resolution

• Deadlock Watchdog
• Let processes do what they want/please
• program keeping an eye on locks
• If DEADLOCK recognized then kill off processes
  until resolved.

19
Deadlock Resolution

• Optimistic Locking Scheme (versioning)
• read and use records without concern
• before writing at end of transaction
• re-read record(s)
• if not changed since 1st read the WRITE Ok
• if has changed then abort whole transaction!
• efficient - performance high if over 90, no
  change then QUICK!

20
Database Recovery - Backup

• Full Backup
• everything is backed up on some sort of magnetic
  media.
• Partial Backup
• backup changes made since last FULL Backup was
  made.

21
Database Recovery - Backup

• Recommended backup philosophy
• FULL Backup each week
• PARTIAL backups each day
• ROTATING the media used
• OFFSITE media storage

22
Database Recovery - Logs

• Journals/Transaction Logs
• Log enough information about a change to allow it
  to be applied again, if required.
• before and after images of record changed and/or
• details about each transaction
• log start and finish of transaction.

100 102 104 102 100 101 100
23
Database Recovery - Checkpoints

• Allows all transactions to finish, at a
  particular time.
• Write a checkpoint, as a log entry. This gives us
  a point at which no transaction are unfinished.
• Useful for recovery, if a problem occurs with the
  database.

100 102 104 102 100 101 100 Checkpoint 105
24
Database Recovery - Rollback

• If database intact
• can undo changes until checkpoint because OK
  point.
• undo unfinished transaction back to a safe point
  (usually checkpoint)

25
Database Recovery - RollForward

• database retrieved from backup
• re-apply transactions up to latest checkpoint.

DATABASE (without changes)
ROLLFORWARD
After Image
26
Data Partitioning

• Split table
• Horizontally
• Vertically
• Characteristics
• Infrequent access
• Large size
• Move to slower / cheaper storage

High speed hard disk
Low cost optical disk
Active customers
Customer Name Address Phone 2234 Inouye 9978
Kahlea Dr. 555-555-2222 5532 Jones 887 Elm
St. 666-777-3333 0087 Hardaway 112 West
2000 888-222-1111 0109 Pippen 873 Lake
Shore 333-111-2235
27
Vertical Partition

• In one table, some columns are large and do not
  need to be accessed as often.
• Store primary data on high speed disk.
• Store other data on optical disk.
• DBMS retrieves both automatically as needed.
• Products table example.
• Basic inventory data.
• Detailed technical specifications and images.

High speed hard disk
Low cost optical disk
Item Name QOH Description TechnicalSpecifications
875 Bolt 268 1/4 x 10 Hardened, meets standards
... 937 Injector 104 Fuel injector Designed 1995,
specs . . .
28
Disk Striping and RAID

• RAID
• Instead of one massive drive, use many smaller
  drives.
• Split table to store parts on different drives
  (striping).
• Duplicate pieces on different drive for backup.
• Drives can simultaneously retrieve portions of
  the data.

CustID Name Phone 115 Jones 555-555-1111 225 Inez
666-666-2222 333 Shigeta 777-777-1357 938 Smith 88
8-888-2225
29
RAID 0 (data striping)

• Two or more hard disks are grouped together so
  that data is spread across all of them equally
• Almost linearly increases speed e.g. 4 hard
  disks as RAID 0 work almost 4 times as fast as a
  single hard disk
• No fault tolerance

ABCDEF
Disk controller
ACE
BDF
30
RAID 1 (data mirroring)
ABCDEF

• Two or more hard disks are grouped together so
  that data is copied on each of them equally
• Almost no increase in speed each hard disk has
  to handle full load
• Full fault tolerance

Disk controller
ABCDEF
ABCDEF
31
RAID 10 (RAID 1 0)

• Combination of RAID 1 and RAID 0
• Provides both redundancy and increased speed
• Only half of space of installed hard disks is
  usable wasteful

ABCDEF
Disk controller
ACE
BDF
32
RAID 5 (data striping with parity)

• Parity information is calculated and along with
  data is spread across the hard disks
• Provides both increased speed and fault tolerance
• Provides (n-1) times space of each of the hard
  disks not very wasteful

ABCDEF
Disk controller
A C P3
B P2 E
P1 D F
33
Disk Striping and RAID

• RAID Redundant Array of Independent Drives
• RAID 0 is simply mirror of a one disk on another
• RAID 5 provides striping and parity.
• RAID 5 controller combines at least 3 drives to
  act as if they were one drive.
• Striping means that data writing is spread out
  over the physical disks as stripes.
• Parity means that redundant data is written on to
  each drive about the other drives.

34
RAID Absolutely Essential

• The parity information is the key.
• If a single drive in the RAID set fails, THE
  SERVER WILL KEEP RUNNING.
• When the faulty drive is removed and a new one
  inserted, the new drive will automatically be
  rebuilt using the parity information from the
  other drives.
• RAID allows maximum server uptime and is a must
  for even the smallest server.

35
Database - Security

• more sensitive data being stored
• more people given access to database
• security is defined as protection of the database
  against accidental or intentional loss,
  destruction or misuse.

36
Database - Security

• Data management software usually provides the
  following security features
• creation of views to restrict user access
• authorization rules
• programs further constraining or limiting
  database access
• encryption of data
• identify user attempting any database access
  (biometric, smartcard).