Lecture Database concepts (3rd Edition): Chapter 6 - David M. Kroenke, David J. Auer

Chia sẻ: 8255652125 8255652125 | Ngày: | Loại File: PPT | Số trang:62

Thêm vào BST

Báo xấu

38
lượt xem 1
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Upon completion of this lesson, the successful participant will be able to: Understand the need for and importance of database administration; learn different ways of processing a database; understand the need for concurrency control, security, and backup and recovery; learn typical problems that can occur when multiple users process a database concurrently; understand the use of locking and the problem of deadlock;…

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Lecture Database concepts (3rd Edition): Chapter 6 - David M. Kroenke, David J. Auer

DAVID M. KROENKE and DAVID J. AUER DATABASE CONCEPTS, 3rd Edition Chapter Six Database Administration
Chapter Objectives • Understand the need for and importance of database administration • Learn different ways of processing a database • Understand the need for concurrency control, security, and backup and recovery • Learn typical problems that can occur when multiple users process a database concurrently • Understand the use of locking and the problem of deadlock KROENKE and AUER - DATABASE 6-2
Chapter Objectives (continued) • Learn the difference between optimistic and pessimistic locking • Know the meaning of ACID transaction • Learn the four 1992 ANSI standard isolation levels • Understand the need for security and learn a generalized model of database security • Know the difference between DBMS and application security • Know the difference between recovery via reprocessing and recovery via rollback/rollforward KROENKE and AUER - DATABASE 6-3
Chapter Objectives (continued) • Understand the nature of the tasks required for recovery using rollback/rollforward • Know basic administrative and managerial DBA functions • Understand distributed database processing • Understand the concept of object-relational databases KROENKE and AUER - DATABASE 6-4
Heather Sweeney Designs: Database Design KROENKE and AUER - DATABASE 6-5
Heather Sweeney Designs: HSD Database Diagram in SQL Server 2005 KROENKE and AUER - DATABASE 6-6
The Database Processing Environment KROENKE and AUER - DATABASE 6-7
Control, Security and Reliability • Three necessary database administration functions – Concurrency control – Security – Backup and Recovery KROENKE and AUER - DATABASE 6-8
Concurrency Control • Concurrency control ensures that one user’s actions do not adversely impact another user’s actions • At the core of concurrency is accessibility • In one extreme, data becomes inaccessible once a user touches the data – This ensures that data that is being considered for update is not shown • In the other extreme, data is always readable – The data is even readable when it is locked for update KROENKE and AUER - DATABASE 6-9
Concurrency Control (continued) • Interdependency – Changes required by one user may impact others • Concurrency – People or applications may try to update the same information at the same time • Record retention – When information should be discarded KROENKE and AUER - DATABASE 6-10
Need for Atomic Transactions • A database operation typically involves several transactions • These transactions are atomic and are sometimes called logical units of work (LUW). • Before an operation is committed to the database, all LUWs must successfully complete. – If one or more LUW is unsuccessful, a rollback is performed and no changes are saved to the database. KROENKE and AUER - DATABASE 6-11
Transaction Example I KROENKE and AUER - DATABASE 6-12
Transaction Example II KROENKE and AUER - DATABASE 6-13
Concurrent Processing Example KROENKE and AUER - DATABASE 6-14
Lost Update Problem • If two or more users are attempting to update the same piece of data at the same time, it is possible that one update may overwrite another update KROENKE and AUER - DATABASE 6-15
Lost Update Problem Example KROENKE and AUER - DATABASE 6-16
Concurrency Issues • Dirty reads – The transaction reads a changed record that has not been committed to the database • Inconsistent reads – The transaction re-reads a data set and finds that the data has changed • Phantom reads – The transaction re-reads a data set and finds that a new record has been added KROENKE and AUER - DATABASE 6-17
Resource Locking • To avoid concurrency issues, resource locking will disallow transactions from reading, modifying, and/or writing to a data set that has been locked KROENKE and AUER - DATABASE 6-18
Implicit versus Explicit Resource Locking • Implicit locks are issued automatically by the DBMS based on an activity • Explicit locks are issued by users requesting exclusive rights to the data KROENKE and AUER - DATABASE 6-19
Concurrent Processing with Explicit Locking Example KROENKE and AUER - DATABASE 6-20