Notes 8- Transaction

What is the Difference Between Checkpoints and Log Files?

*Checkpoints* -Point of synchronization between database and log file. All buffers are force-written to secondary storage. -Checkpoint record is created containing identifiers of all active transactions. -When failure occurs, redo all transactions that committed since the checkpoint and undo all transactions active at time of crash *Log Files* -Log file may be duplexed or triplexed. -Log file sometimes split into two separate random-access files. -Potential bottleneck; critical in determining overall performance.

What is are the Three Type of Optimistic Methods?

*Optimistic Method* -Based on assumption that conflict is rare and more efficient to let transactions proceed without delays to ensure serializability. -At commit, check is made to determine whether conflict has occurred. -If there is a conflict, transaction must be rolled back and restarted. -Potentially allows greater concurrency than traditional protocols. *Read* -Extends from start until immediately before commit. -Transaction reads values from database and stores them in local variables. Updates are applied to a local copy of the data. *Validation* -Follows the read phase. -For read-only transaction, checks that data read are still current values. If no interference, transaction is committed, else aborted and restarted. -For update transaction, checks transaction leaves database in a consistent state, with serializability maintained. *Write* -Follows successful validation phase for update transactions. -Updates made to local copy are applied to the database.

Difference Between Transaction & Consistency

*Transaction* -Action, or series of actions, carried out by user or application, which reads or updates contents of database. -Logical unit of work on the database. -Application program is series of transactions with non-database processing in between. -Transforms database from one consistent state to another, although consistency may be violated during transaction *Consistency* -Can have one of two outcomes: -Success - transaction commits and database reaches a new consistent state. -Failure - transaction aborts, and database must be restored to consistent state before it started. Such a transaction is rolled back or undone. -Committed transaction cannot be aborted. -Aborted transaction that is rolled back can be restarted later

What are Deadlocks?

-An impasse that may result when two (or more) transactions are each waiting for locks held by the other to be released. -Only one way to break deadlock: abort one or more of the transactions. -Deadlock should be transparent to user, so DBMS should restart transaction(s). -However, in practice DBMS cannot restart aborted transaction since it is unaware of transaction logic even if it was aware of the transaction history (unless there is no user input in the transaction or the input is not a function of the database state).

What is the Deadlock Detection and Recovery used for?

-DBMS allows deadlock to occur but recognizes it and breaks it. -Usually handled by construction of wait-for graph (WFG) showing transaction dependencies: --Create a node for each transaction. --Create edge Ti -> Tj, if Ti waiting to lock item locked by Tj. -Deadlock exists if and only if WFG contains cycle. -WFG is created at regular intervals.

What are Deadlock Prevention?

-DBMS looks ahead to see if transaction would cause deadlock and never allows deadlock to occur. -Could order transactions using transaction timestamps: --Wait-Die - only an older transaction can wait for younger one, otherwise transaction is aborted (dies) and restarted with same timestamp. --Wound-Wait - only a younger transaction can wait for an older one. If older transaction requests lock held by younger one, younger one is aborted (wounded).

Describe the Uncommitted Dependency Problem?

-Occurs when one transaction can see intermediate results of another transaction before it has committed. -T4 updates balx to £200 but it aborts, so balx should be back at original value of £100. -T3 has read new value of balx (£200) and uses value as basis of £10 reduction, giving a new balance of £190, instead of £90. -Problem avoided by preventing T3 from reading balx until after T4 commits or aborts.

Describe the Inconsistent Analysis Problem?

-Occurs when transaction reads several values but second transaction updates some of them during execution of first. -Sometimes referred to as dirty read or unrepeatable read. -T6 is totaling balances of account x (£100), account y (£50), and account z (£25). -Meantime, T5 has transferred £10 from balx to balz, so T6 now has wrong result (£10 too high). -Problem avoided by preventing T6 from reading balx and balz until after T5 completed updates.

What is Concurrency Control?

-Process of managing simultaneous operations on the database without having them interfere with one another. -Prevents interference when two or more users are accessing database simultaneously and at least one is updating data. -Although two transactions may be correct in themselves, interleaving of operations may produce an incorrect result.

Describe the process of Database Recovery?

-Process of restoring database to a correct state in the event of a failure. -Need for Recovery Control --Two types of storage: volatile (main memory) and nonvolatile. --Volatile storage does not survive system crashes. --Stable storage represents information that has been replicated in several nonvolatile storage media with independent failure modes.

Describe the Lost update Problem?

-Successfully completed update is overridden by another user. -T1 withdrawing £10 from an account with balx, initially £100. -T2 depositing £100 into same account. -Serially, final balance would be £190. -Loss of T2's update avoided by preventing T1 from reading balx until after update.

What are the Types of Failures?

-System crashes, resulting in loss of main memory. -Media failures, resulting in loss of parts of secondary storage. -Application software errors. -Natural physical disasters. -Carelessness or unintentional destruction of data or facilities. -Sabotage.

What is a Timeout in Deadlocks?

-Transaction that requests lock will only wait for a system-defined period of time. -If lock has not been granted within this period, lock request times out. -In this case, DBMS assumes transaction may be deadlocked, even though it may not be, and it aborts and automatically restarts the transaction.

What are the Properties in a transaction?

Four basic (ACID) properties that define a transaction are: -*Atomicity* 'All or nothing' property. -*Consistency*: Must transform database from one consistent state to another. -*Isolation* -Partial effects of incomplete transactions should not be visible to other transactions. -*Durability*:Effects of a committed transaction are permanent and must not be lost because of later failure.