CIS275 - Chapter 6: Transaction Management

Question 1

a sequence of database operations that must be either completed or rejected as a whole.

Answer 1

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.

Question 2

Saving complete transaction results in the database is called a _____.

Answer 2

commit

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

Question 3

Rejecting an incomplete transaction is called a _____k.

Answer 3

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.

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

Question 4

Answer 4

Question 5

All transactions must be atomic, consistent, isolated, and durable, commonly called the _____ properties:

Answer 5

ACID

Question 6

In an _____ transaction, either all or none of the operations are executed and applied to the database.

Answer 6

atomic

Partial or incomplete results are rolled back, and the database returns to its state prior to execution of the transaction.

Question 7

In a _____ transaction, all rules governing data are valid when the transaction is committed.

Answer 7

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.

Question 8

An _____ transaction is processed without interference from other transactions.

Answer 8

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.

Question 9

A _____ transaction is permanently saved in the database once committed, regardless of system failures.

Answer 9

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.

Question 10

The ACID properties are supported in two database subsystems. The _____ enforces atomic and durable transactions.

Answer 10

recovery system

Both the recovery and concurrency systems, along with other database components, support consistency.

Question 11

The ACID properties are supported in two database subsystems. The _____ enforces isolated transactions.

Answer 11

concurrency system

Both the recovery and concurrency systems, along with other database components, support consistency.

Question 12

Answer 12

Question 13

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

Answer 13

dirty read

Ex:

1. T2 updates data X.
2. T1 reads the updated value of X before T2 commits.
3. T2 fails and is rolled back.

Since T1 reads a value that is eventually rolled back, the result of T1 is invalid.

Question 14

In a _____, a transaction repeatedly reads changing data.

Answer 14

nonrepeatable read

Ex:

1. T1 reads data X.
2. T2 updates X.
3. T1 rereads X.

If T1 incorrectly assumes the value of X is stable, the result of T1 is invalid.

Question 15

In a _____, one transaction inserts or deletes a table row that another transaction is reading.

Answer 15

phantom read

Ex:

1. T1 begins reading table rows.
2. T2 inserts a new row into the table.
3. 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.

Question 16

Answer 16

Question 17

Answer 17

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

Question 18

Answer 18

Question 19

Answer 19

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.

Question 20

Answer 20

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.

Question 21

Answer 21

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.

Question 22

Answer 22

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.

Question 23

Answer 23

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.

Question 24

a sequential order of operations for multiple transactions.

Answer 24

transaction schedule

Operations for different transactions can be interleaved so transactions run concurrently. Operations for individual transactions must occur in the correct order.

Question 25

Within a schedule, two operations in different transactions _____ when the relative order of the operations affects the outcome:

Answer 25

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.

Question 26

_____ contain the same transactions with all conflicting operations in the same order.

Answer 26

Equivalent schedules

Equivalent schedules always have the same result.

1. The schedule has transactions T and T, each with a sequence of operations.
2. Operations read X and write X conflict because the order of the read and write operations affects the final outcome.
3. In a conflicting schedule, conflicting operations are in a different order. The final value of Z is different in the conflicting schedule.
4. Operations read X and read Y do not conflict.
5. 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.

Question 27

_____ contain the same transactions with some conflicting operations in different order.

Answer 27

Conflicting schedules

Conflicting schedules can potentially have different results.

1. The schedule has transactions T and T, each with a sequence of operations.
2. Operations read X and write X conflict because the order of the read and write operations affects the final outcome.
3. In a conflicting schedule, conflicting operations are in a different order. The final value of Z is different in the conflicting schedule.
4. Operations read X and read Y do not conflict.
5. 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.

Question 29

a schedule in which transactions are executed one at a time.

Answer 29

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.

Question 30

Any schedule that is equivalent to a serial schedule is a _____.

Answer 30

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.

Question 31

Answer 31

Question 32

The SQL standard defines four isolation levels:

Answer 32

1. SERIALIZABLE transactions run in a serializable schedule with concurrent transactions. Isolation is guaranteed.
2. 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.
3. 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.
4. 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.

Question 33

Answer 33

Question 34

In a ______, one or more transactions cannot be rolled back.

Answer 34

nonrecoverable schedule

Question 35

In a _____, rollback of one transaction forces rollback of other transactions.

Answer 35

cascading schedule

Question 36

In a _____, rollback of one transaction never forces rollback of other transactions.

Answer 36

strict schedule

Question 37

Answer 37

Question 38

Answer 38

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.

Question 39

a database component that manages concurrent transactions.

Answer 39

concurrency system

The concurrency system implements isolation levels while attempting to optimize overall database performance.

Question 40

A ____ is permission for one transaction to read or write data.

Answer 40

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.

Question 41

A _____ allows a transaction to read, but not write, data.

Answer 41

shared lock

Concurrent transactions can hold shared locks on the same data.

Question 42

An _____ allows a transaction to read and write data.

Answer 42

exclusive lock

When one transaction holds an exclusive lock, no concurrent transaction can take a shared or exclusive lock on the same data.

Question 43

the collection of data reserved by a lock.

Answer 43

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.

Question 44

a component of the concurrency system that tracks, grants, and releases locks.

Answer 44

lock manager

Question 45

Answer 45

Question 46

a specific locking technique that ensures serializable transactions.

Answer 46

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.

Question 47

_____ has expand and contract phases for each transaction, also known as grow and shrink phases.

Answer 47

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.

Question 48

_____ holds all exclusive locks until the transaction commits or rolls back.

Answer 48

Strict two-phase locking

The expand phase is the same as in basic two-phase locking, but the contract phase releases only shared locks.

1. Rollback of T1 forces rollback of T2, so the schedule is cascading.
2. With strict two-phase locking, exclusive locks are not released during the contract phase. The exclusive lock is held until T1 rolls back.
3. T2 read must wait until the exclusive lock is released.
4. Rollback of T1 does not cascade to T2.

Question 49

_____ holds both shared and exclusive locks until the transaction commits or rolls back.

Answer 49

Rigorous two-phase locking

In effect, rigorous two-phase locking has no contract phase.

Question 50

Answer 50

Question 51

a state in which a group of transactions are frozen.

Answer 51

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.

1. The schedule has three transactions that read and write X, Y, and Z.
2. Transactions first take shared locks when reading, then request exclusive locks when writing.
3. 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.
4. Similarly, T2 waits for T3 to release the shared lock on Z, and T3 waits for T1 to release the shared lock on X.
5. The dependency cycle causes deadlock.

Question 52

A _____ is waiting for data locked by another transaction.

Answer 52

dependent transaction

Question 53

A _____ of dependent transactions indicates deadlock has occurred.

Answer 53

cycle

Ex: T1 depends on T2, which depends on T3, which depends on T1. The transactions are deadlocked.

Question 54

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.

Answer 54

Aggressive locking

Question 55

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.

Answer 55

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.

Question 56

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.

Answer 56

Timeout

Question 57

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.

Answer 57

Cycle detection

Question 58

Answer 58

Question 59

_____ execute concurrent transactions without locks and, instead, detect and resolve conflicts when transactions commit.

Answer 59

Optimistic techniques

Optimistic techniques are effective when conflicts are infrequent, as in analytic applications with many reads and few updates.

Question 60

_____ creates a private copy of all data accessed by a transaction, called a snapshot, as follows:

Answer 60

Snapshot isolation

1. Create a snapshot when the transaction starts.
2. Apply updates to the snapshot rather than the database.
3. Prior to commit, check for conflicts. A conflict occurs when concurrent transactions write the same data. Reads do not cause conflicts.
4. If no conflict is detected, write snapshot updates to the database (commit).
5. 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.

1. Under snapshot isolation, the transactions take snapshots and process in parallel.
2. Since the transactions write different data, no conflict is detected. Both transactions commit.
3. Under snapshot isolation, neither transaction waits.
4. Under two-phase locking, T1 waits for T2 to commit and release the shared lock on Y.

Question 61

_____ extends standard snapshot isolation and ensures serializable schedules when isolation level is set to SERIALIZABLE.

Answer 61

Serializable snapshot isolation

1. The serial schedule commits and results in the same value for X and Y.
2. The non-serializable schedule contains conflicting operations in a different order than the serializable schedule.
3. The non-serializable schedule contains a cycle and results in deadlock under two-phase locking.
4. Under snapshot isolation, no conflict is detected, and the transactions commit.
5. Snapshot isolation results in different values for X and Y.

Question 62

Answer 62

Question 63

The recovery system must manage three failure scenarios:

A _____ results in a rollback.

Answer 63

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.

Question 64

The recovery system must manage three failure scenarios:

A _____ includes a variety of events resulting in the loss of main memory.

Answer 64

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.

Question 65

The recovery system must manage three failure scenarios:

A _____ failure occurs when the database is corrupted or the database connection is lost.

Answer 65

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.

Question 66

Answer 66

Question 67

a file containing a sequential record of all database operations.

Answer 67

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.

Question 68

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.

Answer 68

update record i

Question 69

The recovery log contains four types of records:

A _______, indicates data has been restored to the original value during a rollback.

Answer 69

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.

Question 70

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.

Answer 70

transaction record

Question 71

A _____ indicates that all data in main memory has been saved on storage media.

Answer 71

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.

Question 72

Answer 72

Question 73

Answer 73

Question 74

Recovery from a system failure has two phases.

The _____ restores all transactions that were committed or rolled back since the last checkpoint.

Answer 74

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.

Question 75

Recovery from a system failure has two phases.

The _____ rolls back transactions that were neither committed nor rolled back.

Answer 75

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.

Question 76

Answer 76

Question 77

the percentage of time a system is working from the perspective of the system user.

Answer 77

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.

Question 78

The _____ technique periodically creates checkpoints and, while transaction processing is paused, copies the database to backup media.

Answer 78

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.

Question 79

The _____ technique maintains a secondary database that is nearly synchronized with the primary database.

Answer 79

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.

Question 80

Answer 80

Question 81

The ______ statement sets the isolation level for subsequent transactions:

Answer 81

SET TRANSACTION

Question 82

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.

Answer 82

SESSION

SESSION sets the isolation level for all transactions in the current session.

Question 83

_____ sets the isolation level for all transactions submitted to the MySQL server for all subsequent sessions.

Answer 83

GLOBAL

Existing sessions are not affected. The GLOBAL setting can be used only by a database administrator with appropriate MySQL privileges.

Question 84

Answer 84

Question 85

the first or last statement of a transaction.

Answer 85

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.

Question 86

In MySQL with InnoDB, the behavior of statements outside a transaction boundary depends on the _____ system variable.

Answer 86

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

Question 87

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.

Answer 87

AND CHAIN

RELEASE

Question 88

The _____ statement is identical to START TRANSACTION.

Answer 88

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.

Question 89

Answer 89

1. The data administrator sets system autocommit to ON.
2. Since autocommit is ON, the UPDATE statement commits immediately.
3. START TRANSACTION overrides autocommit. The transaction continues until the COMMIT statement.
4. AND CHAIN starts a new transaction.
5. Neither AND CHAIN nor START TRANSACTION appear, so the DELETE statement autocommits.

Question 90

Answer 90

Question 91

A _____ is a point within a transaction where partial transaction results are saved temporarily.

Answer 91

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.

Question 92

Answer 92

1. When the transaction starts, Employee has four rows.
2. At savepoint First, one row has been deleted from Employee.
3. At savepoint Second, three rows have been deleted from Employee.
4. Rollback restores Employee to savepoint First. All savepoint identifiers and partial transaction data are released.
5. Transaction commits. Employee table is saved with three rows.

Question 93

Answer 93

Question 94

a database block that has been updated in main memory but not yet saved on storage media.

Answer 94

dirty block

Question 95

A ____ saves dirty blocks and log records, as follows:

Answer 95

checkpoint

1. Suspend database processing.
2. Write all unsaved log records to the log file.
3. Write all dirty blocks to storage media.
4. Write a checkpoint record in the log.
5. Resume database processing.

Checkpoints enable rapid recovery from system failure, as described elsewhere in this material.

Question 96

A _____ resumes processing while saving dirty blocks.

Answer 96

fuzzy checkpoint

A fuzzy checkpoint improves database availability but complicates recovery if the system fails while saving dirty blocks.

Question 97

Answer 97

1. The data administrator configures checkpoints at three-second intervals.
2. Updates and log records are written to main memory.
3. After three seconds, automatic checkpoint flushes dirty blocks and log records to storage media.
4. The transaction continues writing data to main memory.

Question 98

Answer 98

Question 99

Answer 99

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.

Question 100

Answer 100

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.

Question 101

Answer 101

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.

Question 103

Answer 103

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.

Question 104

Answer 104

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.

Question 105

Answer 105

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.

Question 106

Answer 106

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.

Question 107

Answer 107

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.

Question 108

Answer 108

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.