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)