MIT 5314 Database Applications Slide 1 Database Administration

MIT 5314: Database Applications Slide # 1 Database Administration Chapter 12: Database Administration (With Modifications) Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 2 Database Administration Once upon a time, the typical IS Organization appeared as: CEO VP Marketing VP Finance VP Production • • • Accounting EDP Depart. Why ? ? ? n It made perfect sense: Ø Information Systems were applied where they were most needed • • Dr. Peeter Kirs Accounting Systems Other standardized, routine applications Fall, 2003

MIT 5314: Database Applications Slide # 3 Database Administration As information became used for more purposes and across more functions, the IS Organization changed: CEO VP Marketing VP Finance VP Production CIO Systems Development Database Administration End User Services Ø Information Systems were applied everywhere Ø Information Systems were recognized as an Organizational Resource Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 4 Database Administration Basic Definitions: n Data Administrator (DA) Ø A high-level function that is responsible for the overall management of data resources in an organization: • May be the CIO n Database Administrator (DBA) I am the boss!! Ø A technical function that is responsible for the physical database design and such issues as security enforcement and database performance n Database Steward Without me, You’re Nothing!! Ø A administrative function that is responsible for assuring that organizational applications meet the enterprise goals Dr. Peeter Kirs Die, you Egomaniacs!! Fall, 2003

MIT 5314: Database Applications Slide # 5 Database Administration Data Administration Functions: n Data Policies Ø Explicit statement of goals, objectives, and targets • Goal: To Support Cost-Effective Use of the computer environment • Objective: To improve sharing of information across organizational units • Target: Linking of all departmental databases within 2 years n Data Procedures Ø Written Statement of actions to be taken for a certain activity • “In the event of a database failure, the DBA will: 1. • • • n Data Standards Ø Explicit statement of conventions to be followed in data usage • “All table names will be prefaced by their physical location” • “All fields containing age, weight, …. Will contain the data type short” Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 6 Database Administration Data Administration Functions: n Planning Ø Development of the Organization’s IT Strategy • Must correspond to the Organization’s Business Strategy • E. g. , Consider the Difference between UTEP and Harvard Ø Development of the enterprise model • Top-Down versus Bottom-Up Viewpoint Ø Development of cost/benefit model • Targets must be measurable Ø Design of the database environment • Centralized, distributed, Decentralized? ? How? ? Ø Develop the data administration plan • A lower-level plan for database implementation, maintenance and growth Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 7 Database Administration Data Administration Functions: n Data Analysis Ø Define and model data requirements Ø Define and model business rules Ø Define operational requirements Ø Maintain corporate data dictionary n Data Conflict Resolution Ø Who owns the data? • The department, the business subunit, the corporation? • NOT a trivial question. • Procedures MUST be established in advance Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 8 Database Administration Data Administration Functions: n Internal Marketing Ø Information Systems are political entities • The DA must sell their arguments Ø Recall the Systems Trinity: • The Manager: The person in charge of the functional department • The System Developer: The person developing the system • The User: The person who will use the system Ø Recall why systems fail: • Lack of Top management support • Lack of user Acceptance • Bad system Design The relationship is like a 3 -legged stool: If any leg breaks, the stool collapses Ø It is the DA’s job to make sure that ALL stakeholders are happy Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 9 Database Administration Data Administration Functions: n Managing the Data Repository Ø Used by the DA to manage the information- processing environment • Contain metadata that describes the organization’s data and data processing resources • Replacing Data Dictionaries (simple data-element documentation tools) • Provides information about: • What users must know what • What automated CASE tools that are used to specify and develop information systems • All Applications that access and manipulate data • Dr. Peeter Kirs DBMS that maintain the repository and update system privileges, passwords, and other information Fall, 2003

MIT 5314: Database Applications Slide # 10 Database Administration Functions: n Selection of Hardware and Software Ø Difficult to keep abreast of current technology Ø Difficult to predict future changes Ø Emphasis on established off-the-shelf products n Managing Data Security and Privacy Ø Firewalls Ø Establishment of user privileges Ø Complicated by use of distributed systems n Managing Data Integrity Ø Data consistency Ø Maintaining data relationships Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 11 Database Administration Functions: n Database Backup Ø We must assume that a database will eventually fail Ø Establishment of procedures • How often should the data be back-up? • What data should be backed-up more frequently? • Who is responsible for the back-ups? n Database Recovery Ø Application of proven strategies for reinstallation of database after crash Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 12 Database Administration Shared Administration Activities: n Database Design Ø DA: Logical Design Ø DBA: • External Model Design (Subschemas) • Physical Design/Construction • Design Integrity Controls n Database Implementation Ø DBA: • Establish Security Controls • Supervise Database Loading • Specify Test Procedures • Develop Programming Standards • Establish Back-up/Recovery Procedures Ø Both: Dr. Peeter Kirs • Specify Access Policies • USER TRAINING Fall, 2003

MIT 5314: Database Applications Slide # 13 Database Administration Shared Administration Activities: n Operations and maintenance Ø DBA: • Monitor database performance • Tune and reorganize databases as needed • Enforce standards and procedures Ø Both: Support Users n Growth and Change Ø Both: • Implement Change-Control Procedures • Plan for growth and change • Evaluate new technologies Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 14 Database Administration Data Warehouse Administration: n New function due to increased use of data warehousing Ø (Massively) Integrated decision support databases from various sources n Emphasis on integration and coordination of data and metadata from multiple databases n Specific Functions 1. Support decision-oriented applications 2. Manage data warehouse (exponential) growth 2. Establish service level agreements Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 15 Database Administration Data Dictionaries and Repositories: n Data Dictionary Ø Documents data and metadata elements of a database n Systems Catalog Ø System-generated database that describes all database objects n Information Repository Ø Stores metadata describing data and data processing resources n Information Repository Dictionary System (IRDS) Ø A software tool managing and controlling access to the Information Repository Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 16 Database Administration Data Dictionaries and Repositories: n Components of the repository system architecture A schema of the repository information Software that manages the repository objects Where repository objects are stored Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 17 Database Administration Database performance tuning: n DBMS Installation Ø Setting installation parameters n Memory Usage Ø Setting cache-levels Ø Choosing background processes n Input/Output Contention Ø Deciding who gets what and when Ø How to distribute heavily accessed files n CPU usage Ø Monitoring of CPU loads n Application Tuning Ø Modification of SQL code in applications Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 18 Database Administration Database Security: Ø Protection of data against accidental or intentional loss, destruction, or misuse Ø Increased difficulty due to internet access and client-server technologies Possible locations of data security threats Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 19 Database Administration Threats to Data Security: n Accidental Losses Ø Human Error Ø Software Failure Ø Hardware Failure n Theft and Fraud Ø Establishment of firewalls Ø Monitoring of activities Ø Be careful of ‘disgruntled’ employees n Improper data access Ø Loss of Privacy (Personal data) Ø Loss of Confidentiality (Corporate data) Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 20 Database Administration Threats to Data Security: n Loss of data integrity Ø Data may be compromised due to database crashes Ø Improper recovery can be costly n Loss of Availability Ø Through Sabotage/Data Misplacement Ø Viruses/Worms Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 21 Database Administration Managing Data Security: n Data Integrity Controls: Ø Default Values Entered • Minimization of user data entry Ø Domain Restrictions • Only certain values can be entered Ø Probability Checks • Echoing of input to user for confirmation Ø Self-checking routines • E. g. , Check-digits Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 22 Database Administration Managing Data Security: n Views and Subschemas: Ø Views are not only useful, but can also restrict user access to data Ø Recall our Physician/Patient Database View: CREATE VIEW drugs_given AS SELECT physname, patient. name, illness. name, prescription. drugcode FROM physician, patient, treatment, illness, prescription WHERE physician. physid = patient. physid AND patient. patid = treatment. patid AND treatment. illcode = illness. illcode AND treatment. drugcode = prescription. drugcode ORDER BY physname; Ø The user might be restricted from using the view Ø The user might be restricted from seeing the view’s code (And hence seeing the physical relationships) Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 23 Database Administration Managing Data Security: n Authorization Rules: Ø Rules to Restrict Access Authorization Matrix Subject Tables Dr. Peeter Kirs SQL Privileges Object Tables Fall, 2003

MIT 5314: Database Applications Slide # 24 Database Administration Managing Data Security: n Statistical Databases: 1. The Conceptual Model • Only the datasets with common attributes and their statistics are made available • No data manipulation language is allowed to merge and intersect populations 2. Query Restriction • Query-set Size controls (large only) • Number of over-lapping entities among successive queries • Auditing User Queries • Clustering individual entities in mutually exclusive subsets Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 25 Database Administration Managing Data Security: n Statistical Databases: 3. Output Perturbation • Queries made on actual data • Output ‘perturbed’ so that statistical characteristics remain but individual data is ‘non-sensical’ 4. Data Perturbation • The entire database is first ‘perturbed’ • All statistical relations are maintained in the perturbed dataset • User allowed to make all queries on the perturbed data set (individual data entities show no relationship to the real data) Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 26 Database Administration Managing Data Security: n Authentication Schemes: Ø Problem: Passwords are flawed • Users Share them • Sometimes easy to determine • User write them down and they get copied • Automatic logon scripts make it unnecessary to enter them manually • Unencrypted passwords travel the internet Ø Goal: Verify User Identity Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 27 Database Administration Managing Data Security: n Authentication Schemes: Ø Potential Solutions: • Randomly Assigned Passwords • Forced Password Changes • Secondary Passwords • Biometric Devices • Thumbprint • Hand Geometry • Retinal Scan • Voice Recognition • Facial Recognition • Future: • • Dr. Peeter Kirs Body Odor Multi-attribute Fall, 2003

MIT 5314: Database Applications Slide # 28 Database Administration Managing Data Security: n Encryption (“The Second Oldest Profession”): Ø The earliest recorded use of cryptography is 1900 BC in Egypt. Ø The scribes who sketched the hieroglyphs telling the story of the life of Khnumhotep II in the town of Menet Khufu used a substitution cipher to encrypt the names and titles of individuals in the story. Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 29 Database Administration Managing Data Security: n Encryption (“The Second Oldest Profession”): Ø Substitution Ciphers • The Original symbols are substituted for other symbols • Plain Text: ABCDEFGHIJKLMNOPQRSTUVWXYZ Cipher Text: XYZABCDEFGHIJKLMNOPQRSTUVW “Now is the time for all good people to come to the aid…” Dr. Peeter Kirs “KLT FP QEB QFJB CLO XII DLLA MBLMIB QI ZLJB QI QEB XFA …” Fall, 2003

MIT 5314: Database Applications Slide # 30 Database Administration Managing Data Security: n Encryption: Ø Public/Private Keys Ø Pretty Good Privacy (PGP) Phil Zimmerman • Should the Government have the right to a “Master Key”? • Target of 3 -year investigation that he violated export laws Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 31 Database Administration Database Recovery: Ø Mechanisms for restoring a database quickly and accurately after loss of damage n Recovery Facilities/Components: 1. Back-up Facilities • Periodic back-up copies of the entire database 2. Journalizing Facilities • To maintain audit trails of transactions and logs of database changes 3. Checkpoint Facilities • When the DBMS temporarily halts all activities and synchronizes all files and journals 4. Recovery Manager • A DBMS component that restores the database to a correct condition and restarts processing activities Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 32 Database Administration Database Recovery: n Ongoing Facilities: Backup Facility: Automatic periodic duplication of entire Database Current Database DBMS Transaction Log Journalizing Facility: Database Backup Dr. Peeter Kirs • Logging of Transactions and Database Changes Before and after images of records that have been changed DB Change Log • Logging of every transaction along with timestamps Fall, 2003

MIT 5314: Database Applications Slide # 33 Database Administration Database Recovery: n Periodic/On Demand Facilities: Checkpoint Facility: The processing is stopped and database synchronized Current Database DBMS Transaction Log DB Change Log Recovery Manager: Database Backup Dr. Peeter Kirs Upon crash, the database is rebuilt using the Database backup, DB Change log, and Transaction Log Fall, 2003

MIT 5314: Database Applications Slide # 34 Database Administration Database Recovery: n Back-up Facilities: Ø How long between backup (hourly, daily, weekly) is a policy determined by the DA Ø Frequent back-ups increase reliability BUT each takes some time Ø Back-ups should be stored off-site Ø Approaches: q Cold Backup • Database shut down during back-up • More secure BUT transactions delayed q Hot Backup • Selected portion of database is shut down during back-up • Dr. Peeter Kirs Not as disruptive BUT more complicated Fall, 2003

MIT 5314: Database Applications Slide # 35 Database Administration Database Recovery: n Journalizing Facilities: Ø Every transaction is stored to the transaction log as well as the database q Transaction Log • Record of essential data for each transaction processed against the database q Database Change Log • Before-Images of records (before transaction) • After-Images of records (After modification) Ø Needed for: • • Dr. Peeter Kirs Transaction Audits Database Recovery Fall, 2003

MIT 5314: Database Applications Slide # 36 Database Administration Database Recovery: n Journalizing Facilities: (Recap) Transaction Current Database Effect of transaction added to current database Dr. Peeter Kirs DBMS Transaction Log Copy of transaction stored (In case of database failure) DB Change Log Copy of record affected by transaction stored • Before transaction • After transaction Fall, 2003

MIT 5314: Database Applications Slide # 37 Database Administration Database Recovery: n Checkpoint Facilities: Ø At some specified point in time (by the DA) the DBMS refuses all transactions (The system is in a Quiet state) Ø The database and the transaction logs are synchronized Transaction Current Database Dr. Peeter Kirs DBMS Transaction Log Fall, 2003

MIT 5314: Database Applications Slide # 38 Database Administration Database Recovery: n Recovery Manager: Ø Module of DBMS that restores the database to a ‘correct’ position when a failure occurs Why do databases Fail? q Aborted Transactions • The transaction terminates abnormally due to human error, input of invalid data, loss of transmission, hardware failure, deadlock, etc. q Incorrect Data • Incorrect, but valid, data entered • E. g. , incorrect account number, customer payment q System Failure • E. g. , Power loss, operator error, systems software failure • The database is NOT damaged q Database Destruction • The database is lost, destroyed, or can not be read • Often due to disk failure Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 39 Database Administration Database Recovery: n Recovery and Restart Procedures q Switch • 2 mirror-image databases maintained • All transactions stored/updated in both databases • Upon failure, the database is ‘switched’ for the mirror image • Generally stored across distributed databases • Fastest/most secure • Expensive • Does not protect against power failures or catastrophes Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 40 Database Administration Database Recovery: n Recovery and Restart Procedures q Restore/Run • The previous transactions are reprocessed (up to the point of the failure) against the backup copy of the database • The most recent copy of the database is mounted and the latest transactions rerun Transaction Log Database Backup New Database • Simple/Cheap • May take considerable time to reprocess • Resequencing errors may occur Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 41 Database Administration Database Recovery: n Recovery and Restart Procedures q Backward Recovery (Rollback) • Unwanted changes are undone through the use of Before images (in the Database Change Log) DB Change Log (Using only Before Images) Current Database New Database Backup Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 42 Database Administration Database Recovery: n Recovery and Restart Procedures q Forward Recovery (Rollforward) • After images (in the Database Change Log) are applied to the Database Backup DB Change Log (Using only After Images) Database Backup New Database Backup Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 43 Database Administration Database Recovery: What Strategy should be applied? Ø That depends on the type of failure q Aborted Transactions • Preferred: Rollback • Alternative: Rollforward (To a state just prior to the abort) q Incorrect Data • Preferred: First correct data (if possible) then rollback and rollforward with corrected data • Alternative: Compensating transactions (debit then re-credit) q System Failure (Database intact) • Preferred: Switch • Alternatives: (1) Rollback (2) Restart from Checkpoint q Database Destruction • Preferred: Suicide (unless you can Switch) • Alternatives: (1) Rollforward (2) Reprocess transactions Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 44 Database Administration Transaction Management: n Transaction: Ø A logical unit of work that must be either entirely completed or aborted Ø No intermediate states are acceptable. Ø Most real-world database transactions are formed by two or more database requests. • A database request is the equivalent of a single SQL statement in an application program or transaction Ø A transaction that changes the contents of the database must alter the database from one consistent database state to another. Ø To ensure consistency of the database, every transaction must begin with the database in a known consistent state. Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 45 Database Administration Transaction Management: n Transaction Properties: q Atomicity • A transaction is a SINGLE (indivisible), • • invisible, logical unit of work A database request and ALL related operations MUST be completed If ALL requirements are not, the transaction is aborted q Durability • A transaction must be PERMANENT • When a transaction is completed, it has reached (and must remain) in a permanent state • Once in a permanent state, it can not be lost • Even if the database fails, the transaction remains Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 46 Database Administration Transaction Management: n Transaction Properties: q Serializability • Each concurrent transaction is treated as • • thought they were received and executed in a serial (one after the other) fashion This is true even in a multi-user or distributed database If transactions do occur simultaneously, one is assigned precedence over the other q Isolation • Dr. Peeter Kirs Data/Information provided/updated by a transaction can not be used by another (later transaction) until the first transaction is complete (i. e. , accepted) Fall, 2003

MIT 5314: Database Applications Slide # 47 Database Administration Transaction Management: Ø Suppose that we wish to withdraw items from inventory Table Inventory part descrip onhand 01 Pens 276 02 Erasers 500 03 Paper 1000 Table Inventory part descrip onhand 01 Pens 276 02 Erasers 475 03 Paper Dr. Peeter Kirs 1000 IF we sell 25 Erasers: 1. Find the part Number 2. Read the number onhand 3. If the number onhand is < 25, ABORT the transaction 4. If the number onhand is >= 25, calculate the new number onhand quantity 500 - 25 = 475 5. Enter (update) the new number onhand quantity (The DBMS will update the Transaction log and Database Change Log) Fall, 2003

MIT 5314: Database Applications Slide # 48 Database Administration Transaction Management: Ø The SQL Commands needed are (sort-of) straight-forward: SELECT onhand FROM inventory WHERE part = 02; UPDATE inventory SET onhand = 475 ; OR Maybe SELECT onhand FROM inventory WHERE descrip = ‘Erasers’; UPDATE inventory SET onhand = onhand - 25 ; COMMIT; Dr. Peeter Kirs Table Inventory part descrip onhand 01 Pens 276 02 Erasers 500 03 Paper 1000 Table Inventory part descrip onhand 01 Pens 276 02 Erasers 475 03 Paper 1000 Fall, 2003

MIT 5314: Database Applications Slide # 49 Database Administration Transaction Management: Why did you say sort-of ? ? Ø Notice we didn’t check to see if there were 25 Erasers available Ø If there were not, we could not complete the transaction How do we do that ? ? Ø That is why we are going to learn SQL/PL (Structured Query Language/Programming Language) Ø Stay Tuned Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 50 Database Administration Transaction Management: Ø Of course, even simple transactions are sometimes problematic: • Suppose that Dr. Mary Smith (physid: ‘ 123456789’) Transfers all her patients to Dr. Von Bulow (physid: ‘ 374659201’) Ø The command: UPDATE patient SET patient. physid = ‘ 374659201’ WHERE patient. physid = ‘ 123456789’; Ø Will NOT be accepted unless we first enter the command: INSERT INTO patient VALUES (‘ 374659201’, ‘Von Bulow, Klaus’, ……); Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 51 Database Administration Transaction Management: Ø Consider the following Statement: “A credit card transaction is a ternary relationship between a customer, a merchant, and a bank” Customer Transaction Merchant Given 1 merchant and 1 bank, how many customers? Given 1 customer and 1 bank, how many Merchants? Mandatory? Bank Which Makes the relationship? • An Associative Entity Dr. Peeter Kirs Given 1 customer and 1 Merchant, how many banks? Mandatory? Fall, 2003

MIT 5314: Database Applications Slide # 52 Database Administration Transaction Management: Ø Assume that the following attributes apply: Cust. ID Customer Bank. ID Merchant. ID Trans. AMT Transaction Trans. Date Merchant Cust. ID Merch. ID Credit. Lim Other Balance Bank. ID Dr. Peeter Kirs Other Too Simple? Probably!! Fall, 2003

MIT 5314: Database Applications Slide # 53 Database Administration Transaction Management: Ø Our actual tables might appear as: Table Transaction Cust. ID Cred. Lim Balance A 112233 1000 A 112234 A 112235 Merch. ID Bank. ID Tran. Amt Tran. Date Merch. ID Other 325. 87 A 112233 LMR 678 CB 789 425. 76 05/03/03 LMR 678 ∙∙∙ 5000 4030. 20 A 112235 ALD 609 WF 890 107. 34 05/03/03 GXT 678 ∙∙∙ 400 125. 87 A 112234 LMR 678 FN 034 45, 00 05/03/03 ALD 609 ∙∙∙ A 112233 GXT 678 CB 789 56. 12 05/03/03 RTU 665 ∙∙∙ A 112235 RTU 665 WF 890 87. 45 05/04/03 A 112233 RTU 665 CB 789 46. 75. 45 05/04/03 Table Customer Cust. ID Bank. ID Other WF 890 ∙∙∙ FN 034 ∙∙∙ CB 789 ∙∙∙ Table Merchant Table Bank Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 54 Database Administration Transaction Management: Ø A few Activities need to be carried out: n When a transaction takes place, all of the attributes in the associative entity TRANSACTION must be recorded: n At the same time, the customer’s Credit. Lim and balance must be checked: • If Cred. Lim – Balance – Trans. Amt < 0, the purchase is denied (Aborted) • If Cred. Lim – Balance – Trans. Amt >= 0, the purchase is Accepted n IFF the purchase is accepted: • Customer Balance Must be updated • Merchant. Bal Must be updated n IFF the purchase is denied: • The entire TRANSACTION is deleted Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 55 Database Administration Transaction Management: n Transaction Logs: (A Quick and Dirty Review) q The DBMS uses transaction logs (A Table) to keep track of all transactions on a database q Intended as an organizational record of transactions q Necessary if a ROLLBACK is issued q Necessary in case of a database failure/crash • In case of failure, the transaction log is used to ROLLFORWARD • Transactions added since the previous COMMIT are added and COMMITted to the database Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 56 Database Administration Transaction Management: n Transaction Logs: Ø Consider a Sample Transaction Log for our Previous Problem: Table Customer Table Transaction Cust. ID Cred. Lim Balance Cust. ID Merch. ID Bank. ID Tran. Amt Tran. Date A 112233 1000 325. 87 • • • • • • A 112234 5000 4030. 20 A 112233 GXT 678 CB 789 56. 12 05/03/03 A 112235 400 125. 87 • • • • • • Trans_Num Table Row_ID Attribute Before After 10441 CUSTOMER A 112233 Balance 269. 75 325. 87 10441 TRANSACTION Cust. ID A 112233 10441 TRANSACTION Merch. ID GXT 678 10441 TRANSACTION Bank. ID CB 798 10441 TRANSACTION Trans. Date 05/03/03 10441 TRANSACTION Trans. Amt 56. 12 Assigned by DBMS Dr. Peeter Kirs NOTE: Only Information about affected Tables Included Fall, 2003

MIT 5314: Database Applications Slide # 57 Database Administration Transaction Management: n Transaction Logs: Ø If the transaction is aborted, we can rollback with the transaction log: Table Customer (Before) Table Customer (After) Cust. ID Cred. Lim Balance A 112233 1000 325. 87 A 112233 1000 269. 75 A 112234 5000 4030. 20 A 112235 400 125. 87 Trans_Num Table Row_ID Attribute Before After 10441 CUSTOMER A 112233 Balance 269. 75 325. 87 10441 TRANSACTION Cust. ID A 112233 10441 TRANSACTION Merch. ID GXT 678 10441 TRANSACTION Bank. ID CB 798 10441 TRANSACTION Trans. Date 05/03/03 10441 TRANSACTION Trans. Amt 56. 12 Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 58 Database Administration Concurrency Control: n Problem: Ø In a multi-user environment, simultaneous access to data can result in interference and data loss n Solution: Concurrency Control Ø The process of managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multiuser environment. Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 59 Database Administration Concurrency Control: n Issues in Concurrency Control: q Lost Updates • Time Assume that there are two individuals sharing a checking account with a present balance of $400 • One of the individuals deposits $200 • Shortly afterward, one withdraws $150 Balance if Deposit is Lost Event Process Balance 11: 04: 00 Deposit 11: 04: 01 11: 04: 12 11: 10: 26 Withdrawl 11: 12 11: 52 READ BAL = BAL + 200 WRITE BAL READBAL = BAL - 150 WRITE BAL 400. 00 600. 00 450. 00 400. 00 250. 00 Inaccurate Balance Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 60 Database Administration Concurrency Control: n Issues in Concurrency Control: q Uncommitted Data • If A ROLLBACK is to take place, it must occur BEFORE any New Transactions Consider our previous example With the proper ROLLBACK • Time 11: 04: 00 11: 04: 01 11: 04: 12 11: 04: 49 11: 10: 26 11: 12 11: 52 Dr. Peeter Kirs Event Deposit Withdrawl Process READ BAL = BAL + 200 WRITE BAL ** ROLLBACK READBAL = BAL - 150 WRITE BAL Balance 400. 00 600. 00 400. 00 250. 00 Fall, 2003

MIT 5314: Database Applications Slide # 61 Database Administration Concurrency Control: n Issues in Concurrency Control: q Uncommitted Data • Now consider what would occur if the rollback takes place AFTER the second withdrawl Time 11: 04: 00 11: 04: 01 11: 04: 12 11: 10: 26 11: 12 11: 39 11: 52 Dr. Peeter Kirs Event Deposit Withdrawl Process READ BAL = BAL + 200 WRITE BAL READBAL = BAL - 150 ** ROLLBACK WRITE BAL Balance 400. 00 600. 00 450. 00 400. 00 Fall, 2003

MIT 5314: Database Applications Slide # 62 Database Administration Concurrency Control: n Issues in Concurrency Control: q Inconsistent retrievals: • Occur when a transaction calculates results while another operation is taking place Time 11: 04: 00 11: 04: 01 11: 04: 15 11: 04: 32 Event Deposit Withdrawl Process READ BAL = BAL + 200 BAL = BAL - 150 WRITE BAL Balance 400. 00 600. 00 250. 00 600. 00 Withdrawl Occurs while Deposit Update taking place Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 63 Database Administration Concurrency Control: n Transaction Scheduling • • Establishes the order in which concurrent transactions are processed Interleaves (meshes) the execution of database operations to ensure serializability • Bases actions on time stamping and locking techniques (to be explained) • Attempts to Optimize CPU usage by not having the CPU wait for a WRITE to occur after a READ • In our previous examples, transactions would be written to the log, and a read/write would not be processed until the previous transactions write was processed Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 64 Database Administration Concurrency Control: n Locking • • • Most common technique to achieve serialization Guarantees exclusive use of data items to a current transaction The Lock denies access (update) to another transaction until the previous transaction is committed • Locks prevent another transaction from reading inconsistent data • DBMSs automatically enforce locking procedures through the use of a Lock Manager Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 65 Database Administration Concurrency Control: n Locking q Lock Granularity: • • The level at which the data is locked Database Level: • Entire database is locked • No transaction can access the data until the previous transaction has been committed • Preferable for batch operations • Inadequate for multi-user databases User A requests data from Table 1 (Database Locked) User A Commits or aborts (Database Unlocked) Dr. Peeter Kirs Current Database Table 1 User B requests data from Table 2 (Wait ---- Database Locked) User B Transaction initiated Fall, 2003

MIT 5314: Database Applications Slide # 66 Database Administration Concurrency Control: n Locking q Lock Granularity: • Table Level: • • Only the table accessed by a transaction is locked Less restrictive, but still inadequate for multi-user databases User A requests data from Table 2 (Table 2 Locked) User A Commits or aborts (Table 2 Unlocked) Dr. Peeter Kirs Table 1 Table 2 User B requests data from Table 1 (OK ---- Table Available) User C requests data from Table 2 (Wait ---- Table Locked) User C Transaction initiated Fall, 2003

MIT 5314: Database Applications Slide # 67 Database Administration Concurrency Control: n Locking q Lock Granularity: • Page Level: • • A Page is a pre-specified amount of data (4 K, 8 K, etc. ) which is read into memory from the database (stored on the disk) Allows for some multi-user transactions, but requires detailed checking (i. e. , are the records requested by a transaction being used by a previous transaction) Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 68 Database Administration Concurrency Control: n Locking q Lock Granularity: • Record Level: • ONLY the record requested is locked • All other records are available for subsequent transactions • Generally suitable for most multi-user systems • Field Level: • • Only the individual field accessed is locked Excellent for multi-user systems --- BUT --- • Dr. Peeter Kirs Requires involved programmatic checking Fall, 2003

MIT 5314: Database Applications Slide # 69 Database Administration Concurrency Control: n Locking q Lock Types: • All locks are Binary: They are either locked or unlocked • Regardless of level of granularity, if locked the data is unavailable to other transactions q Shared Locks (S-Locks): • Multiple users can read, but NOT update, data • If data is S-Locked, an X-Lock (Below) can not be placed on it q Exclusive Locks (X-Locks): • Data can NOT be accessed, even for reading, by other users • If X-Locked, no other lock type can be placed on it Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 70 Database Administration Concurrency Control: n Deadlock • Impasse resulting from two or more transactions locking the same data at the same time • Each must wait for the other to unlock the data • Assume 2 people share a checking account and both try to withdraw money from an ATM at the same time: George Balance Read: $700. 00 ATM Accessed: S-Lock Placed Time: 1: 31: 45 1: 32: 00 1: 32: 15 ATM Accessed: S-Lock Placed Withdrawl Request --- DENIED --- 1: 32: 30 Balance Read: $700. 00 1: 32: 45 1: 33: 15 Wait (DEADLOCK) Withdrawl Request --- DENIED --- Laura Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 71 Database Administration Concurrency Control: n Deadlock Management q Deadlock Prevention • • When accessed, all records necessary are X-Locked Other users must wait for the records to be released q Deadlock Detection/Resolution • The DBMS periodically scans for deadlocks • If detected, one of the transactions is ‘backed-out’ Any transactions made during the deadlock are aborted When he resources become unlocked, the process is restarted • • (Note that this requires additional Computer Resources) Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 72 Database Administration Concurrency Control: n Deadlock Management q Time Stamping • UNIQUE, MONOTONIC (i. e. , increasing) time applied to each transaction • One time stamp for last read • • (additional record fields required) Read time stamp can not precede update time stamp • • Dr. Peeter Kirs One time stamp for last update Transaction is aborted and rescheduled Transaction submitted for processing in order of time stamp Fall, 2003

MIT 5314: Database Applications Slide # 73 Database Administration Concurrency Control: n Versioning (Optimistic Management) • • Assumes that in most cases the same record will NOT be accessed concurrently OR will simply be read Each time a record is requested, the DBMS creates a new record ‘version’ Any changes made are made to the DB version • The changed version is compared to the original • • If no conflicts exist, the version is accepted • Otherwise, the changes are aborted, and the system is rolled-back Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 74 Database Administration Concurrency Control: n Versioning (Optimistic Management) • Consider our previous example Check Against Original George Commit ATM Accessed 1: 31: 45 Balance Read: $700. 00 1: 32: 00 ATM Accessed 1: 32: 15 Withdraw $200 New Balance $500 1: 32: 30 1: 32: 45 1: 33: 15 1: 33: 24 Balance Read $700. 00 Withdraw $300 New Balance $400 Laura Check Against (new) Original Rollback and Restart Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 75 Database Administration ? ? ? Any Questions ? ? ? You Moron!! I know Evrythin!!! Dr. Peeter Kirs Fall, 2003

MIT 5314: Database Applications Slide # 76 Database Administration Dr. Peeter Kirs Fall, 2003