RDBMS – Day4
Agenda
• Transaction
• Types of Transaction Systems
• Transaction Properties
• Requirements for an OLTP system
– Integrity
– Concurrency
• Locking
• Granularity of Locking
• Intent Locking
• Deadlock
• Database Recovery
ER/CORP/CRS/DB07/003
Copyright © 2004, 2
Infosys Technologies Ltd Version No: 2.0
Transaction
Logical unit of program execution that takes a database from one
consistent state to another consistent state
ER/CORP/CRS/DB07/003
Copyright © 2004, 3
Infosys Technologies Ltd Version No: 2.0
State diagram of a Transaction
BEGIN
While executing
A ctive
After the final When normal
statement execution can‟t
has been proceed
executed
P artially Failed
completed
After rolling
back and
restoration to
Afte r
previous state
successful
completion
Committed A borte d
ER/CORP/CRS/DB07/003
Copyright © 2004, 4
Infosys Technologies Ltd Version No: 2.0
Transaction Systems
• Batch Transaction Processing System
• On-Line Transaction Processing System
• Real time Transaction Processing System
ER/CORP/CRS/DB07/003
Copyright © 2004, 5
Infosys Technologies Ltd Version No: 2.0
Transaction Properties
• Atomicity
• Consistency
• Isolation
• Durability
ER/CORP/CRS/DB07/003
Copyright © 2004, 6
Infosys Technologies Ltd Version No: 2.0
Requirements for an OLTP system
• Integrity
• Concurrency
ER/CORP/CRS/DB07/003
Copyright © 2004, 7
Infosys Technologies Ltd Version No: 2.0
Integrity
Integrity
– Refers to :
• Correctness and completeness of data
• Validity of individual items
• Preservation of interrelationships in the DB
– Data integrity constraints:
• Required data
• Domain integrity
• Entity integrity
• Referential integrity
ER/CORP/CRS/DB07/003
Copyright © 2004, 9
Infosys Technologies Ltd Version No: 2.0
Required data
• Requires a column to contain Non-NULL values
• Indicated with the key word NOT NULL in create statement
An example:
CREATE TABLE EMPLOYEE (
EMP_NO integer NOT NULL,
EMP_NAME varchar (15) ,
EMP_AGE integer,
…….
…………
ER/CORP/CRS/DB07/003
Copyright © 2004, 10
Infosys Technologies Ltd Version No: 2.0
Domain Integrity-Check constraint
• Specify a range of values that a column can take.
• Used to specify business rules.
• Specified in the create statement
An example:
CREATE TABLE EMPLOYEE (
EMP_NO integer NOT NULL,
……………
……….
EMP_LOC varchar(15)
CHECK ( EMP_LOC in ( „BANGALORE‟,
‟BOMBAY‟,‟DELHI‟)));
ER/CORP/CRS/DB07/003
Copyright © 2004, 11
Infosys Technologies Ltd Version No: 2.0
Entity Integrity
• The database models the outside world
• A table‟s primary key should have unique values so that it
represents some real world entity
• The requirement that the primary key be unique is called “Entity
integrity constraint”
ER/CORP/CRS/DB07/003
Copyright © 2004, 12
Infosys Technologies Ltd Version No: 2.0
Referential Integrity
• Ensures that the integrity of the parent-child relationship between
tables created by primary and foreign keys is preserved
• Issues:
• Inserting a new child row
• Updating foreign key in a child row
• Updating the primary key in a parent row
• Deleting a parent row
ER/CORP/CRS/DB07/003
Copyright © 2004, 13
Infosys Technologies Ltd Version No: 2.0
Concurrency
Concurrency
• Two or more users access a database concurrently
• Problems associated with concurrent execution:
– Lost update
– Dirty read
– Non repeatable read
– Phantom records
• Concurrency techniques:
– Locking
– Time stamping
ER/CORP/CRS/DB07/003
Copyright © 2004, 15
Infosys Technologies Ltd Version No: 2.0
Lost update
Time Hilary's Deposit Balance Kevin's Deposit
10:22 Read Balance (1500) 1500
10:23 Balance=1500+2000
10:24 Read Balance (1500)
10:25 Write new Balance (3500) 3500
10:26 Commit
10:27 Balance=1500+1800
10:28 3300 Write new Balance (3300)
10:29 Commit
ER/CORP/CRS/DB07/003
Copyright © 2004, 16
Infosys Technologies Ltd Version No: 2.0
Dirty Read
Time Hilary's Deposit Balance Kevin's Deposit
11:22 Read Balance (1500) 1500
11:23 Balance=1500+2000
11:24 Write new Balance (3500) 3500
11:25 Read Balance (3500)
11:26 Rollback
11:27 Balance= 3500 + 1800
11:28 5300 Write new Balance (5300)
11:29 Commit
ER/CORP/CRS/DB07/003
Copyright © 2004, 17
Infosys Technologies Ltd Version No: 2.0
Incorrect summary
Time Hilary's Transfer Balance Summary Transaction
Read Hilary’s Balance
12:22 (1500) 1500 Sum = 0
12:23 Balance=1500-500 Read Hilary's Balance (1500)
Write new Balance
12:24 (1000) 1000
12:25 Sum=Sum + Balance (1500)
Read Evelyn's Balance
12:26 (1500) 1500
12:27 Balance=1500 + 500
Write new Balance
12:28 (2000) 2000
12:29 Commit 2000
12:30 Read Evelyn's Balance (2000)
12:31 Sum=Sum + Balance (3500)
12:32 3500 Write Sum (3500)
12:33 Copyright © 2004,
Commit
ER/CORP/CRS/DB07/003
18
Infosys Technologies Ltd Version No: 2.0
Phantom Record
Time Create account Total Accounts
13:22
13:23
Read the total number of
13:24 accounts in the bank as total
Create account for
Simon with a deposit
13:25 of 500
Create account for
Mike with a deposit of
13:26 1000
13:27
13:28 Commit
13:29 Write Total
13:30 Commit
ER/CORP/CRS/DB07/003
Copyright © 2004, 19
Infosys Technologies Ltd Version No: 2.0
Solution to the Problems – Serialization…
• To make every transaction follow each other.
• Achieved by setting following rules on transactions:
– If any row is being modified, then do not allow any other transaction
either to read or write that row until the first transaction completes.
– If a transaction is reading a particular row, prevent other
transactions from making any changes to that row until the first
transaction completes.
– If a transaction is reading some data, do not allow any other
transaction to insert new rows into the same table until the first
transaction completes. This will avoid problems like phantom
records.
ER/CORP/CRS/DB07/003
Copyright © 2004, 20
Infosys Technologies Ltd Version No: 2.0
Solution to the Problems – Serialization.
• Serialization can be achieved using:
– Locking
– Time stamping
ER/CORP/CRS/DB07/003
Copyright © 2004, 21
Infosys Technologies Ltd Version No: 2.0
Locking
• A lock is a variable
associated with each
data item in a
database.
• When updated by a
transaction, DBMS
locks the data item
• serializability could
be maintained by
this.
• Lock could be
Shared or Exclusive
• An example -> Copyright © 2004, ER/CORP/CRS/DB07/003
22
Infosys Technologies Ltd Version No: 2.0
Granularity of locks
• The granular level at which a resource can be locked
• A database consists of several items that form a hierarchy. For
example, the general hierarchy is:
– Database
– Tablespace
– Table
– Row
ER/CORP/CRS/DB07/003
Copyright © 2004, 23
Infosys Technologies Ltd Version No: 2.0
Intent Locking
• Only the intention of locking is expressed at the ancestor node of the
required resource.
• Also called Parent Child locking.
• Increase the concurrency.
ER/CORP/CRS/DB07/003
Copyright © 2004, 24
Infosys Technologies Ltd Version No: 2.0
Types of Intent Locking
• IS (Intent Share)
• IX (Intent Exclusive)
• SIX (Shared Intent Exclusive)
ER/CORP/CRS/DB07/003
Copyright © 2004, 25
Infosys Technologies Ltd Version No: 2.0
Lock Compatibility matrix
ER/CORP/CRS/DB07/003
Copyright © 2004, 26
Infosys Technologies Ltd Version No: 2.0
Deadlock
• Occurs when two or more separate processes compete for resources
held by one another.
T1 T2
Write_lock A
… action(s)
Read_lock B
… action(s)
Write_lock B
WAIT Read_lock A
T1 must wait for WAIT
T2 to release lock
T2 must wait for T1 to release lock
ER/CORP/CRS/DB07/003
Copyright © 2004, 27
Infosys Technologies Ltd Version No: 2.0
Deadlock- Example
Time Transaction BalanceUpdate Transaction LoanUpdate
10:22 Lock ACC_DETAILS Lock LOAN_DETAILS
10:23 Update ACC_DETAILS Update LOAN_DETAILS
10:24 Try for lock on LOAN_DETAILS Try for lock on ACC_DETAILS
10:25 Wait for lock Wait for lock
10:26 Wait for lock Wait for lock
10:27 Wait for lock Wait for lock
10:28 Wait for lock Wait for lock
ER/CORP/CRS/DB07/003
Copyright © 2004, 28
Infosys Technologies Ltd Version No: 2.0
Security of Data
• USERID and PASSWORD to restrict the users from acquiring an un-authorized
access
• Grant and Revoke statements (Data Control Language) to provide restricted
access control to resources like Tables
• Database views to restrict access to sensitive data
• Encryption of data to avoid un-authorized access
ER/CORP/CRS/DB07/003
Copyright © 2004, 29
Infosys Technologies Ltd Version No: 2.0
Failure types
• Transaction failure
• System crash
• Disk failure
ER/CORP/CRS/DB07/003
Copyright © 2004, 30
Infosys Technologies Ltd Version No: 2.0
Storage Types
• Volatile
– Is fast
– E.g. Main memory,
Cache
• Non-Volatile
– Survives system crashes
– E.g. disk, magnetic tapes
• Stable
– Theoretically never fails
– E.g. RAID, remote back
up systems etc.
ER/CORP/CRS/DB07/003
Copyright © 2004, 31
Infosys Technologies Ltd Version No: 2.0
The Transaction Log
• Most important structure used to recover from database failures
• Contains the before image and after image of the data item modified
• Contains log records for each update activity
• Each log record contains:
– Transaction identifier
– Data item identifier
– Old value
– New value
• Start and commit are indicated with special records
ER/CORP/CRS/DB07/003
Copyright © 2004, 32
Infosys Technologies Ltd Version No: 2.0
Techniques to maintain log files
• Deferred Update
• Immediate Update
ER/CORP/CRS/DB07/003
Copyright © 2004, 33
Infosys Technologies Ltd Version No: 2.0
Deferred Update Scheme (1of 2)
• Database modifications are written in the log,
• The WRITE operation is deferred until a transaction Partially Commits
• A transaction is said to be partially committed when it completes its final action
• Before the actual updates on the database begins, it must be ensured that all
log records are written to the stable storage
• Once all the updates are done on the database the transaction enters the
„committed‟ state.
ER/CORP/CRS/DB07/003
Copyright © 2004, 34
Infosys Technologies Ltd Version No: 2.0
Deferred Update Scheme (2 of 2)
• The recovery scheme executes redo( Ti) and sets the value of all the data
items modified by Ti to the new value as found in the log
• This is done only for those transactions for which there is a
record present in the log
ER/CORP/CRS/DB07/003
Copyright © 2004, 35
Infosys Technologies Ltd Version No: 2.0
START
Deferred Update
Update Record in
Memory
Update in Logs
NO Has System YES
crashed?
Restart System
Is transaction YES YES Do you find
comitted? commit in log?
NO Make changes NO
permanent in
database using
log
Discard Log data
STOP
ER/CORP/CRS/DB07/003
Copyright © 2004, 36
Infosys Technologies Ltd Version No: 2.0
Deferred DB Modification
Log Records Database
Read(A,a1)
a1=a1-50
T1 Write(A,a1)
Read(B,b1)
b1=b1+50
Write(B,b1) A=950
A=1000
Commit
B=2000
B=2050
T2 read(C,c1)
c1=c1-100
Write(C,c1) C=600
C=700
Commit
ER/CORP/CRS/DB07/003
Copyright © 2004, 37
Infosys Technologies Ltd Version No: 2.0
Occurrence of failure (Example)
CRASH
Recovery: No Action
CRASH
CRASH
Recovery: redo(T0)
Recovery:Redo(T0),
Redo(T1)
ER/CORP/CRS/DB07/003
Copyright © 2004, 38
Infosys Technologies Ltd Version No: 2.0
Immediate update scheme (1 of 2)
• Allows the modifications to be output to the database while the transaction is
in the ACTIVE state.
• This uses two procedures :UNDO and REDO
ER/CORP/CRS/DB07/003
Copyright © 2004, 39
Infosys Technologies Ltd Version No: 2.0
Immediate update scheme (2 of 2)
• The transaction is undone, if the log contains but does not contain
• The transaction need to be redone, if the log contains both the records and
ER/CORP/CRS/DB07/003
Copyright © 2004, 40
Infosys Technologies Ltd Version No: 2.0
Example
T1
Read(A,a1) A= 1000
a1=a1-50 B = 2000
Write(A,a1)
Read(B,b1)
b1=b1+50
Write(B,b1)
read(C,c1)
c1=c1-100
T2 C=700
Write(C,c1)
ER/CORP/CRS/DB07/003
Copyright © 2004, 41
Infosys Technologies Ltd Version No: 2.0
START
Immediate Update
Update Record in
Memory
Update in Logs
Update Database
on disk
NO Has System YES
crashed?
Restart System
Is transaction YES YES Do you find
comitted? commit in log?
NO NO
Make changes
permanent
Undo changes in Undo changes in
database using Discard Log data database using
log log
STOP
ER/CORP/CRS/DB07/003
Copyright © 2004, 42
Infosys Technologies Ltd Version No: 2.0
Immediate DB Modification
Log Records Database
Read(A,a1)
a1=a1-50
A=950
A=1000
Write(A,a1)
Read(B,b1) B=2050
B=2000
T1
b1=b1+50
Write(B,b1)
Commit
read(C,c1)
T2 C=600
C=700
c1=c1-100
Write(C,c1)
Commit
ER/CORP/CRS/DB07/003
Copyright © 2004, 43
Infosys Technologies Ltd Version No: 2.0
Occurrence of failure (Example)
CRASH
Recovery: undo(T0)
CRASH
CRASH
Recovery:
undo(T1) Recovery:
redo(T0) Redo(T0)
Redo(T1)
ER/CORP/CRS/DB07/003
Copyright © 2004, 44
Infosys Technologies Ltd Version No: 2.0
Checkpoints
• Problem
– Searching process is time consuming
– Most transactions might have to be redone
• The system regularly performs checkpoints
• The transactions are not allowed to perform any updates while the
checkpoints are in progress.
• The presence of a checkpoint allows the system to streamline its recovery
process
ER/CORP/CRS/DB07/003
Copyright © 2004, 45
Infosys Technologies Ltd Version No: 2.0
Check Point Scenario
ER/CORP/CRS/DB07/003
Copyright © 2004, 46
Infosys Technologies Ltd Version No: 2.0
T1 Log File
Commits
T1: ABC
T1
T2: ABC
T1: ABC T4: AB
A B C T2
T2: AB T3 : ABC
T4: AB
Commits T2: C
T2 T3: ABC
Database
A B C T5: AB Database
T3 T3
Commits A B C
T1 : No Changes
T4 T2 : Redo C
A B T3 : Redo ABC
T5 T4 : Undo AB
T5 : Discard AB
A B
T4
No Commit
Start Check Point Memory Crash
T5 No
Commit
Recovery from crash
ER/CORP/CRS/DB07/003
Copyright © 2004, 47
Infosys Technologies Ltd Version No: 2.0
Summary
• Transaction is a logical unit of work which takes the database from one
consistent state to the other
• Atomicity, consistency, isolation and durability are the ACID properties of a
transaction
• Data integrity and Security are enforced using SQL DDL statements
• Transactions should be able to concurrently execute without affecting the
consistency of the database
• Locking is a mechanism of achieving such controlled concurrency
• Database recovery is based on the concept of shadowing or logging
• Log based recovery scheme could be deferred or immediate
ER/CORP/CRS/DB07/003
Copyright © 2004, 48
Infosys Technologies Ltd Version No: 2.0
Thank You!
ER/CORP/CRS/DB07/003
Copyright © 2004, 49
Infosys Technologies Ltd Version No: 2.0