quiz 3 - week 9 content

Question 1

A transaction comprises a unit of ___ ___

Transactions take the database from one ___ ___ to the next ___ ___

in Oracle databases a transaction implicitly begins with the ______________________

Issuing either a _____ or ____ statement explicitly ends a transaction

Answer 1

database work

consistent state, consistent state

in Oracle databases a transaction implicitly begins with the first statement that modifies data

commit or rollback

Question 2

When you issue a COMMIT, you are assured that :

all of your changes have been ___ ___

that any data ___ ___ and ___ have been validated

Answer 2

successfully saved

integrity checks, rules

Question 3

what does rollback do

Answer 3

ends transaction
undo any uncommitted changes

is an undo button

Question 4

savepoint

rollback to

SET TRANSACTION:

Answer 4

savepoint- creates a marked poiint

rollback to - rolls back transactions to the marked point without rolling back any of the work that preceded it

allows you to set various transaction attributes, such as the transaction’s isolation level and whether it is read-only or read-write.

Question 5

atomicity
a transaction is atomic if it __________________ or __________________

DDL are implemented in a manner that:
1
2
3

Answer 5

succeeds as a whole
fails as a whole

commit outstanding work, ending a transactions in place.
perform DDL
Commit DDL, roll back if failed

Question 6

consistency
should not introduce ___ ___ to the database

tools to minimize data inconsistencies

____________

Answer 6

wrong data

tabular structure, data types, constraints

3rd normal form

Question 7

1nf
2nf
3nf

Answer 7

atomic, unique pk

non-key columns are fully functionally dependent on the pk

every non-key column must depend only on the primary key, not on another non-key column

Question 8

__________-Is the contract between the database and the user that
states that a transaction is stored in the database when it is
completed no matter what happens (even if the system will
fail)

Answer 8

durability

Question 9

___________- * Every transaction has to be dealt with in isolation even if
the transactions are concurrent

• For example there should be a mechanism that allows to
  resolve the conflicts arising from multiple users updating
  same data at the same time

Answer 9

isolation

Question 10

Whenever an INSERT, UPDATE or DELETE statement is issued, the data is not immediately sent to the table but, rather, is temporarily stored in the ___ __

When the user issues a SELECT statement to view the data, data from the table is combined with the data from the buffer before presenting it to the user and the
user thinks that the data has been inserted

Other users, however, do not see the data. Locks prevent users from ___________________ when they are simultaneously making changes and so are very
important

Answer 10

user’s buffer.

destroying each other’s data

Question 11

___ are acquired when any process accesses a piece of data where there is a chance that another concurrent process will need this piece of data as well at the same time. By locking the piece of data we ensure that we are able to action on that data the way we expect.

• ____ occur when two processes need access to same piece of data concurrently so one process locks the data and the other one needs to wait for the other one to complete and release the lock. As soon as the first process is complete, the blocked process resumes operation. The blocking chain is like a queue: once the blocking process is complete, the next processes can continue.
• _____ occurs when one process is blocked and waiting for a second process to complete its work and release locks, while the second process at the same time is blocked
  and waiting for the first process to release the lock

Answer 11

Locks

blocks

Deadlock

Question 12

you can save part of a transaction t/f

Answer 12

f
You can not save part of the transaction to the database. It will either be saved as a whole or none of the modifications will be saved

Question 13

A transaction is defined as all the _________________

Answer 13

DML statements that are placed between two COMMITs

Question 14

A ___ ___ occurs when a transaction reads data that another transaction has modified but not yet committed. If the other transaction is rolled back, the first transaction is left with incorrect data

A __________ happens when a transaction reads the same data twice but finds it has changed between the two reads due to another transaction’s update

___ ___ occur when a transaction reads a set of rows that satisfy a condition, but another transaction inserts or deletes rows that meet the same condition, causing the result set to change upon a re-read

Answer 14

dirty read

non-repeatable read

Phantom reads

Question 15

4 isolation levels

_______:
* Allows a transaction to read data that has been modified by other transactions but not yet
committed.

__________:
* A transaction can only read data that has been committed by other transactions.

__________:
* Ensures that if a transaction reads a set of rows, it will see the same set of rows throughout the entire transaction.

___________:
* Provides the highest level of isolation.
* Ensures complete isolation between transactions, preventing any interference

Answer 15

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Question 16

Read Uncommitted Allows:

Read Committed Allows:

Repeatable Reads Allows:
Serializable ___

Answer 16

Dirty Reads, Non Repeated Reads, and Phantom Reads

Non Repeated Reads, and Phantom Reads

Phantom Reads

Does not allow any of the three reads