Database Processing Eighth Edition Managing MultiUser Databases Chapter

Multi-User Databases • Serving the needs of multiple users and multiple applications adds complexity

Multi-User Database Issues • Interdependency – Changes required by one user may impact others • Concurrency – People or applications may try to update the same information at the same time 3 © 2002 by Prentice Hall

Multi-User Database Issues • Record Retention – When information should be discarded • Backup/Recovery

Common Multi-User DBMS • Windows 2000 – Access 2000 – SQL Server – ORACLE

Role of the Database Administrator • Organizations typically hire a database administrator (DBA) to handle the issues and complexities associated with multi-user databases. • A DBA facilitates the development and use of one or more databases. 6 © 2002 by Prentice Hall

Data Administrator versus Database Administrator • Data Administrator – Handle the database functions and responsibilities for the entire organization. • Database Administrator (DBA) – Handle the functions associated with a specific database, including those applications served by the database. 7 © 2002 by Prentice Hall

The Characteristics of a DBA • Technical – The DBA is responsible for the performance and maintenance of one or more databases. • Diplomatic – The DBA must coordinate the efforts, requirements, and sometimes conflicting goals of various user groups to develop community-wide solutions. 8 © 2002 by Prentice Hall

Technical Skills of the DBA • Managing the database structure • Controlling concurrent processing • Managing processing rights and responsibilities • Developing database security • Providing database recovery • Managing the database management system (DBMS) • Maintaining the data repository 9 © 2002 by Prentice Hall

Managing the Database Structure • Managing the database structure includes configuration control and documentation regarding: – The allocation of space – Table creation – Indices creation – Storage procedures – Trigger creation 10 © 2002 by Prentice Hall

Configuration Control • Post-implementation changes • Procedures & Policies • Address unanticipated problems 11

The Need for Documentation • Crucial when trying to understand problems • Backups and

Documentation • All structural changes must be carefully documented with the following: – Reason for change – Who made the changes – Specifically what was changed – How and when the changes were implemented – How were the changes tested and what were the results 13 © 2002 by Prentice Hall

Documentation Aids • Version Control and Computer Assisted Software Engineering (CASE) tools automate and/or manage many tedious documentation tasks. • Printing the data dictionaries after structural changes also helps eliminate many tedious documentation tasks 14 © 2002 by Prentice Hall

Controlling Concurrency Processing • Concurrency control ensures that one user’s actions do not adversely impact another user’s actions • At the core of concurrency is accessibility. 15 © 2002 by Prentice Hall

Aspects of Concurrency Control • Rollback/Commit: Ensuring all actions are successful before posting to the database • Multitasking: Simultaneously serving multiple users • Lost Updates: When one user’s action overwrites another user’s request 16 © 2002 by Prentice Hall

Rollback/Commit • Involve logical units of work (LUW). • Commit when entire LUW successful.

Lost Update Problem • When two or more users are attempting to update the same piece of data at the same time. • Resource locking scenarios are designed to address this problem 18 © 2002 by Prentice Hall

Resource Locking • A resource lock prevents a user from reading and/or writing to a piece of data • The size of the piece of data (e. g. , database, table, field) is termed the lock granularity 19 © 2002 by Prentice Hall

Types of Resource Locks • Implicit versus Explicit • Exclusive versus Shared 20 ©

Two-Phased Resource Locking • Two-phased locking, whereby locks are obtained as they are needed – A growing phase, whereby the transaction continues to request additional locks – A shrinking phase, whereby the transaction begins to release the locks 21 © 2002 by Prentice Hall

Deadlocks • When two transactions are waiting on one another to release resources. 22

Avoiding Deadlocks • Strategy 1: – Wait until all resources are available, then lock them all before beginning • Strategy 2: – Establish and use clear locking orders/sequences • Strategy 3: – Once detected, the DBMS will rollback one transaction 23 © 2002 by Prentice Hall

Resource Locking Strategies • Optimistic Locking – Read data – Process transaction – Issue update – Look for conflict – If conflict occurred, rollback and repeat or else commit • Pessimistic Locking – Lock required resources – Read data – Process transaction – Issue update – Release locks 24 © 2002 by Prentice Hall

Consistent Transactions • Consistent transactions are often referred to by the acronym ACID –

ACID: Atomic • A transaction consists of a series of steps. Each step must be successful for the transaction to be saved. • This ensures that the transaction completes everything it intended to do before saving the changes. 26 © 2002 by Prentice Hall

ACID: Consistent • No other transactions are permitted on the records until the current statement finishes • This ensures that the transaction integrity has statement level consistence among all records 27 © 2002 by Prentice Hall

ACID: Isolation • Within multi-user environments, different transactions may be operating on the same data. As such, the sequencing of uncommitted updates, rollbacks, and commits continuously change the data content. • The 1992 ANSI SQL standards define four isolation levels and specify respective issues. 28 © 2002 by Prentice Hall

Summary of Isolation Levels 29 © 2002 by Prentice Hall

ACID: Durable • Once committed, durable transactions are saved to the data permanently 30

Set-at-a-Time Versus Row-at-a-Time • SQL statements act as filters for the entire data set. • A cursor may be defined within a SQL statement to point to a particular record. • Several types of cursors have been defined. The cursor type defines how the cursor behaves. 31 © 2002 by Prentice Hall

Types of Cursors 32 © 2002 by Prentice Hall

Database Security • Database security strives to ensure… – Only authorized users perform authorized

Managing Processing Rights and Responsibilities • Processing rights define who is permitted to do what, when • The individuals performing these activities have full responsibility for the implications of their actions • Individuals are identified by a username and a password 34 © 2002 by Prentice Hall

Granting of Processing Rights • Database users are known as an individual and as a member of one or more role • Access and processing rights/privileges may be granted to an individual and/or a role • Users possess the compilation of rights granted to the individual and all the roles for which they are members 35 © 2002 by Prentice Hall

Granting Privileges 36 © 2002 by Prentice Hall

Providing Database Recovery • Common causes of database failures… – Hardware failures – Programming bugs – Human errors/mistakes – Malicious actions • Since these issues are impossible to completely avoid, recovery procedures are essential 37 © 2002 by Prentice Hall

Database Recovery Characteristics • Continuing business operations (Fallback procedures/Continuity planning) • Restore from backup • Replay database activities since backup was originally made 38 © 2002 by Prentice Hall

Fall-back Procedures/ Continuity Planning • The business will continue to operate even when the database is inaccessible • The fall-back procedure defines how the organization will continue operations • Careful attention must be paid to… – saving essential data – continuing to provide quality service 39 © 2002 by Prentice Hall

Restoring from Backup • In the event that the system must be rebuilt or reloaded, the database is restored from the last full backup. • Since it is inevitable that activities occurred since the last full backup was made, subsequent activities must be replayed/restored. 40 © 2002 by Prentice Hall

Recovery via Reprocessing • This is a brunt-force technique. • Simply re-type all activities since the backup was performed. • This procedure is costly because of the effort involved in re-entering the data. • This procedure is risky in that human error is likely and in that paper record-keeping may not be accurate. 41 © 2002 by Prentice Hall

Recovery via Rollback/Rollforward • Most database management systems provide a mechanism to record activities

Rollforward • Activities recorded in the log files may be replayed. In doing so, all activities are re-applied to the database. • This procedure is used to resynchronize restored database data. • This procedure is termed a Rollforward. 43 © 2002 by Prentice Hall

Rollback • Since log files save activities in sequence order, it is possible to undo activities in reserve order that they were originally executed. • This is performed to correct/undo erroneous or malicious transaction(s). • This procedure is known as a Rollback. 44 © 2002 by Prentice Hall

Managing the Database Management System (DBMS) • In addition to controlling and maintaining the users and the data, the DBA must also maintain and monitor the DBMS itself. – Performance statistics (performance tuning/optimizing) – System and data integrity – Establishing, configuring, and maintaining database features and utilities 45 © 2002 by Prentice Hall

Maintaining the Data Repository • The data repository contains metadata. Metadata is data about data. • The data repository specifies the name, type, size, format, structure, definitions, and relationships among the data. They also contain the details about applications, users, add-on products, etc. 46 © 2002 by Prentice Hall

Types of Data Repositories • Active data repository – The development and management tools automatically maintain and upkeep the metadata. • Passive data repository – People manually maintain and upkeep the metadata 47 © 2002 by Prentice Hall