Logical unit of work that must be entirely completed or aborted
Can consist of multiple database requests - multiple SQL statements
Consistent database state
All integrity constraints are satisfied
Database request
Single SQL statement
Transaction defined by user or programmer
Problems with transactions? Use Rollback to go back to a previous consistent
state
Transaction Properties (ACIDS)
Atomicity
Single, indivisible, logical unit of work
All parts completed, or none
Consistency
Database in a consistent state after a transaction is processed
Isolation
2 transactions cannot use same data at same time
Durability
After Commit, data from transaction is permanently in the database
Serializability
Processing of transactions concurrently yields consistent results
As if they were processed serially
Needed with concurrent transactions
Transaction Management with SQL
Possible transaction finish
COMMIT is reached
ROLLBACK is reached
Program ends successfully (like COMMIT)
Program terminated (like ROLLBACK)
A Transaction
UPDATE PRODUCT
SET PROD_QOH = PROD_QOH – 2
WHERE PROD_CODE = ‘1558-QW1’;
UPDATE CUSTOMER
SET CUST_BALANCE = CUST_BALANCE + 87.98
WHERE CUST_NUMBER = ‘10011’;
COMMIT;
Transaction Log
See Table 10.1 for an example - know attributes used
Roll back or forward
Log = DB; critical for recovery so store it on multiple disks
Concurrency Control
Ensures serializability
Three problems that can occur without this control
Lost updates
To updates to row X at same time
Last update saved is recorded; other one is lost
Uncommitted data
Transaction (T2) reads uncommitted data
Violates isolation principle
After this, other transaction (T1) is rolled back so T2 is working with
incorrect data
Inconsistent retrievals
Query with calculations
Data being updated during query
Query reads some data after it has been updated and other data before
the update
Scheduler
Schedules transactions for efficiency, not on FCFS basis
Ensures Serializabilty and Isolation of transactions
Temporary state of inconsistency?
During a transaction
Conflicting transactions work on same data
One transaction must be a Write
Use these methods to implement concurrency control
Locking
Time stamping
Optimistic
Concurrency Control with Locking Methods
A lock guarantees exclusive use of data
Lock acquired by transaction prior to data access
Lock Manager
Granularity:
Database; Table; Page; Row; Attribute
Locks at Database and Table levels are too restrictive
Locks – Page Level
Page/diskpage/diskblock = addressable part of disk
Most commonly used
Locks – Row Level
Would be ok but easier to implement page-level locks
Field-level locks?
Too difficult to implement
Types:
Binary or Shared/Exclusive
Binary
Restrictive as only 2 states: Locked or Unlocked
Two Read Transactions could result in data locked
Shared/Exclusive
Exclusive
When possibility of conflict
Issued if no lock on data
Mutual exclusive rule
Only one transaction at a time can have an exclusive lock
on an object
Shared used for Reads
Issued if no exclusive lock
Overhead with locks
Must know type of lock
Operations must be supported (READ_LOCK, WRITE_LOCK, UNLOCK)
Schema must allow lock upgrade/downgrade
Locks can lead to 2 major problems
Transactions not serializable
Deadlocks
Two-Phase Locking
Ensures serializability
Growing and shrinking phase
Rules for Two-Phase Locking
Two transactions cannot have conflicting locks
No Unlock can precede a Lock in same transaction
No data changed until all locks obtained
Locking can lead to deadlocks
Deadlocks (Deadly Embrace)
T1 needs X and Y
T2 needs Y and X
So each transaction has locked something that the other one needs
Deadlock Solutions
Deadlock Prevention
Abort transaction if chance of deadlock
Reschedule
Deadlock Detection
If deadlock found, Victim is aborted
Deadlock Avoidance
Must obtain all locks before execution
Concurrency Control with Time Stamping Methods
Time Stamps
Global unique number
Uniqueness
Monotonicity means that values always increase
All operations in transaction have same time stamp
Conflicting operations executed in time stamp order
If transactions conflict, abort one
Disadvantage
Each value has a Stamp for last Read and last Write
Which Transaction rolled back? Note that in examples below that one transaction
has already got a lock and then the other transaction is requesting a lock
Wait/Die
Older requests: Older waits until younger finishes and releases
lock
Younger requests: Younger dies and is rescheduled
Wound/Wait
Older requests: Older wounds (rollbacks) younger
Younger requests: Younger waits until older finishes and releases
lock
Concurrency Control with Optimistic Methods
Three phases
Read
And write private copy and temp. update file
Validate
Write
Best for query databases
Database Recovery Management
Based on atomicity principle
Problems? Rollback the effects of the transaction
Backups still needed
Types
Full Backup
Differential Backup
Transaction Log Backup
Backup stored …
Read causes of failures, p.417
Recovery of a complete database
Latest Backup + log
Roll forward
Transaction Recovery
Write-ahead-log protocol
Date to log before data written to database
Redundant transaction logs
Buffers and performance
Data updated in buffers
Then a bunch of data is written to disk at one time, not a case
of writing each individual update
Checkpoints
Suspends processing new requests
Data to disk
Log and database in sync.
Transaction Recovery using Deferred write (deferred update)
Log updated; Disk updated when COMMIT
Recovery
Last checkpoint is key
Transaction committed before last checkpoint
Transaction committed after last checkpoint – use log’s
after values
Transactions rolled back after last checkpoint
Transaction Recovery using Write-through (immediate update)
Data to disk before COMMIT
Recovery
Last checkpoint is key
Transaction committed before last checkpoint
Transaction committed after last checkpoint – redo (after
values)
Transactions rolled back after last checkpoint –rollback -
use log’s before values