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.
Within a schedule, two operations in different transactions _____ when the relative order of the operations affects the outcome:
conflict
Operations conflict when one operation reads and another writes the same data. The relative order of the read and write affects the outcome.
Operations do not conflict when both read, but neither writes, the same data. The relative order of the two reads does not affect the outcome.
_____ contain the same transactions with all conflicting operations in the same order.
Equivalent schedules
Equivalent schedules always have the same result.
- The schedule has transactions T and T, each with a sequence of operations.
- Operations read X and write X conflict because the order of the read and write operations affects the final outcome.
- In a conflicting schedule, conflicting operations are in a different order. The final value of Z is different in the conflicting schedule.
- Operations read X and read Y do not conflict.
- In an equivalent schedule, non-conflicting operations may be in a different order, but conflicting operations are in the same order. The final value of Z is the same.
_____ contain the same transactions with some conflicting operations in different order.
Conflicting schedules
Conflicting schedules can potentially have different results.
- The schedule has transactions T and T, each with a sequence of operations.
- Operations read X and write X conflict because the order of the read and write operations affects the final outcome.
- In a conflicting schedule, conflicting operations are in a different order. The final value of Z is different in the conflicting schedule.
- Operations read X and read Y do not conflict.
- In an equivalent schedule, non-conflicting operations may be in a different order, but conflicting operations are in the same order. The final value of Z is the same.
a schedule in which transactions are executed one at a time.
serial schedule
Serial schedules have no concurrent transactions. Every transaction begins, executes, and commits or rolls back before the next transaction begins. All transactions in a serial schedule are isolated.
Any schedule that is equivalent to a serial schedule is a _____.
serializable schedule
A serializable schedule can be transformed into a serial schedule by switching the relative order of reads in different transactions.
Serializable schedules generate the same result as the equivalent serial schedule. Therefore, concurrent transactions in a serializable schedule are isolated.
The SQL standard defines four isolation levels:
- SERIALIZABLE transactions run in a serializable schedule with concurrent transactions. Isolation is guaranteed.
- REPEATABLE READ transactions read only committed data. After the transaction reads data, other transactions cannot update the data. REPEATABLE READ prevents most types of isolation violations but allows phantom reads.
- READ COMMITTED transactions read only committed data. After the transaction reads data, other transactions can update the data. READ COMMITTED allows nonrepeatable and phantom reads.
- READ UNCOMMITTED transactions read uncommitted data. READ UNCOMMITTED processes concurrent transactions efficiently but allows a broad range of isolation violations, including dirty, nonrepeatable, and phantom reads.
In a ______, one or more transactions cannot be rolled back.
nonrecoverable schedule
In a _____, rollback of one transaction forces rollback of other transactions.
cascading schedule
In a _____, rollback of one transaction never forces rollback of other transactions.
strict schedule
Expected:
20, 8, 8, conflicting, conflicting, equivalent
Schedule A’s T1 writes a new value for X, 6 + 4 = 10, before T2 reads X. So, Z = 10 * 2 = 20.
Schedule B computes Z using the initial value of X, which is 4. So, Z = 4 * 2 = 8.
Schedule C computes Z as Schedule B does. So, Z = 8.
A and B do not have the same final Z values, so are conflicting schedules. Same for A and C.
All read and write pairs in B are in the same order as C. As a result, the final Z values are the same. So, B and C are equivalent schedules.
a database component that manages concurrent transactions.
concurrency system
The concurrency system implements isolation levels while attempting to optimize overall database performance.
A ____ is permission for one transaction to read or write data.
lock
Concurrent transactions are prevented from reading or writing the same data. A transaction takes locks when the transaction needs to read or write data. A transaction releases locks when the transaction is committed or no longer needs the locked data.
A _____ allows a transaction to read, but not write, data.
shared lock
Concurrent transactions can hold shared locks on the same data.
An _____ allows a transaction to read and write data.
exclusive lock
When one transaction holds an exclusive lock, no concurrent transaction can take a shared or exclusive lock on the same data.
the collection of data reserved by a lock.
Lock scope
Lock scope is often a single row, allowing other transactions to access other rows in the same table. If a transaction needs access to multiple rows, lock scope might be a block or the entire table. Since transactions also read and write indexes, lock scope might be an index entry, index block, or entire index.
a component of the concurrency system that tracks, grants, and releases locks.
lock manager
a specific locking technique that ensures serializable transactions.
Two-phase locking
Three variations of two-phase locking are common in relational databases:
Basic two-phase locking
Strict two-phase locking
Rigorous two-phase locking
All three variations prevent conflicts and ensure serializable transactions. Strict and rigorous also prevent cascading rollbacks while basic does not. Rigorous is easier to implement than strict, but less efficient, since rigorous holds shared locks longer.
Concurrency systems in most relational databases implement strict two-phase locking.
_____ has expand and contract phases for each transaction, also known as grow and shrink phases.
Basic two-phase locking
In the expand phase, the transaction can take, but not release, locks. In the contract phase, the transaction can release, but not take, locks.
_____ holds all exclusive locks until the transaction commits or rolls back.
Strict two-phase locking
The expand phase is the same as in basic two-phase locking, but the contract phase releases only shared locks.
- Rollback of T1 forces rollback of T2, so the schedule is cascading.
- With strict two-phase locking, exclusive locks are not released during the contract phase. The exclusive lock is held until T1 rolls back.
- T2 read must wait until the exclusive lock is released.
- Rollback of T1 does not cascade to T2.
_____ holds both shared and exclusive locks until the transaction commits or rolls back.
Rigorous two-phase locking
In effect, rigorous two-phase locking has no contract phase.
a state in which a group of transactions are frozen.
Deadlock
In a deadlock, all transactions request access to data that is locked by another transaction in the group. None of the transactions can proceed. Ex:
T1 takes an exclusive lock on X.
T2 takes an exclusive lock on Y.
T1 requests a shared lock on Y.
T2 requests a shared lock on X.
T1 waits for T2 to release the lock on Y. T2 waits for T1 to release the lock on X. The transactions are deadlocked.
- The schedule has three transactions that read and write X, Y, and Z.
- Transactions first take shared locks when reading, then request exclusive locks when writing.
- Since T2 has a shared lock on Y, the T1 exclusive lock request for Y waits for T2 to commit and release the shared lock.
- Similarly, T2 waits for T3 to release the shared lock on Z, and T3 waits for T1 to release the shared lock on X.
- The dependency cycle causes deadlock.
A _____ is waiting for data locked by another transaction.
dependent transaction
A _____ of dependent transactions indicates deadlock has occurred.
cycle
Ex: T1 depends on T2, which depends on T3, which depends on T1. The transactions are deadlocked.
Concurrency systems manage deadlock with a variety of techniques:
_____. Each transaction requests all locks when the transaction starts. If all locks are granted, the transaction runs to completion. If not, the transaction waits until other transactions release locks. Either way, the transaction cannot participate in a deadlock.
Aggressive locking
Concurrency systems manage deadlock with a variety of techniques:
_____. All data needed by concurrent transactions is ordered, and each transaction takes locks in order. Taking locks in order prevents deadlock.
Data ordering
Ex: The sequence above is modified so that locks on X precede locks on Y:
T1 takes an exclusive lock on X.
T2 requests a shared lock on X.
T1 takes a shared lock on Y.
T2 takes an exclusive lock on Y.
Request 2 waits for T1 to release the exclusive lock on X. T1 proceeds and eventually releases the lock on X. Now request 2 is granted and T2 proceeds.
Concurrency systems manage deadlock with a variety of techniques:
When waiting time for a lock exceeds a fixed period, the transaction requesting the lock rolls back. Alternatively, the concurrency system compares transaction start times and rolls back the later transaction. The timeout period is set by the database or configured by the database administrator.
Timeout
Concurrency systems manage deadlock with a variety of techniques:
The concurrency system periodically checks for cycles of dependent transactions. When a cycle is detected, the concurrency system selects and rolls back the ‘cheapest’ transaction. The cheapest transaction might, for example, have the fewest rows locked or most recent start time. The rollback breaks the deadlock.
Cycle detection
_____ execute concurrent transactions without locks and, instead, detect and resolve conflicts when transactions commit.
Optimistic techniques
Optimistic techniques are effective when conflicts are infrequent, as in analytic applications with many reads and few updates.
_____ creates a private copy of all data accessed by a transaction, called a snapshot, as follows:
Snapshot isolation
- Create a snapshot when the transaction starts.
- Apply updates to the snapshot rather than the database.
- Prior to commit, check for conflicts. A conflict occurs when concurrent transactions write the same data. Reads do not cause conflicts.
- If no conflict is detected, write snapshot updates to the database (commit).
- If a conflict is detected, discard the snapshot and restart the transaction (roll back).
Since snapshot isolation does not take locks, transactions never wait. However, transactions occasionally restart after all operations are processed.
- Under snapshot isolation, the transactions take snapshots and process in parallel.
- Since the transactions write different data, no conflict is detected. Both transactions commit.
- Under snapshot isolation, neither transaction waits.
- Under two-phase locking, T1 waits for T2 to commit and release the shared lock on Y.
_____ extends standard snapshot isolation and ensures serializable schedules when isolation level is set to SERIALIZABLE.
Serializable snapshot isolation
- The serial schedule commits and results in the same value for X and Y.
- The non-serializable schedule contains conflicting operations in a different order than the serializable schedule.
- The non-serializable schedule contains a cycle and results in deadlock under two-phase locking.
- Under snapshot isolation, no conflict is detected, and the transactions commit.
- Snapshot isolation results in different values for X and Y.
The recovery system must manage three failure scenarios:
A _____ results in a rollback.
transaction failure
The application program may initiate a rollback due to logical errors. The database system may initiate rollback due to deadlock or insufficient disk space. The operating system may initiate rollback if a hardware or software component fails. Regardless of the cause, the recovery system restores all data changed by the transaction to the original values.
The recovery system must manage three failure scenarios:
A _____ includes a variety of events resulting in the loss of main memory.
system failure
Databases initially write to main memory blocks, which are lost when an application, the operating system, or the database system fails. Blocks are subsequently saved on storage media, which normally survive a system failure. If main memory is lost before blocks are written to storage media, data written by committed transactions might be lost. In this event, the recovery system:
Recovers data written to main memory, but not storage media, by committed transactions.
Rolls back data written to storage media by uncommitted transactions.
The recovery system must manage three failure scenarios:
A _____ failure occurs when the database is corrupted or the database connection is lost.
storage media failure
Many storage systems automatically make redundant copies of data. If one copy of data is corrupted, the storage system automatically switches to a backup copy without intervention by the database or operating system. Nevertheless, storage media do fail occasionally. Alternatively, the connection between the processor and database server might fail. Either way, the database is unavailable to the application.
a file containing a sequential record of all database operations.
recovery log
The log and database are stored on different storage media so the log survives database failures. The recovery system uses the log to restore the database after a failure.
The log contains transaction and data identifiers. Transaction identifiers are assigned by the database system for internal use by the recovery and concurrency systems. Data identifiers correspond to table name, column name, and primary key value, but are compressed or encoded for efficiency.
The recovery log contains four types of records:
An _____ indicates a transaction has changed data. Update records include the transaction identifier, the data identifier, the original data value, and the new data value. Update records may optionally track insert and delete operations.
update record i
The recovery log contains four types of records:
A _______, indicates data has been restored to the original value during a rollback.
compensation record, also known as an undo record
Compensation records include the transaction identifier, the data identifier, and the restored (original) data value. Compensation records are necessary because the log must capture every database change, including changes executed by a rollback.
The recovery log contains four types of records:
A _____ indicates a transaction boundary. Three types of transaction records exist: start, commit, and rollback. Transaction records include the ‘start’, ‘commit’, or ‘rollback’ indicator and the transaction identifier.
transaction record
A _____ indicates that all data in main memory has been saved on storage media.
checkpoint record
When the database executes a checkpoint, transaction processing is suspended while all unsaved data and log records are written to storage media. In the event of a system failure, the recovery system reads only log records following the last checkpoint, rather than the entire file. Checkpoint records include a ‘checkpoint’ indicator along with the identifiers of all transactions that are active (uncommitted) at the time of the checkpoint.
Recovery from a system failure has two phases.
The _____ restores all transactions that were committed or rolled back since the last checkpoint.
redo phase
In the redo phase, the recovery system reads the recovery log forward from the latest checkpoint record. While reading the log, the recovery system maintains a list of active transactions. The list is initialized with active transactions in the checkpoint record. As each log record is read:
Transactions in a ‘start’ transaction record are added to the list.
Transactions in a ‘commit’ or ‘rollback’ transaction record are removed from the list.
Data in an update record is set to the new value.
Data in a compensation record is restored to the original value.
Recovery from a system failure has two phases.
The _____ rolls back transactions that were neither committed nor rolled back.
undo phase
At the end of the redo phase, all transactions remaining on the list have no ‘commit’ or ‘rollback’ records. These transactions are rolled back in the undo phase.
During the undo phase, the recovery system reads the recovery log backwards from the last record. While reading the log, the recovery system maintains a list of incomplete transactions. The list is initialized with active transactions remaining from the redo phase. Each transaction on the list is rolled back:
When an update record is read, data is restored to the original value, and a compensation record is written at the end of the log.
When the ‘start’ transaction record is read, the transaction is removed from the list, and a ‘rollback’ transaction record is written at the end of the log.
The above process is the same as the rollback process for transaction failures, described above.
When the transaction list is empty, the undo phase ends, and recovery is complete.
the percentage of time a system is working from the perspective of the system user.
Availability
Many databases require high availability, in excess of 99% of the time. Ex: A database that manages stock exchange trades must have availability approaching 100%.
Availability is a primary concern in recovery from storage media failures.
The _____ technique periodically creates checkpoints and, while transaction processing is paused, copies the database to backup media.
cold backup
When storage media fails, the recovery system:
Copies the latest backup to the database.
Executes the system failure recovery process beginning at the latest checkpoint.
Depending on the backup size and period length, recovery might require minutes or hours. The database is unavailable during recovery, so the cold backup technique is used when low availability is acceptable.
The _____ technique maintains a secondary database that is nearly synchronized with the primary database.
hot backup
As the primary database processes transactions, log records are sent to and processed by the secondary database. If the databases share a high-speed connection, the secondary database is only moments behind the primary database.
When storage media for the primary database fails, the secondary database becomes primary. This can be accomplished, for example, by swapping the internet addresses of primary and secondary servers. The new primary database is available in a matter of seconds, as soon as outstanding log records are processed.
The ______ statement sets the isolation level for subsequent transactions:
SET TRANSACTION
a series of SQL statements submitted to a MySQL server, beginning when a user or program connects to the server and ending when the user or program disconnects.
SESSION
SESSION sets the isolation level for all transactions in the current session.
_____ sets the isolation level for all transactions submitted to the MySQL server for all subsequent sessions.
GLOBAL
Existing sessions are not affected. The GLOBAL setting can be used only by a database administrator with appropriate MySQL privileges.
the first or last statement of a transaction.
transaction boundary
A transaction boundary is one of three statements:
The START TRANSACTION statement starts a new transaction.
The COMMIT statement commits the current transaction.
The ROLLBACK statement rolls back the current transaction.
In MySQL with InnoDB, the behavior of statements outside a transaction boundary depends on the _____ system variable.
autocommit
When autocommit is ON, individual statements are separate transactions and immediately commit or roll back. When autocommit is OFF, individual statements do not commit immediately. Instead, a START TRANSACTION is executed automatically after each COMMIT and ROLLBACK. The default setting is ON.
System variables like autocommit are assigned a value using a SET statement
COMMIT and ROLLBACK have optional keywords:
_____ overrides the autocommit setting and starts a new transaction, as if a START TRANSACTION were executed. The isolation level of the new transaction is the same as the prior transaction.
_____ ends the current session and disconnects from the server.
AND CHAIN
RELEASE
The _____ statement is identical to START TRANSACTION.
BEGIN
However, BEGIN is easily confused with the BEGIN - END statement pair, which has different meaning in many programming languages. For this reason, the BEGIN statement is not recommended.
- The data administrator sets system autocommit to ON.
- Since autocommit is ON, the UPDATE statement commits immediately.
- START TRANSACTION overrides autocommit. The transaction continues until the COMMIT statement.
- AND CHAIN starts a new transaction.
- Neither AND CHAIN nor START TRANSACTION appear, so the DELETE statement autocommits.
A _____ is a point within a transaction where partial transaction results are saved temporarily.
savepoint
Savepoints prevent redoing work in lengthy transactions. If an error occurs within a transaction after a savepoint, the transaction can restart at the savepoint rather than at the beginning.
Savepoints are managed with three statements using an identifier, the savepoint name:
The SAVEPOINT statement saves internal transaction data and associates the data with the identifier.
The RELEASE SAVEPOINT statement discards the identifier and saved data.
The ROLLBACK TO statement resets transaction data to the savepoint values, restarts processing at the savepoint, and releases all subsequent savepoints.
Savepoints are temporary and internal to a transaction. When the transaction commits, all savepoints are released.
- When the transaction starts, Employee has four rows.
- At savepoint First, one row has been deleted from Employee.
- At savepoint Second, three rows have been deleted from Employee.
- Rollback restores Employee to savepoint First. All savepoint identifiers and partial transaction data are released.
- Transaction commits. Employee table is saved with three rows.
a database block that has been updated in main memory but not yet saved on storage media.
dirty block
A ____ saves dirty blocks and log records, as follows:
checkpoint
- Suspend database processing.
- Write all unsaved log records to the log file.
- Write all dirty blocks to storage media.
- Write a checkpoint record in the log.
- Resume database processing.
Checkpoints enable rapid recovery from system failure, as described elsewhere in this material.
A _____ resumes processing while saving dirty blocks.
fuzzy checkpoint
A fuzzy checkpoint improves database availability but complicates recovery if the system fails while saving dirty blocks.
- The data administrator configures checkpoints at three-second intervals.
- Updates and log records are written to main memory.
- After three seconds, automatic checkpoint flushes dirty blocks and log records to storage media.
- The transaction continues writing data to main memory.
Expected:
transaction 1: READ UNCOMMITTED
transaction 2: SERIALIZABLE
transaction 3: REPEATABLE READ
transaction 4: READ COMMITTED
transaction 5: READ COMMITTED
Transaction 1: The SET TRANSACTION statement overrides the GLOBAL isolation level, so READ UNCOMMITTED.
Transaction 2: The SET TRANSACTION statement overrides the GLOBAL isolation level, so SERIALIZABLE.
Transaction 3: The SET TRANSACTION statement prior to transaction 2 governs only the next transaction, so transaction 3 uses the GLOBAL setting, so REPEATABLE READ.
Transactions 4 and 5: The SESSION isolation level overrides the GLOBAL isolation level for all subsequent transactions in the session, so READ COMMITTED.
Expected:
transaction 1: READ UNCOMMITTED
transaction 2: REPEATABLE READ
transaction 3: SERIALIZABLE
transaction 4: SERIALIZABLE
transaction 5: READ COMMITTED
Transaction 1: The SET TRANSACTION statement overrides the GLOBAL isolation level, so READ UNCOMMITTED.
Transaction 2: The SET TRANSACTION statement prior to transaction 1 governs only the next transaction, so transaction 2 uses the SESSION setting, so REPEATABLE READ.
Transactions 3 and 4: The SESSION isolation level overrides the GLOBAL isolation level for all subsequent transactions in the session, so SERIALIZABLE.
Transaction 5: The SET TRANSACTION statement overrides both SESSION and GLOBAL isolation levels for transaction 5, so READ COMMITTED.
Expected:
transaction 1: READ UNCOMMITTED
transaction 2: SERIALIZABLE
transaction 3: READ COMMITTED
transaction 4: REPEATABLE READ
transaction 5: REPEATABLE READ
Transaction 1: The SET TRANSACTION statement overrides the GLOBAL isolation level, so READ UNCOMMITTED.
Transaction 2: The SET TRANSACTION statement overrides the GLOBAL isolation level, so SERIALIZABLE.
Transaction 3: The SET TRANSACTION statement prior to transaction 2 governs only the next transaction, so transaction 3 uses the GLOBAL setting, so READ COMMITTED.
Transactions 4 and 5: The SESSION isolation level overrides the GLOBAL isolation level for all subsequent transactions in the session, so REPEATABLE READ.
Expected:
transaction 1: READ COMMITTED
transaction 2: SERIALIZABLE
transaction 3: SERIALIZABLE
transaction 4: REPEATABLE READ
transaction 5: SERIALIZABLE
Transaction 1: The SET TRANSACTION statement overrides the GLOBAL isolation level, so READ COMMITTED.
Transactions 2 and 3: The SESSION isolation level overrides the GLOBAL isolation level for all subsequent transactions in the session, so SERIALIZABLE.
Transaction 4: The SET TRANSACTION statement overrides the SESSION isolation level, so REPEATABLE READ.
Transaction 5: The SET TRANSACTION statement prior to transaction 4 governs only the next transaction, so transaction 5 uses the SESSION setting, so SERIALIZABLE.
Expected:
transaction 1: READ COMMITTED
transaction 2: READ COMMITTED
transaction 3: READ UNCOMMITTED
transaction 4: READ COMMITTED
transaction 5: REPEATABLE READ
Transactions 1 and 2: The SESSION isolation level overrides the GLOBAL isolation level for all subsequent transactions in the session, so READ COMMITTED.
Transaction 3: The SET TRANSACTION statement overrides the GLOBAL isolation level, so READ UNCOMMITTED.
Transaction 4: The SET TRANSACTION statement prior to transaction 3 governs only the next transaction, so transaction 4 uses the SESSION setting, so REPEATABLE READ.
Transaction 5: The SET TRANSACTION statement overrides both SESSION and GLOBAL isolation levels for transaction 5, so REPEATABLE READ.
Expected:
transaction 1: READ UNCOMMITTED
transaction 2: REPEATABLE READ
transaction 3: SERIALIZABLE
transaction 4: READ COMMITTED
transaction 5: READ COMMITTED
Transaction 1: The SET TRANSACTION statement overrides the GLOBAL isolation level, so READ UNCOMMITTED.
Transaction 2: The SET TRANSACTION statement overrides the GLOBAL isolation level, so REPEATABLE READ.
Transaction 3: The SET TRANSACTION statement prior to transaction 2 governs only the next transaction, so transaction 3 uses the GLOBAL setting, so SERIALIZABLE.
Transactions 4 and 5: The SESSION isolation level overrides the GLOBAL isolation level for all subsequent transactions in the session, so READ COMMITTED.
Expected:
transaction 1: READ UNCOMMITTED transaction 2: SERIALIZABLE transaction 3: REPEATABLE READ transaction 4: READ COMMITTED transaction 5: READ COMMITTED Transaction 1: The SET TRANSACTION statement overrides the GLOBAL isolation level, so READ UNCOMMITTED. Transaction 2: The SET TRANSACTION statement overrides the GLOBAL isolation level, so SERIALIZABLE. Transaction 3: The SET TRANSACTION statement prior to transaction 2 governs only the next transaction, so transaction 3 uses the GLOBAL setting, so REPEATABLE READ. Transactions 4 and 5: The SESSION isolation level overrides the GLOBAL isolation level for all subsequent transactions in the session, so READ COMMITTED.
Expected:
statement 3: neither start nor end
statement 5: start
statement 7: end
statement 8: start and end
Statement 3 is within a transaction that begins at statement 2 and ends at statement 4, so neither starts nor ends a transaction.
Statement 5 follows COMMIT AND CHAIN, so starts a transaction. Since autocommit is OFF, the transaction does not end until the next START TRANSACTION, COMMIT, or ROLLBACK statement.
Statement 7 precedes the start of a transaction, so ends a transaction.
Statement 8 follows ROLLBACK AND CHAIN, so starts a transaction, and is the last statement in a session, so ends a transaction.
Expected:
statement 3: end
statement 5: start and end
statement 6: start
statement 7: neither start nor end
Statement 3 precedes the start of a transaction, so ends a transaction.
Statement 5 follows COMMIT AND CHAIN, so starts a transaction, and precedes ROLLBACK AND CHAIN, so ends a transaction.
Statement 6 follows START TRANSACTION, so starts a transaction. Since autocommit is OFF, the transaction does not end until the next START TRANSACTION, COMMIT, or ROLLBACK statement.
Statement 7 is within a transaction that begins at statement 6 and ends at statement 8, so neither starts nor ends a transaction.
