cosc3380 databases set4

How many SQL statements must be in one transaction?

At least one

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

Never

After a rollback, the database restarts a transaction

Sometimes

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 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

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

Isolated

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

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

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

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

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 isolated. (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 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

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 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

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

Isolated

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

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

Type of Schedule?

T1 T2

------------ -----------

read X

X = X + 4

read X

X = X / 8

write X

write X

commit

commit

Non-serializable schedule

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

True

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

True

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

False

Type of schedule?

Transaction A writes data and rolls back before transaction B reads the data.

Strict

Type of schedule?

Transaction A writes data. Transaction B reads the data and commits before transaction A commits.

Nonrecoverable

Type of schedule?

Transaction A writes data. Transaction B reads the data. Transaction A rolls back before B commits.

Cascading

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

True

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

False

One transaction can hold an exclusive lock while other transactions hold shared locks on the same row. (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

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

True

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

Rigorous

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

Rigorous

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 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

T1 T2 T3

... ...... ....

write Y

write Z

write X

commit

commit

commit

How many transactions must roll back to break the deadlock?

One

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

True

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

False

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

True

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

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

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

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

SAVEPOINT

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

ROLLBACK TO

A ___________ statement erases saved data for exactly one savepoint.

RELEASE SAVEPOINT

A _________ statement reverses all changes made by a transaction.

ROLLBACK

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

False

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

True

A dirty block is a block that has been corrupted and cannot be read. (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

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

What Failure Scenario?

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

Transaction Failure

What Failure Scenario?

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

System Failure

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

Transaction or Rollback

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

Update

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

Checkpoint

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

Transaction or Start

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

Compensation or Undo

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

Checkpoint

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

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

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

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

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

Database availability is:

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

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

From the latest checkpoint.

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

Log records are sent to the secondary database.

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

2

What account can create other user accounts?

Root account

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

False

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

True

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

False

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

False

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

True

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

True

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

True

Authenticates users and grants access to specific databases and tables.

Query Processor

Contains MySQL Workbench

Tools

Does not directly interact with the query processor

File System

Determines what types of indexes are supports

Storage Engine

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

False

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

False

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

True

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

True

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

Query Parser

Which component detects an incorrect database server address?

Connection Manager

Which component determines that a query was recently executed?

Cache Manager

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

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

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.

Which is not a MySQL storage engine?

MEMORY

MongoDB

CSV

MongoDB

Which storage engine is commonly used for transaction management?

InnoDB

ARCHIVE

MEMORY

InnoDB

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

False

The __________ contains one row for each database object.

data dictionary or catalog

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

table_stats

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

log or log files