Transaction Management
Module 5
ISM 104 / CSE 2251/DSE 2252
What is a Transaction ?
• A logical unit of work that must be either
completed or aborted
• Intermediate states are not acceptable
• Changes the database from one consistent
state to another.
Module 3 : Transaction Management and
Concurrency Control
• Insert Card
• Enter password – Validate – if Successful
• Home screen
• Select withdraw
• Enter amount
• Check balance – successful
• Dispense cash
• Ask pref receipt
Module 3 : Transaction Management and
Concurrency Control
Transaction Properties
• Atomicity
• Durability
• Serializability
• Isolation
Module 3 : Transaction Management and
Concurrency Control
What is Transaction Management
• Comprises of unit of work performed within a
DBMS
Module 3 : Transaction Management and
Concurrency Control
Transaction Management and SQL
• Standards defined by ANSI to govern sql
statements
• Two sql statements that provide transaction
support are :
– COMMIT
– ROLLBACK
Module 3 : Transaction Management and
Concurrency Control
ANSI Standards
• Transaction sequence is initiated it must
continue all succeeding sql statements until
one of the following is event occurs :
– COMMIT
– ROLLBACK
– End of Program successfully reached
– Program abnormally aborted
Module 3 : Transaction Management and
Concurrency Control
Significance of Transaction Log (TL)
• DBMS Maintains this log which records all the
transactions that have updated the database.
• Used by the DBMS for
– Recovery Requirement
– System Failure
• RDBMS use TL for roll forward
Module 3 : Transaction Management and
Concurrency Control
What info the TL stores
•• A database log record is made up of
• Log Sequence Number: A unique id for a log record. With
LSNs, logs can be recovered in constant time. Most logs'
LSNs are assigned in monotonically increasing order, which
is useful in recovery algorithms, like ARIES.
• Prev LSN: A link to the last log record. This implies database
logs are constructed in linked list form.
• Transaction ID number: A reference to the database
transaction generating the log record.
• Type: Describes the type of database log record.
• information about the actual changes that triggered the log
record to be written
Module 3 : Transaction Management and
Concurrency Control
Types of database log records
• All log records include the general log attributes discussed, and also other attributes depending on
their type (which is recorded in the Type attribute, as above).
• Update Log Record notes an update (change) to the database. It includes this extra information:
– PageID: A reference to the Page ID of the modified page.
– Length and Offset: Length in bytes and offset of the page are usually included.
– Before and After Images: Includes the value of the bytes of page before and after the page change. Some
databases may have logs which include one or both images.
• Compensation Log Record notes the rollback of a particular change to the database. It includes this
extra information:
– undoNextLSN: This field contains the LSN of the next log record that is to be undone for transaction that
wrote the last Update Log.
• Commit Record notes a decision to commit a transaction.
• Abort Record notes a decision to abort and hence rollback a transaction.
• Checkpoint Record notes that a checkpoint has been made. These are used to speed up recovery.
They record information that eliminates the need to read a long way into the log's past. :
– redoLSN: This is a reference to the first log record that corresponds to a dirty page. i.e. the first update that
wasn't flushed at checkpoint time. This is where redo must begin on recovery.
– undoLSN: This is a reference to the oldest log record of the oldest in-progress transaction. This is the oldest
log record needed to undo all in-progress transactions.
• Completion Record notes that all work has been done for this particular transaction. (It has been
fully committed or aborted)
Module 3 : Transaction Management and
Concurrency Control
Concurrency Control
• Coordination of simultaneous execution of
transactions in a multiprocessing dbs
• Objective is to ensure serializability of
transaction in multiuser environment
Module 3 : Transaction Management and
Concurrency Control
Major Problems
• Data integrity and consistency problems
• Three problems :
– Lost update
– Uncommitted transaction
– Inconsistent retrieval
Module 3 : Transaction Management and
Concurrency Control
Lost update
• Scenario :
– We have a product table attribute QOH whose
current value is 35. assume two concurrent
transactions being executed T1(purchase of 100
units) and T2( sell 30 units) that update the table
Module 3 : Transaction Management and
Concurrency Control
Lost updates
Time Transaction Step Stored Value
1 T1 Read QOH 35
2 T2 Read QOH 35
3 T1 QOH=35+100
4 T2 QOH=35-30
5 T1 Write QOH(LUPD) 135
6 T2 Write QOH 5
Module 3 : Transaction Management and
Concurrency Control
Uncommitted Data
Time Transaction Step Stored Value
1 T1 Read QOH 35
2 T1 QOH=35+100 35
3 T1 Write QOH 135
4 T2 Read QOH (read UCD) 135
5 T2 QOH=135-30
6 T1 ROLLBACK 35
7 T2 Write QOH 105
Module 3 : Transaction Management and
Concurrency Control
Inconsistent Retrieval
• A Transaction calculates some aggregate
function over a set of data while other
transactions are updating the data
Module 3 : Transaction Management and
Concurrency Control
The Scheduler
• This establishes the order in which the
operations within concurrent transactions are
executed.
• Operations carried by the scheduler :
– Ensures serializability by Interleafing the execution
of database operations
– Uses locking or time stamp methods for
concurrency control
Module 3 : Transaction Management and
Concurrency Control
Lock
• Guarantees exclusive use of data item to a
current transaction.
• Lock information is managed by the lock
manager, responsible for assigning and
policing locks used by transactions.
Module 3 : Transaction Management and
Concurrency Control
Lock Granularity
• Indicates the levels of lock use
• Levels :
– Database Level Lock
– Table Level Lock
– Page Level Lock
– Row Level Lock
– Field Level Lock
Module 3 : Transaction Management and
Concurrency Control
Lock types
• Binary Locks
• Shared / Executive Locks
Module 3 : Transaction Management and
Concurrency Control
Two Phase Locking
• This protocol defines the technique for
acquiring and relinquishing locks.
• The two phases are :
– Growing phase : - transaction acquires all required
locks without unlocking any data. Once all locks
have been acquired the transaction is in its locked
point.
– Shrinking phase : in this state transactions release
all locks and cannot obtain a new lock.
Module 3 : Transaction Management and
Concurrency Control
Two Phase Locking
• Rules followed by two phase locking protocol :
– Two transactions cannot have conflicting locks
– No unlock operation can precede a lock operation
in the same transaction
– No data are effected until all locks are obtained.
Module 3 : Transaction Management and
Concurrency Control
Dead Lock
• when two or more transactions are each
waiting for each other to release a resource.
• Techniques to control dead locks
– Dead Lock Prevention
– Dead Lock Detection
– Dead Lock Avoidance
Module 3 : Transaction Management and
Concurrency Control
Database Security
• Security Violations
– Unauthorized reading of data
– Unauthorized modification of data
– Unauthorized destruction of data
• Security can be implemented as follows :
– Authorization
– Audit trails
– Authorization in SQL is done through privileges
– Encryption and Authentication