CIS275 - Chapter 6: Transaction Management Flashcards
a sequence of database operations that must be either completed or rejected as a whole.
transaction
Partial execution of a transaction results in inconsistent or incorrect data.
Ex: The debit-credit transaction transfers funds from one bank account to another. The first operation removes funds, say $100, from one account, and the second operation deposits $100 in another account. If the first operation succeeds but the second fails, $100 is mysteriously lost. The transaction must complete and save either both operations or neither operation.
Saving complete transaction results in the database is called a _____.

commit

- Initially, Sam Snead has $1,000 in account B and $2,900 in account C.
- The transaction changes balances and commits. Changes are saved in the database.
- If the operating system, database, or application fails during transaction, the transaction must roll back.
- The database updates account B, detects failure, and restores account B to the initial value $1,000.
- After rollback, the transaction terminates. The final operation is not executed, so account C remains $2,900.
Rejecting an incomplete transaction is called a _____k.

rollback

A rollback reverses the transaction and resets data to initial values. A variety of circumstances cause a rollback:
The operating system detects a device failure. Ex: Magnetic disk fails during execution of a transaction, and transaction results cannot be written to the database.
The database detects a conflict between concurrent transactions. Ex: Two airline customers attempt to reserve the same seat on a flight.
The application program detects an unsuccessful database operation. Ex: In the debit-credit transaction, funds are removed from the debit account, but the credit account is deleted prior to deposit.
When a failure occurs, the database is notified and rolls back the transaction. If the failure is temporary, such as intermittent network problems, the database attempts to restart the transaction. If the failure is persistent, such as a deleted bank account, the databases ‘kills’ the transaction permanently.
- Initially, Sam Snead has $1,000 in account B and $2,900 in account C.
- The transaction changes balances and commits. Changes are saved in the database.
- If the operating system, database, or application fails during transaction, the transaction must roll back.
- The database updates account B, detects failure, and restores account B to the initial value $1,000.
- After rollback, the transaction terminates. The final operation is not executed, so account C remains $2,900.


All transactions must be atomic, consistent, isolated, and durable, commonly called the _____ properties:
ACID
In an _____ transaction, either all or none of the operations are executed and applied to the database.
atomic
Partial or incomplete results are rolled back, and the database returns to its state prior to execution of the transaction.
In a _____ transaction, all rules governing data are valid when the transaction is committed.
consistent
Completed transactions that violate any rules are rolled back.
Consistency applies to both universal and business rules. Universal rules apply to all relational data. Ex: Primary keys must be unique and not NULL. Business rules are particular to a specific database or application. Ex: Funds must not be lost in a debit-credit transaction.
An _____ transaction is processed without interference from other transactions.
isolated
Isolated transactions behave as if each transaction were executed one at a time, or serially, when in fact the transactions are processed concurrently.
Computers usually process multiple transactions concurrently. Multiple processors, or cores, in a single computer might work on multiple transactions in parallel. A single processor might switch to a new transaction while waiting for an active transaction to read or write data.
Concurrent transactions that access the same data might conflict. Ex: One transaction sums all salaries while another increases all salaries by 10%. If both transactions run concurrently, the sum might include some increased salaries but not others, and thus the sum might be invalid. To ensure transactions are isolated, databases must prevent conflicts between concurrent transactions.
A _____ transaction is permanently saved in the database once committed, regardless of system failures.
durable
System failures potentially cause the loss of transaction data after the transaction is committed. Ex: An application commits a transaction, the transaction data is written to blocks in memory, but hardware fails before the blocks are saved on magnetic disk. Because transaction results are lost, the transaction is not durable.
The ACID properties are supported in two database subsystems. The _____ enforces atomic and durable transactions.
recovery system
Both the recovery and concurrency systems, along with other database components, support consistency.

The ACID properties are supported in two database subsystems. The _____ enforces isolated transactions.
concurrency system
Both the recovery and concurrency systems, along with other database components, support consistency.



In a _____, a transaction reads data that is subsequently rolled back.
dirty read

Ex:
- T2 updates data X.
- T1 reads the updated value of X before T2 commits.
- T2 fails and is rolled back.
Since T1 reads a value that is eventually rolled back, the result of T1 is invalid.
In a _____, a transaction repeatedly reads changing data.
nonrepeatable read

Ex:
- T1 reads data X.
- T2 updates X.
- T1 rereads X.
If T1 incorrectly assumes the value of X is stable, the result of T1 is invalid.
In a _____, one transaction inserts or deletes a table row that another transaction is reading.
phantom read
Ex:
- T1 begins reading table rows.
- T2 inserts a new row into the table.
- T1 continues reading table rows.
Since T1 sees or misses the new row, depending on precisely when T2 writes the row to the database, the result of T1 is unpredictable.




- Transaction 1 reserves a seat, but the internet fails before a confirmation message is sent.
- Transaction 1 commits, but the confirmation is not delivered.
- Transaction 2 reserves a seat, and the confirmation message is delivered.
- Transaction 2 is rolled back. The seat is not reserved, so the confirmation is invalid.




Expected:
Atomic, Consistent, Durable, Isolated
(a) This transaction writes incomplete results to the database. An atomic transaction must write complete results or none at all.
(b) A foreign key that does not match the corresponding primary key violates referential integrity. Referential integrity is a universal rule. The result of a consistent transaction must conform to both universal and business rules.
(c) The drive failure causes changes to be lost after the transaction commits. However, the result of durable transactions must be permanent.
(d) Isolated transactions run as if no other transactions are running at the same time. Once one transaction updates account A’s balance, account A should be unavailable to any other transaction.

Expected:
Set 1:
T1 executes a dirty read.
T2 executes without a conflict.
T3 executes without a conflict.
Set 2:
T1 executes a phantom read.
T2 executes without a conflict.
T3 executes without a conflict.
Set 1: T1 increases account A based on a value that is rolled back by T3. Since T1 reads a value that is subsequently rolled back, T1 executes a dirty read. T2 reads after T3’s rollback.
Set 2: T3 deletes account A while T1 is reading accounts. As a result, the average account size may be invalid. So, T1 executes a phantom read. T2 reads before T3 deletes.

Expected:
Set 1:
T1 executes without a conflict.
T2 executes a nonrepeatable read.
T3 executes without a conflict.
Set 2:
T1 executes without a conflict.
T2 executes without a conflict.
T3 executes a dirty read.
Set 1:
T2 reads account A once to compute sum and again, if sum is greater than expected, adds $10 to all accounts. In between the two reads, T3 changes account A. Since T2 repeatedly reads changing data, T2 executes a nonrepeatable read. T1 reads after T2 and T3 commit.
Set 2:
T3 increases account A based on a value that is rolled back by T2. Since T3 reads a value that is subsequently rolled back, T3 executes a dirty read. T1 reads after T2’s rollback.

Expected:
Set 1:
T1 executes without a conflict.
T2 executes a phantom read.
T3 executes without a conflict.
Set 2:
T1 executes without a conflict.
T2 executes without a conflict.
T3 executes without a conflict.
Set 1: T3 deletes account A while T2 is reading accounts. As a result, the average account size may be invalid. So, T2 executes a phantom read. T1 reads after T3 deletes.
Set 2: T1, T2, and T3 handle different accounts, so no conflict.

Expected:
Set 1:
T1 executes without a conflict.
T2 executes a nonrepeatable read.
T3 executes without a conflict.
Set 2:
T1 executes without a conflict.
T2 executes without a conflict.
T3 executes without a conflict.
Set 1: T2 reads account A once to compute sum and again, if sum is greater than expected, adds $10 to all accounts. In between the two reads, T3 changes account A. Since T2 repeatedly reads changing data, T2 executes a nonrepeatable read. T1 reads after T2 and T3 commit.
Set 2: T1 reads account A once to compute sum and again, if sum is greater than expected, to adds $10 to all accounts. Account A does not change in between reads, so no conflict.
a sequential order of operations for multiple transactions.
transaction schedule
Operations for different transactions can be interleaved so transactions run concurrently. Operations for individual transactions must occur in the correct order.













































































