Transcript Chapter 12: Managing Multi
Database Processing Chapter 12 Managing Multi-user Databases David M. Kroenke © 2000 Prentice Hall
Multi-User Issues • Concurrency Control • Database Reliability • Database Security • Database Administration Chapter 12 Page 307 © 2000 Prentice Hall
Chapter 12 Common Multi-User DBMS • Windows 2000 – Access 2000 – SQL Server – ORACLE • UNIX – ORACLE – Sybase – Informix Page 307 © 2000 Prentice Hall
Concurrency Control
Chapter 12
“making sure that one user’s work does not inappropriately influence another’s” The need for
atomic transactions
“logical work performed as a unit”
Page 308 © 2000 Prentice Hall
Chapter 12 Concurrent Processing Problems • Lost update problem • Inconsistent read problem Page 312 © 2000 Prentice Hall
Chapter 12 Remedy for inconsistencies caused by concurrent processing • Resource Locking
“disallow sharing by locking data that are retrieved for update”
Page 312 © 2000 Prentice Hall
Chapter 12 Lock Terminology • Implicit locks
placed by the DBMS
• Explicit locks
placed by command
• Lock granularity
the size of the lock
• Exclusive lock • Shared lock
from access of any type from change but not read
Page 312 © 2000 Prentice Hall
Chapter 12
Serializable
Transactions
“a scheme for processing concurrent transactions”
Strategies – two-phased locking – COMMIT and ROLLBACK commands Page 313 © 2000 Prentice Hall
Chapter 12
Deadlock
“deadly embrace”; each transaction waiting for a resource that the other person has locked
Page 314 © 2000 Prentice Hall
Chapter 12 Lock Styles
Optimistic
assumption is made that no conflict will occur
Pessimistic
assumption is made that conflict will occur
Page 314 © 2000 Prentice Hall
Transaction Isolation Levels Page 317 Figure 12-8 © 2000 Prentice Hall
Summary of Cursor Types Page 319 Figure 12-9 © 2000 Prentice Hall
Database Recovery Chapter 12 • Via Reprocessing • Via Rollback/Rollforward Page 320 © 2000 Prentice Hall
Chapter 12 Recovery Terminology
Log
records of the data changes in chronological order
Before-images/After-images
record before / after it was changed
Checkpoint
copy of every a point of synchronization between the database and the transaction log
Page 321 © 2000 Prentice Hall
Database Security Page 324 Figure 12-13 © 2000 Prentice Hall
Model of ORACLE Security Page 325 Figure 12-14a © 2000 Prentice Hall
Model of SQL Server Security Page 327 Figure 12-15a © 2000 Prentice Hall
Chapter 12 Database Administration • DBA
database administrator
– manages the database structure – manages data activity – manages the DBMS – manages the data repository Page 329 © 2000 Prentice Hall
Chapter 12 Managing the Database Structure • Configuration Control • Documentation Page 330 © 2000 Prentice Hall
Managing the Database Structure Page 331 Figure 12-16 © 2000 Prentice Hall
Chapter 12 Managing Data Activity
Data dictionary
the data items, and their relationships
Data proponents
names and formats of key database users
Page 331 © 2000 Prentice Hall
Managing Data Activity Page 332 Figure 12-17 © 2000 Prentice Hall
Managing the DBMS Page 333 Figure 12-18 © 2000 Prentice Hall
Chapter 12 Managing the Data Repository
Data repositories
– active – passive
collections of metadata about databases, database applications, Web pages, users, and other application components
Page 334 © 2000 Prentice Hall