Ch. 10 Database Transactions
Transaction support provided by?
provided by COMMIT and ROLLBACK
Database recovery
restores a database from a given, usually inconsistent state to a previous consistent state
When are deadlocks possible?
Deadlocks are only possible if at least one of the transactions wants to obtain an exclusive (X) lock on a data item
How do incomplete, improper transactions affect database integrity?
Hurt database integrity
Program is abnormally terminated Equivalent to?
SQL crashes, equivalent to ROLLBACK
Field / attribute level locking
allows concurrent transactions to access the same row, as long as they are accessing different fields within the row
Time stamping 2 properties?
assigns global unique time stamp to each transaction, must be unique and monotonic
Pessimistic approach to concurrency control Examples/
based on the assumption that majority of database operations conflict, and something will go wrong Ex: locks, timestamps
Optimistic approach to concurrency control Acceptable for?
based on the assumption that majority of database operations do not conflict. *Acceptable for:* read/query DB with few update transactions.
Why does SQL code represent a transaction?
because database was accessed, not all transactions update the database
Write Phase of Optimistic Approach
changes are permanently applied to DB
Successful transaction
changes database from one consistent state to another
Reached end of program Equivalent to?
closed SQL developer, equivalent to COMMIT
Serializability
concurrent execution of several transactions yields consistent results
Natural disasters
critical event including fires, earthquakes, floods, power failures
Isolation
data used during transaction can't be used by a second transaction until the first transaction is completed
Consistency
database is permanently consistent before and after transaction
Disc block
directly accessible section of the disk, usually less than a table, several rows within table
Redundant transaction logs
ensures physical disk failure will not impair ability to recover
Uniqueness in transactions
ensures that no equal time stamp values can exist
Monotonicity
ensures that time stamp values always increase (transactions don't start at the same time)
Write ahead log protocol
ensures transaction logs are written before data is updated (DB can later be recovered to a consistent state using data in log)
Database level locking
entire database is locked, preventing use of any table in DB by T2 when T1 is being executed
If transaction can't be completed (DB recovery management)
entire transaction is aborted, any changes made to DB are rolled back
Database request
equivalent of a single SQL statement within an application program or transaction
Lock
guarantees exclusive use of a data item to a current transaction
2 Phase locking guarantees?
guarantees serializability, but does not prevent deadlocks
Reached ROLLBACK statement indicates
indicates failure/unsuccessful end of transaction, all changes are aborted, database reverts to previous consistent state, all locks are released
Reached COMMIT statement indicates
indicates successful end of transaction, all changes permanently recorded in database, all locks are released
Lock granularity
indicates the level of lock use
Serializable schedule
interleaved execution of transactions yields same results as some serial execution
Transaction log
keeps track of all transactions that modify the database
Hardware/software failures
most common, any hardware/software problem that causes data to be overwritten, deleted, or lost (hard disk media failure, bad capacitor on motherboard, failing memory bank, OS error)
Deadlock
occurs when 2+ transactions simultaneously wait on each other to release a lock before continuing
Database checkpoints
operations where DBMS writes all its updated buffers to disk
Deferred-write / deferred update
operations within a transaction don't immediately update the physical database, only the transaction log is updated
Transaction log stores (specific data)
*-* A record for the beginning of the transaction *-* Ending of the transaction (COMMIT) *-*Type of operation being performed (update, delete, insert) *-*Names of objects affected by transaction *-* Before and after values for any updated fields *-* Pointers to previous and next transaction log entries for the same transaction
Rules Governing 2 Phase locking
*1.* 2 transactions cannot have conflicting locks *2.* No unlock operation can precede a lock operation in the same transaction *3.* No data is affected until all locks are obtained
Phases of Optimistic Approach
*1.* Read *2.* Validation *3.* Write
How do locks affect the 3 concurrency problems (lost update, uncommitted data, inconsistent retrievals) ?
*Lost update:*successfully prevents a lost update, but potential for deadlocks *Uncommitted data:* successfully prevents uncommitted data *Inconsistent retrievals:* successfully prevents inconsistent retrievals, but potential for deadlocks
Binary Lock
2 states locked (1) or unlocked (0), most common type of lock
What in the wait graph signified there is a deadlock?
A cycle
Exclusive / write lock (X) How many transactions can hold an X lock on a data source?
Access is specifically reserved for the transaction that locked object, must be used when potential for conflict exists 1 transaction
Shared / read lock (S) How many transactions can hold an S lock on a data source?
Concurrent transactions are granted read access on the basis of a common lock 2+ transactions
Write through / immediate update
DB is immediately updated by transaction operations during transaction's execution, even before it reaches its commit point
What does the DBMS do to transaction log when a system failure occurs?
DBMS examines transaction log for all uncommitted/incomplete transactions, restores (ROLLBACK) database to previous state based on log information
Page level locking
DBMS locks a disc block
Detection technique to control deadlocks
DBMS periodically tests DB for deadlocks, a victim (transaction) is rolled back if a deadlock is found
What does the DBMS do to the transaction log when the recovery process is completed?
DBMS writes all committed transactions not physically written to the database before the failure in the log
What technique is recommended when there is a low probability of deadlocks?
Detection
How does time stamping prevent deadlocks (infinite waiting) ?
Each resource has a timeout value. If a resource is not granted before the timeout expires, the transaction is rolled back.
Interleaving execution of database operations requirements
Ensure serializability and isolation
Can deadlock conditions exist among shared locks?
No
Wound/Wait Scheme If requestor is older? If requestor is younger?
Older transaction rolls back younger transaction and reschedules it *If requestor is older:* older transaction will pre-empt(wound) younger transaction by rolling it back. Younger transaction get rescheduled with original timestamp. *If requestor is younger:* younger requestor waits until older transaction finishes
Wait/Die Scheme If requestor is older? If requestor is younger?
Older transaction waits for the younger transaction to finish. *If requestor is older:* it'll wait until younger transaction finishes *If requestor is younger:* requestor will be rolled back (die), and rescheduled with original time stamp
What technique is recommended when there is a high probability of deadlocks?
Prevention
Techniques to control deadlocks
Prevention, detection, avoidance
Transaction sequence must continue until
Reached COMMIT statement, ROLLBACK statement, end of program, or program is abnormally terminated
In a wait for graph, the transactions are the _____
Transactions are the nodes
Transaction
a logical unit of work that must either be entirely completed or entirely aborted ("all or nothing"), must past the ACID test
Prevention technique to control deadlocks
a transaction requiring a new lock is aborted when there is a possibility that a deadlock can occur
Consistent state
all data integrity constraints are satisfied
Atomicity
all operations of a transaction must be completed (or none of the transactions are complete)
Row level
allows concurrent transactions to access different rows of the same table
Scheduler
special DBMS program that establishes order of operations for executing concurrent transactions
Table level locking
table is locked, preventing access to any row by T2 when T1 is using the table (other tables are available)
Database buffers
temporary storage areas in primary memory
Why are locks required?
to prevent another transaction from reading inconsistent data
Growing phase
transaction acquires all required locks without unlocking any data
ACID test
transaction is atomic, consistent, isolated, durable
Validation Phase of Optimistic Approach
transaction is validated to ensure changes made won't affect integrity and consistency of DB. If it receives a positive result, it goes to the next phase. If it receives a negative result, discard changes and restart transaction
Avoidance technique to control deadlocks
transaction must obtain all needed locks before it can be executed
Read Phase of Optimistic Approach
transaction reads DB, executes its computations, and makes updates to a private copy of DB values in a temporary update file which isn't accessed by other transactions
Shrinking phase
transaction releases all locks and cannot obtain any new lock
Requestor
transaction that wants a resource another transaction holds
Durability
transactions can't be undone after being committed
Human caused incidents
unintentional or intentional critical event
Pessimistic locking
use of locks based on the assumption that conflict between transactions is likely (something is likely to go wrong)