Please enable JavaScript.
Coggle requires JavaScript to display documents.
Transaction Management (TM) & Concurrency Control (CC) - Coggle…
Transaction Management (TM) &
Concurrency Control (CC)
What is a Transaction
Logical unit of work
Must be either completed or aborted
No intermediate states are acceptable
Transaction Properties
Atomicity: all or nothing
Consistency: permanence of database's consistent state
Durability: changes can't be undone or lost
Serializability: same result as some serial execution
Insolation: incomplete results not revealed
SQL Transaction Management Sequence
TM is about managing transaction properties
Transaction must continue until:
COMMIT statement is reached
ROLLBACK statement is reached
END of a program is reached
PROGRAM reaches abnormal termination
Log helps with this sequence
Transaction Log
Tracks all transactions that update database
May be used by ROLLBACK command
May be used to recover from system failure
Log stores
Record for begining of transaction
Each SQL statement
Commit statement
Serialisability: The concurrent interleaved execution of transactions will produce the same result as some serial execution of those same transaction
Potential problems in multiuser environments
Lost updates
Uncommitted data
Inconsistent retrievals
Lost updates
Caused because transactions are running concurrently
There wouldn't be a problem if they were executed serially
Uncommitted data
Occurs when the transaction property of isolation is not implemented
The partial results of a transaction are not hidden other transations see this, & if the data is not finally committed it causes a problem
Solutions for problems caused in multiuser environments
The Scheduler (software)
Establishes order of concurrent transaction execution
Interleaves execution of database operations to ensure serialisability
Concurrency control solutions
Locking methods
Lock guarantees current transaction exclusive use of data item
Acquires lock prior to access
Lock released when transaction is completed
Managed by lock manager (software)
Must obey Two-Phase Lock Protocol
Two-Phase Locking
Growing phase & Shrinking phase
Rules:
Two transactions cannot have conflicting locks
No unlock operation can precede a lock operation in the same transaction
No data are affected until all locks are obtained**
Time stamping
Various Types of Lock Granularity
Lock Granularity
Corse: Fewer locks, Better performance, Less availabiliy
Fine: More locks, Poor performance, More availability
Database (Coarse)
File
Page
Record
Field (Fine)
Shared Locks
Exists when concurrent transactions granted READ access
Produces no conflict for read-only transactions
Issued when transaction wants to read & exclusive lock not held on item
Exclusicve Locks
Exists whe access reserved for locking transaction
Used when potential for conflict exists
Issued when transaction wants to update unlocked data
Deadlock Problem
Occurs when 2 or more transactions wait for each other to unlock data
Called deadly embrace
Control techniques
Deadlock prevention
Deadlock detection
Deadlock avoidance
The Dining Philosophers:
Deadlock is not just btwn 2 transactions, but whenever there is a circular wait graph of transactions
Database Recoverty Management
Restores a database to previously consistent state
Based on the atomic transaction property
Level of backup
Full backup
Differential
Transaction log
Transaction Recovery
Deferred-write and Deferred-update
Changes are written to the transaction log
Database updated after transaction reaches commit point
Write-through
Immediately updated during execution
Before the transaction reaches its commit point
Transaction log also updated
Transaction fails, database uses log information
to ROLLBACK