recovery & concurrency control. what is a transaction? a transaction is a logical unit of work...
TRANSCRIPT
Recovery & Concurrency Control
What is a Transaction? A transaction is a logical unit of work that
must be either entirely completed or aborted. 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.
Example of Transaction
X = 100
X = 50
X = X - 50
Amount in stock = X
Initial State <Consistent State>
Final State <Consistent State>
Transaction A <Modifies database>
Transaction ACID Properties
AtomicAll operations of a transaction be completed; if not, the
transaction is aborted.Transaction cannot be subdivided
ConsistentConstraints don’t change from before transaction to after
transaction Isolated
Data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.
Database changes not revealed to users until after transaction has completed
DurableDatabase changes are permanentThe permanence of the database’s consistent state.
Transaction Management with SQL
Transaction support is provided by 2 SQL statements: COMMIT ROLLBACK.
When a transaction sequence is initiated, it must continue through all succeeding SQL statements until one of the following four events occurs:A COMMIT statement is reached.A ROLLBACK statement is reached.The end of a program is successfully reached
(COMMIT).The program is abnormally terminated (ROLLBACK).
Transaction Management with SQL
Example:UPDATE PRODUCTSET PROD_QOH = PROD_QOH - 200WHERE PROD_CODE = ‘QS123XY’;
UPDATE ACCT_RECEIVABLESET ACCT_BALANCE = ACCT_BALANCE + 10000WHERE ACCT_NUM = ‘12345678’;
COMMIT;
Concurrency Control Problem–in a multi-user environment,
simultaneous access to data can result in interference and data loss
Solution–Concurrency ControlConcurrency ControlThe 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 multi-user environment
Concurrency Control The objective of concurrency control is to
ensure the serializability of transactions in a multi-user database environment.
Simultaneous execution of transactions over a shared database can create several data integrity and consistency problems:Lost Updates.Uncommitted Data.Inconsistent retrievals.
Lost Updates
Lost Updates Using Product Table: Product’s quantity on Hand
(PROD_QOH) Two concurrent transactions update PROD_QOH:
See Table 1 for the serial execution under normal circumstances.
See Table 2 for the lost update problems resulting from the execution of the second transaction before the first transaction is committed.
Lost Updates
Uncommitted Data Data are not committed when two transactions T1
and T2 are executed concurrently and the first transaction is rolled back after the second transaction has already accessed the uncommitted data - thus violating the isolation property of the transaction.
Uncommitted Data
Correct Execution Of Two Transactions
An Uncommitted Data Problem
Inconsistent Retrieval Inconsistent retrievals occur when a transaction
calculates some summary (aggregate) functions over a set of data while other transactions are updating the data.
Example:T1 calculates the total quantity on hand of the
products stored in the PRODUCT table.At the same time, T2 updates the quantity on hand
(PROD_QOH) for two of the PRODUCT table’s products.
Inconsistent Retrieval
Inconsistent Retrieval
Inconsistent Retrieval
Concurrency Control Techniques
Serializability Finish one transaction before starting another
Locking Mechanisms The most common way of achieving
serializationData that is retrieved for the purpose of
updating is locked for the updaterNo other user can perform update until
unlocked
Updates with locking (concurrency control)
Locking Mechanisms Locking level:
Database–used during database updatesTable–used for bulk updatesBlock or page–very commonly usedRecord–only requested row; fairly commonly usedField–requires significant overhead; impractical
Database Lock
Table Lock
Block or Page Lock
Record Lock
Locking Mechanisms Types of locks:
Shared lock (S locks/read locks)○ Allows other transactions to read but not update Allows other transactions to read but not update
a record or other resourcesa record or other resources○ Read but no update permitted. Used when just
reading to prevent another user from placing an exclusive lock on the record
Exclusive lock (X locks/write locks)○ Prevents another transactions from reading and Prevents another transactions from reading and
therefore updating a record until it is locked.therefore updating a record until it is locked.○ No access permitted. Used when preparing to
update
Deadlock
Managing Deadlock Deadlock prevention:
Lock all records required at the beginning of a transaction
Two-phase locking protocol○ Growing phase○ Shrinking phase
May be difficult to determine all needed resources in advance
Deadlock prevention Two-phase locking protocol
Growing phase○ A transaction acquires all the required locks without
unlocking any data○ Once all locks have been acquired, the transaction is in its
locked pointShrinking phase
○ A transaction releases all locks and cannot obtain any new lock
Two-phase locking protocol
Managing Deadlock Deadlock Resolution:
Allow deadlocks to occur nut build mechanisms into the DBMS for detecting and breaking the deadlocks.
The DBMS maintains a matrix of resource usage.○ Indicates what subjects (users) are using what
objects (resources)○ Scanning this matrix: the computer can detect
deadlock if occur○ DBMS resolve deadlock: One of the transactions is
aborted: changes made by the transaction up to the time of deadlock are removed & transaction is restarted when the required resources become available.
Versioning
Optimistic approach to concurrency control Instead of locking Assumption is that simultaneous updates will
be infrequent Each transaction can attempt an update as it
wishes The system will reject an update when it
senses a conflict Use of rollback and commit for this
Figure 13-15 The use of versioning
Better performance than locking
Data Dictionaries and Repositories
Data dictionaryDocuments data elements of a databaseStore metadata/information about the databaseActive: managed by DBMSPassive: managed by the user(s)Part of system catalogSystem catalog
○ System-created database that describes all database objects
○ Table-related data (table names), table creators/owners, column names, data types, etc
Data Dictionaries and Repositories
Information RepositoryStores metadata that describe an organization’s
data and data processing resources, manages the total information processing environment and combines information about an organization's business information and its application portfolio
Information Repository Dictionary System (IRDS)Software tool that is used to manage and control
access to the information repository 3 components of repository system
architecture:Information ModelRepository EngineRepository Database
Figure 13-16 Three components of the repository system architecture
A schema of the repository information
Software that manages the repository objects
Where repository objects are stored
Source: adapted from Bernstein, 1996.
Data Availability Downtime is expensive
Lost business during the outageCost of catching up when service is restoredLegal costsPermanent loss of customer loyalty
How to ensure availabilityHardware failures–provide standby componentsLoss of data–database mirroringHuman Error – SOP, training, documentationMaintenance downtime–automated and non-disruptive
maintenance utilitiesNetwork problems–careful traffic monitoring, firewalls, and
routers
Information in this slides were taken from Modern Database Management Information in this slides were taken from Modern Database Management System, Ninth edition by Jeffrey A.Hoffer, Mary B.Prescott & Heikki Topi. System, Ninth edition by Jeffrey A.Hoffer, Mary B.Prescott & Heikki Topi. AND Database Systems, Design, Implementation & Management by Peter AND Database Systems, Design, Implementation & Management by Peter Rob & Carlos CoronelRob & Carlos Coronel