Database Processing Chapter 12 Managing Multiuser Databases David
Database Processing Chapter 12 Managing Multi-user Databases David M. Kroenke © 2000 Prentice Hall
Chapter 12 Multi-User Issues • • Concurrency Control Database Reliability Database Security Database Administration Page 307 © 2000 Prentice Hall
Chapter 12 Common Multi-User DBMS • Windows 2000 – Access 2000 – SQL Server – ORACLE Page 307 • UNIX – ORACLE – Sybase – Informix © 2000 Prentice Hall
Chapter 12 Concurrency Control “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 from access of any type Shared lock 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
Chapter 12 Database Recovery • 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 copy of every record before / after it was changed Checkpoint 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 -14 a © 2000 Prentice Hall
Model of SQL Server Security Page 327 Figure 12 -15 a © 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 names and formats of the data items, and their relationships Data proponents 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 collections of metadata about databases, database applications, Web pages, users, and other application components – active – passive Page 334 © 2000 Prentice Hall
- Slides: 24
