managing multi-user databases (1) is 240 – database management lecture #18 – 2004-04-20 prof. m....
TRANSCRIPT
![Page 1: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/1.jpg)
Managing Multi-User Databases (1)
IS 240 – Database Management
Lecture #18 – 2004-04-20Prof. M. E. Kabay, PhD, CISSP
Norwich University
![Page 2: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/2.jpg)
2 Copyright © 2004 M. E. Kabay. All rights reserved.
Topics
Database AdministrationConfiguration ControlDocumentation
Concurrency ControlAtomic TransactionsResource LockingConsistent TransactionsTransaction Isolation LevelCursor Type
![Page 3: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/3.jpg)
3 Copyright © 2004 M. E. Kabay. All rights reserved.
Database Administration
Why might we need to administer DBs?Changing requirementsManaging employee turnoverHandling hardware & software failuresMeeting SLAs (Service Level Agreements)
![Page 4: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/4.jpg)
4 Copyright © 2004 M. E. Kabay. All rights reserved.
Functions of the DBA
Managing DB structureControlling concurrent processingManaging processing rights & responsibilitiesDeveloping and implementing DB securityProviding for DB recoveryManaging DB performance & resourcesMaintaining the data repository
![Page 5: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/5.jpg)
5 Copyright © 2004 M. E. Kabay. All rights reserved.
Managing DB Structure
Configuration ControlParticipate in early design and
implementationControl & manage changes to structure
Inevitable changes in requirementsPolicies on how to coordinate requests
for changeProcedures for testing and
implementing changesPrepare for unexpected
Emergency quick-response plansParticipate in business-continuity planningMaintain disaster-recovery plans
![Page 6: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/6.jpg)
6 Copyright © 2004 M. E. Kabay. All rights reserved.
Documentation
Documentation integral component of structure maintenanceWhich changes were made when
Errors may not be visible for monthsMay need to roll back changes to
previous statusHistorical data important for trend analysisNew programmers & DBAs must be able to
understand system quickly
![Page 7: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/7.jpg)
7 Copyright © 2004 M. E. Kabay. All rights reserved.
Concurrency Control
Atomic TransactionsResource LockingConsistent TransactionsTransaction Isolation LevelCursor Type
![Page 8: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/8.jpg)
8 Copyright © 2004 M. E. Kabay. All rights reserved.
Multi-Step Transactions Are Fragile
Think about order-entry systemCreate order-header
Includes total of cost of line-items (details)Updated at END of detail data entry
Begin entering line-itemsEnter 3 records … have not yet finishedSystem crashes
What is the value in the order-header’s total field?
![Page 9: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/9.jpg)
9 Copyright © 2004 M. E. Kabay. All rights reserved.
Concurrency Causes New Problems
E.g., The Lost Update Problem:User A reads inventory: finds 20 widgets.User B reads inventory: also finds 20
widgets.User A subtracts 10 widgets from 20, writes
total ____ widgets back into inventoryUser B subtracts 5 widgets from ____, writes
total ____ widgets back into inventoryBut actually, there are only ____ widgets left
in the real inventory
20 widgets
A
20 widgets
B
![Page 10: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/10.jpg)
10 Copyright © 2004 M. E. Kabay. All rights reserved.
Atomic Transactions
We want to completeAll the steps of a transaction orNone of the steps
ATOMICGreek “a” for “not” & “tomos” for “cut”Thus “atomic” means “can not be cut.”
We mark atomic transactions with boundariesStart transactionCommit transaction
If necessary, can reverse steps takenRollback transaction
ατομος
![Page 11: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/11.jpg)
11 Copyright © 2004 M. E. Kabay. All rights reserved.
Resource Locking
Basic Concepts of LockingLock TerminologySerializable TransactionsDeadlocksOptimistic vs Pessimistic LockingDeclaring Lock Characteristics
![Page 12: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/12.jpg)
12 Copyright © 2004 M. E. Kabay. All rights reserved.
Basic Concepts of Locking
Locking is used in inter-process communication (IPC)
A lock is a form of semaphore (signal)Locks allow processes to
coordinate their access to resources Prevent inconsistencies
In DBMS, primarily used to serialize data accessOne process gets control of data at a time
![Page 13: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/13.jpg)
13 Copyright © 2004 M. E. Kabay. All rights reserved.
Lock Terminology Implicit vs explicit
Automatic locks placed by DBMS: implicitProgrammatically ordered: explicit
Lock granularityLarge: database, datasetFine: records
Exclusive vs shared locksExclusive:
One process READ/WRITENo other processes allowed at all
Shared:One process has R/WOther processes can only READ
![Page 14: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/14.jpg)
14 Copyright © 2004 M. E. Kabay. All rights reserved.
Conditional vs Unconditional Locking
Conditional lockingProcess 1 locks resource AProcess 2 locks resource A
Receives error conditionLock fails and process 2 continues
Unconditional lockingProcess 1 locks resource AProcess 2 locks resource A
Does not receive a condition reportProcess 2 waits in suspense (hangs)
until lock is granted
![Page 15: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/15.jpg)
15 Copyright © 2004 M. E. Kabay. All rights reserved.
Serializable Transactions
Prevent transactions affecting same records from overlapping
Two-phase lockingCan accumulate locksBut once any lock is released, cannot get
more until all are releasedDefines growing phase and shrinking
phaseMore restrictive (and more common) strategy
No locks released until COMMIT or ROLLBACK
![Page 16: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/16.jpg)
16 Copyright © 2004 M. E. Kabay. All rights reserved.
Deadlock (Deadly Embrace)
11 22
AA BB
Process 1 locks resource
A unconditionally
Process 2 locks resource
B unconditionall
y
1 locks Bunconditionall
y
1 locks Bunconditionall
y
2 locks Aunconditionall
y
![Page 17: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/17.jpg)
17 Copyright © 2004 M. E. Kabay. All rights reserved.
Preventing Deadlocks
Deadlock is example of a race conditionWill not necessarily occurOccurs by chance when specific events
happen at specific timeAlways ensure that processes in applications
LOCK RESOURCES IN SAME ORDERUNLOCK RESOURCES IN REVERSE
ORDERApply these principles to example on
previous slide to see how they absolutely prevent deadlock
![Page 18: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/18.jpg)
18 Copyright © 2004 M. E. Kabay. All rights reserved.
Pessimistic Locking Strategy
Assume collisions will occur and prevent conflictsLock recordsProcess transactionRelease locks
But very dangerous for performance if processing involves human interactionNot controllableOperator can leave resources locked and hang
systemOperator could go to lunch!
DO NOT LOCK AROUND HUMAN INTERVENTION!
![Page 19: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/19.jpg)
19 Copyright © 2004 M. E. Kabay. All rights reserved.
Optimistic Locking Strategy
Assume collisions will be rare and recover if they happenRead original data recordsProcess transaction using buffersLock original data recordsCheck to see if original data have changed
If no change, commit transaction & unlock
If change, unlock & start over
![Page 20: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/20.jpg)
20 Copyright © 2004 M. E. Kabay. All rights reserved.
Optimistic Locking (1)
ProcessProcess
Value1
Value3
Value2
Value3
Value1
DB
Data buffers
Same?
Shows events when data change while processing is being carried out
after initial READ
![Page 21: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/21.jpg)
21 Copyright © 2004 M. E. Kabay. All rights reserved.
Optimistic Locking (2)
ProcessProcess
Value1
Value1
Value2
Value1
Value1
DB
Data buffers
Same?
Value2
Observe events whenthere is no change
in initial dataduring processing
![Page 22: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/22.jpg)
22 Copyright © 2004 M. E. Kabay. All rights reserved.
Optimistic vs Pessimistic Strategies
Optimistic locking advantagesDoes not lock resources around human
interventionAppropriate for Web / Internet transactionsEspecially important if lock granularity is
large (e.g., entire DB or entire tables)Optimistic locking disadvantages
If specific resource is in high demand (much contention for specific records) then can cause repeated access (thrashing)
Can degrade individual and system performance
![Page 23: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/23.jpg)
23 Copyright © 2004 M. E. Kabay. All rights reserved.
Declaring Lock Characteristics
Older programs often used specific calls to locking routinesE.g., “DBLOCK”Passed parameters to set exact type of lock
Conditional or not, granularity etc.Modern programming using DBMS uses
transaction markersBEGIN, COMMIT, ROLLBACKAllows global definition of locking strategyDBMS handles detailsCan thus change locking globally without
reprogramming
![Page 24: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/24.jpg)
24 Copyright © 2004 M. E. Kabay. All rights reserved.
Homework
First, take a deep breath and assure yourself that you can do this.
Study pages 295-307 very carefully using SQ3R.
Prepare for Thursday’s class by using the survey-question phase of SQ3R on pages 307-325. Then actually READ those pages.
REQUIRED: By Tuesday 27th April 2003:For 40 points, complete questions 11.1
through 11.20 and include the questions with your answers
![Page 25: Managing Multi-User Databases (1) IS 240 – Database Management Lecture #18 – 2004-04-20 Prof. M. E. Kabay, PhD, CISSP Norwich University mkabay@norwich.edu](https://reader036.vdocuments.us/reader036/viewer/2022070305/551472c5550346414e8b6183/html5/thumbnails/25.jpg)
25 Copyright © 2004 M. E. Kabay. All rights reserved.
DISCUSSION