cosc3380 databases set4

What is the isolation level of each transaction? session begins SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; session ends session begins SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; transaction 1 transaction 2 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; transaction 3 transaction 4 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; transaction 5 session ends

1: Serializable 2: Read Uncommitted 3: Repeatable Read 4: Repeatable Read 5: Read Committed

Refer to the schedule below : T1 reads X T2 reads Y T2 writes Z T1 writes Y T2 writes X deadlock Deadlock occurs because T1 is waiting for an exclusive lock on Y, and T2 is waiting for an exclusive lock on X. When deadlock occurs, the database rolls back T2 and completes T1. Order the log records to match the schedule.

1: Start T1 2: Start T2 3: Update T2, Z... 4: Undo T2, Z... 5: Rollback T2 6: Update T1, Y... 7: Commit T1

What is the transaction boundary of 1, 3, 6, 8? session begins SET autocommit = OFF; session ends session begins statement 1 statement 2 statement 3 START TRANSACTION; statement 4 COMMIT AND CHAIN; statement 5 statement 6 statement 7 ROLLBACK; statement 8 session ends3

1: start 3: end 6: neither start nor end 8: both start and end

Refer to the website db-engines.com. What is the overall MySQL ranking, compared to all database systems?

2

A database uses strict two-phase locking. Transaction A takes an exclusive lock on X. Transaction B requests a shared lock on X. When is the shared lock granted?

After A commits or rolls back

How many SQL statements must be in one transaction?

At least one

A transaction increases all employee salaries by 10%. Due to a system failure, increases for only half of the employees are written to the database.

Atomic

A transaction removes expired subscriptions. Removes for only half of the subscriptions are written to the database due to a system failure. (What's violated)

Atomic

After the above animation completes, block C is updated and block G is read from the file system. What happens to the buffer?

Block C moves to the top, Block D is discarded, and Block G moves to the top.

During the redo phase, what log records remove a transaction from the active transaction list?

Both commit and rollback records

During the redo phase, what log records generate a database write?

Both update and compensation (undo) records

Which component determines that a query was recently executed?

Cache Manager

Type of schedule? Transaction A writes data. Transaction B reads the data. Transaction A rolls back before B commits.

Cascading

A list of active transactions appears in a(n) __________ record.

Checkpoint

A(n) __________ record indicates all data is saved from main memory to storage media.

Checkpoint

A(n) __________ record is written in the log whenever an update is reversed during a rollback.

Compensation or Undo

Which component detects an incorrect database server address?

Connection Manager

A transaction adds 1 to each account's primary key to make room for a new account. The updated primary key does not cascade to foreign keys. (What's violated)

Consistent

A transaction saves a row with a foreign key. The foreign key is not NULL and does not match any values of the corresponding primary key.

Consistent

Which deadlock management technique delays the fewest possible transactions?

Cycle Detection

Snapshot Isolation Steps: A)Write updates to the database or roll back the transaction B)Determine if any updates conflict with other transactions C)Write updates to a private copy of data D)Make a private copy of data accessed by the transaction Answer steps in order (Ex. ACDB)

DCBA

T2 increases Sam Snead's salary by 20% T1 reads Sam Snead's salary T2 rolls back T1 computes and writes Sam Snead's bonus based on his salary T1 commits

Dirty Read

A transaction removes expired subscriptions. The removals are written in the database, but due to a drive failure, the information is permanently lost. (What's violated)

Durable

A transaction withdraws $500 from account A and deposits $500 in account B. The withdrawal and deposit are written in the database, but due to a disk drive failure, the information is permanently lost.

Durable

A database uses basic two-phase locking. Transaction A takes an exclusive lock on X. Transaction B requests a shared lock on X. When is the shared lock granted?

During the contract phase of A

A bank stores checking account data in a database. The bank stores account owner data in a file on a different computer. An action creates a new owner in the file and assigns the owner to a checking account in the database. -The action is always atomic. (T/F)

False

A bank stores checking account data in a database. The bank stores account owner data in a file on a different computer. An action creates a new owner in the file and assigns the owner to a checking account in the database. -The action is always consistent. (T/F)

False

A bank stores checking account data in a database. The bank stores account owner data in a file on a different computer. An action creates a new owner in the file and assigns the owner to a checking account in the database. -The action is always durable. (T/F)

False

A bank stores checking account data in a database. The bank stores account owner data in a file on a different computer. An action creates a new owner in the file and assigns the owner to a checking account in the database. -The action is always isolated. (T/F)

False

A dirty block is a block that has been corrupted and cannot be read. (T/F)

False

An application program is part of the tools layer. (T/F)

False

CHECKPOINT statement syntax is specified in the SQL standard. (T/F)

False

Content in this material only applies to the MySQL database system. (T/F)

False

Deadlock can occur in a serializable schedule. (T/F)

False

MySQL with InnoDB suspends processing during a checkpoint and restarts after a checkpoint record is written to the log. (T/F)

False

One transaction can hold an exclusive lock while other transactions hold shared locks on the same row. (T/F)

False

Oracle Database has multiple storage engines. (T/F)

False

Several transactions can hold concurrent exclusive locks on the same block, as long as the transactions access different rows in the block. (T/F)

False

Several transactions can hold concurrent exclusive locks on the same row. (T/F)

False

The MySQL Command-Line Client provides a graphical interface for interacting with the database server. (T/F)

False

The Workbench is intended primarily for database users. (T/F)

False

The database server must be manually started each time the user runs the MySQL Command-Line Client. (T/F)

False

Transactions A and B are both SERIALIZABLE, and A always starts before B. The result may vary. (T/F)

False

Does not directly interact with the query processor

File System

With a cold backup, recovery from storage media failure reads the log:

From the latest checkpoint.

Which storage engine is commonly used for transaction management? InnoDB ARCHIVE MEMORY

InnoDB

Transaction 1 slowly updates account A's balance by a complex algorithm that reads the balance. Transaction 2 quickly withdraws 5 from account A's balance. Transaction 2 starts before 1 finishes. (What's violated)

Isolated

Two transactions run in parallel to set an account's balance to different values. (What's violated)

Isolated

Two transactions running in parallel reserve the same seat for different passengers.

Isolated

With a hot backup, as a transaction executes against the primary database:

Log records are sent to the secondary database.

Which is not a MySQL storage engine? MEMORY MongoDB CSV

MongoDB

After a transaction commits, the transaction can be rolled back:

Never

Type of Schedule? T1 T2 ------------ ----------- read X X = X + 4 read X X = X / 8 write X write X commit commit

Non-serializable schedule

Type of schedule? Transaction A writes data. Transaction B reads the data and commits before transaction A commits.

Nonrecoverable

T1 computes total salary for the entire company T2 increases Sam Snead's salary by 20% T2 commits T1 computes total salary by department T1 writes ( department total / company total ) for each department T1 commits

Nonrepeatable Read

T1 T2 T3 ... ...... .... write Y write Z write X commit commit commit How many transactions must roll back to break the deadlock?

One

T1 reads salaries of some Accounting department employees T2 transfers Maria Rodriguez from Accounting to Development T2 commits T1 reads salaries of remaining Accounting employees T1 computes and writes total salary of Accounting employees T1 commits

Phantom Read

Which component detects a missing semicolon at the end of an SQL statement?

Query Parser

Authenticates users and grants access to specific databases and tables.

Query Processor

A ___________ statement erases saved data for exactly one savepoint.

RELEASE SAVEPOINT

A _________ statement reverses all changes made by a transaction.

ROLLBACK

A ___________ statement erases saved data for zero, one, or many savepoints.

ROLLBACK TO

Which two-phase locking technique has, in effect, just one phase?

Rigorous

Which two-phase locking technique results in the longest wait times for concurrent transactions?

Rigorous

What account can create other user accounts?

Root account

A __________ statement temporarily saves data read and written by a transaction.

SAVEPOINT

Type of Schedule? T1 T2 ------------ ----------- read X Y = X + 4 write Y commit read X X = X / 8 write X commit

Serial schedule

Type of Schedule? T1 T2 ------------ ----------- read X Y = X + 4 read X X = X / 8 write X write Y commit commit

Serializable schedule

After a rollback, the database restarts a transaction

Sometimes

Determines what types of indexes are supports

Storage Engine

What Failure Scenario? Application programs run on a client machine. The database runs on a separate server machine. The network between client and server fails, and the database does not respond to any application requests.

Storage Media Failure

Type of schedule? Transaction A writes data and rolls back before transaction B reads the data.

Strict

What Failure Scenario? For unknown reasons, an application program freezes. The data administrator forces termination and restarts the application.

System Failure

What block does InnoDB discard when more space is needed in the buffer?

The block that has not been accessed for the longest time.

Database availability is:

The percentage of time that a database is responsive to application programs.

During the undo phase, the recovery system reads the log in reverse and stops at:

The start record for the last transaction in the active transaction list

Contains MySQL Workbench

Tools

What Failure Scenario? The database detects two deadlocked transactions. To break the deadlock, the database rolls back one of the transactions.

Transaction Failure

After all updates have been reversed in a rollback, a(n) __________ record is written in the log.

Transaction or Rollback

A(n) __________ record always appears in the log at the beginning of a transaction.

Transaction or Start

During the undo phase, the recovery system writes compensation records for:

Transactions that do not commit or roll back following the most recent checkpoint

A SERIALIZABLE transaction can run concurrently with a READ COMMITTED transaction. (T/F)

True

A program that helps database administrators configure MySQL is considered a utility program. (T/F)

True

A transaction with isolation level SERIALIZABLE can participate in a deadlock. (T/F)

True

Checkpoints can be initiated either manually by the database administrator or automatically by the database. (T/F)

True

Many Connector tools are built on top of API tools. (T/F)

True

MySQL Workbench and MySQL Command-Line Client both allow the user to type SQL statements. (T/F)

True

Several transactions can hold concurrent shared locks on the same row. (T/F)

True

The MySQL Workbench screenshot above shows the columns that make up the City table. (T/F)

True

The SQL statements in the SQL query panel are not executed until the lightning bolt is clicked. (T/F)

True

The root account password is set when installing MySQL. (T/F)

True

When a transaction takes a shared lock on a block, other transactions may be delayed. (T/F)

True

When two READ UNCOMMITTED transactions run concurrently, the result may vary. (T/F)

True

Whenever deadlock occurs, a cycle of dependent transactions always exists. (T/F)

True

When a transaction deletes a table row, a(n) __________ record is written in the log.

Update

The __________ contains one row for each database object.

data dictionary or catalog

_________ support database recovery in the event of a system failure.

log or log files

The ___________ table provides various table statistics and is used by the query optimizer to generate efficient execution plans.

table_stats